Metric Formula Editor

You can use the Metric Formula Editor to create or edit a metric, which is a calculation performed against the data in the data warehouse. Metrics represent business measures and key performance indicators, and are similar to formulas in spreadsheet software. Steps to create and edit a metric are provided below.

Use the Metric Formula Editor to:

• Combine multiple metrics to create a single compound metric (for example, Revenue - Profit).
• Create a metric that contains multiple layers of functions (a nested metric, such as Avg(Sum(Revenue)), which first adds all the Revenue fact values and then averages them.
• Build custom expressions, such as percent-to-total metrics or expressions that use custom functions created with the Strategy Function Plug-In Wizard. For an introduction to the Strategy Function Plug-In Wizard, see the Functions Reference.
• Create a metric by typing the metric formula directly.

To create a metric, you must define the metric's formula, which consists of arithmetic operators (+, -, *, and /) and functions. The operators and functions can be applied to facts, attributes, or metrics. An example of the formula of a compound metric is RunningAvg(Cost), where Cost is a metric and RunningAvg is a function that calculates the running average. Another example is Sum(Cost) + Sum(Profit), where Cost and Profit are metrics and Sum is a function that calculates the sum. In this case, the summation of the Cost metric is added to the summation of the Profit metric.

To add functions to the metric formula, you must select any options required to define the function. For example, if you want to add a grouping function, such as Sum, Average, First, or Maximum, you must define the metric's expression, and you can also define options such as the level, condition, and transformation. If you want to add a non-grouping function, such as data mining, date, OLAP, and ranking functions, you must define the input values (called arguments) for the function, as well as any parameters you can use to determine the behavior of the function. For the syntax of metric formulas that use grouping functions, see the Advanced Reporting Help. For the syntax for non-grouping functions, see the Functions Reference.

If you want to create a metric by selecting objects and being guided through the process, and the metric that you want to create does not include multiple metrics or custom expressions, toggle off the Formula Editor, then select the appropriate options to define your metric. For steps, see Create a Stand-Alone Metric.

This procedure assumes that you are familiar with directly typing metric formulas. For the syntax of metric formulas that use grouping functions, see the Advanced Reporting Help. For the syntax for non-grouping functions, see the Functions Reference.

Stand-Alone Vs. Derived Metrics

This topic discusses stand-alone metric objects. You can also create derived metrics within reports, dashboards, and documents. They are not saved as separate objects. See: 

• Metrics in Dashboards for an introduction to derived metrics created in dashboards

• Introduction to Derived Metrics in Reports

• Creating Metrics in Documents

To Create a Metric Using the Formula Editor

1. Open the Workstation window.
2. From the File menu, select New Metric.
3. If you are connected to multiple environments or projects, select an environment and project. The Metric Editor dialog box opens.
4. Toggle on the Formula Editor at the top right of the dialog box. The Formula Editor opens.
5. Type the metric formula in the pane on the right. As you type, matching objects, such as attributes and metrics, display in a drop-down list. To add an object to the metric, do one of the following:
   • To add an object from the list, select the object's name.
   • To browse for and select an object to add, click Browse . The Select an Object dialog box opens. Navigate to the location of the object or search for it by typing its name in the search field or by browsing to the location of the object, then select it.
6. You can also add an object to the formula by using the All Objects pane. Navigate to the location of the object or search for it. Double-click the object to add it to the formula at the cursor.
7. To add an arithmetic operator (such as + and -) or a set of parenthesizes or angle brackets, click the object in the tool bar above the formula area or type it directly.
8. To add a function to the formula, select it in the Functions pane on the left. You can add multiple metrics to create compound metrics (Revenue - Profit) or nested metrics (such as Avg(Sum(Revenue))). Narrow the list of functions displayed in the pane by doing one of the following:
   • To search for the function by name, type the function's name in the search field.

   • Choose a function category from the drop-down list, such as Math Functions or Financial Functions. The pane is updated to include only the functions that belong to the selected category.

   When you select a function, a description of the function is displayed at the bottom of the dialog box. Click Details to view more information about the function, such as syntax and examples.

   You can also type a function directly in the formula, selecting it from the from the matching objects that display. Using the Functions pane allows you to display and edit the arguments, parameters,

9. Define any additional options for the function clicking Edit next to the function in the Functions pane. by doing one of the following:

   • If you selected a grouping function, such as Sum, Average, First, or Maximum, you define the metric's expression, as well as optional components such as the level, condition, and transformation.

     Click here to view steps to define the expression.

     1. Define the metric's expression by doing one of the following:

        • To specify the expression by typing the name of an object, type the name of the object in the Search for a fact, metric, or attribute field. As you type, matching objects display in a drop-down list; select the object to use.

        • To specify the expression by choosing an object, click the Browse icon . In the Select an Object dialog box, navigate to and select an object, or search for the object.

     2. If the function uses parameters, such as whether to count distinct elements for the Sum function, click Function Parameters to define them. Click OK.

     3. You can further define the metric by adding a level, condition, and/or transformation. Click Show All then:

     • To define the attribute level to evaluate the metric at, add a level to the metric. For steps, see Introduction to Level Metrics. A level allows you to specify the attributes to use in the metric calculation, regardless of what is contained on any report, document, or dashboard the metric is placed on. By default, a metric is calculated at the level of the attributes on the report, document, or dashboard.
     • To filter the data included in the metric, add a condition to the metric. For steps, see Conditional Metrics: How to Filter Data Calculations. Metric conditions (also called metric filters) are applied to the metric without affecting any other metrics on a report, document, or dashboard. For information on report filters, which affect multiple metrics on a report, see Create Filters.
     • To apply offset values such as "four months ago" to a metric, you add a transformation. For example, you can compare revenue numbers from this year to last year. For steps, see Transformation Metrics: Time-Based and Other Comparisons.
     4. Click Add.

   • If you selected a non-grouping function, such as data mining, date, OLAP, and ranking functions, options to define the function's input values (called arguments) display. Any parameters that determine the function's behavior also display. For example, the NTile function has two parameters, Ascending and Tiles. Ascending controls whether the NTiles are ordered in ascending or descending order, while Tiles sets the number of splits. Hover your cursor over an argument or a parameter to display a tooltip description.

     Click here to view steps to define the expression.

     1. For each argument listed, type a value or click the Browse icon to find the metric, fact, prompt, or other compatible object to use as input values of the function.

     2. For each parameter listed, type a value or select the parameter value from the drop-down list.

     3. Click Add.

10. Once you complete the metric formula, click Validate. The Formula Editor displays a status message below the metric expression, indicating whether the new metric expression is correct. When the message displays as Valid Metric Formula, the metric expression is correct.

    • If you need to delete the metric formula and begin again, click Clear .
11. You can define how the metric header and the metric values are formatted and displayed in a report. For example, you can define how numeric values are displayed, font styles and sizes, and cell display colors. Your formatting is applied to the metric regardless of the report on which it is placed. For steps to format the metric, see Format Metric dialog box.
12. You can select the functions that can be used to total the metric on a report and the dynamic aggregation function (used for the rollup of metric values that occurs when an attribute is moved from the report grid to the Report Objects in a report). For steps, see Advanced Metric Options dialog box.
13. Click Save.

Once you have created a metric, you can add the metric to a report, document, or dashboard to analyze your data. For steps, see Create and Edit Reports.

Related Topics

• Introduction to Metrics for a brief overview of metrics

• Format Metric dialog box for steps to format a metric

• Advanced Metric Options dialog box for steps to define dynamic aggregation and subtotal functions, VLDB properties, metric aliases, the metric join type, and other settings

• The Advanced Metrics chapter of the Advanced Reporting Help for the syntax of metrics that use grouping functions

• Functions Reference for descriptions of functions, including the syntax of non-grouping functions