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.

The Metric Formula Editor allows you to:

Combine multiple metrics to create a single compound metric (for example, Revenue - Profit).
Build custom expressions, such as percent-to-total metrics or expressions that use custom functions created with the MicroStrategy Function Plug-In Wizard. For an introduction to the MicroStrategy 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, click Switch to Function Editor, then select the appropriate options to define your metric. For steps, see How to Create a 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.

To Create a Metric Using the Formula Editor

Click Create on any page, and select New Metric.
Click Use Formula Editor.
In the pane, begin typing the metric formula. You can type arithmetic operators (such as + and -) and comparison operators (such as < and >) directly in the metric expression. As you type, matching objects, such as attributes and metrics, are displayed 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 the Browse icon . The Select an Object dialog box opens. Search for the object by typing its name in the search field or by browsing to the location of the object, then select it.
Click the Insert Function icon .
In the pane below, select the function to add to the metric. You can 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.
- To choose from all functions in a specific category, from the drop-down list, choose a function category, such as Math Functions or Financial Functions. The pane is updated to include only the functions that belong to the selected category.
Click Next, then define any additional options for the function by doing one of the following:

If you selected a grouping function, such as Sum, Average, First, or Maximum, the Expression field is displayed, along with settings for defining the level, condition, and transformation (optional). Perform the following steps:
1. Define the function 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 Expression field. As you type, matching objects are displayed in a drop-down list. You can click an object or continue to type. You can type multiple objects, such as Revenue-Profit.
  - To specify the expression by choosing an object, click the Browse icon next to the Expression field. The Select an Object dialog box opens. Either navigate to and select an object, or search for an object.
2. By default, the function is calculated at the level of the attributes on the report on which the metric is placed. Instead, you can specify the attributes to use in the calculation, regardless of what is contained on any report the metric is placed on. For detailed steps, see By default, metrics are evaluated at the level of the attributes on the report; this is called the report level. The report level allows the metric calculation to adapt to different reports. You can remove the report level from the metric. If you do this, only the level explicitly set on the metric affects the metric calculation, regardless of the attributes placed on the report. You do not have to remove the report level to add levels to the metric. For a more detailed description of the report level, and the impact of removing it, see the Advanced Reporting Help. Do one of the following:To remove the report level from the metric, click the Delete icon next to Report Level. To add the report level to the metric after it has been deleted, click the Add Report Level icon . .
3. You can define a condition to filter which values to include in the calculation. For detailed steps to add a condition, see Add the Condition.
4. You can apply offset values, such as "four months ago", to the calculation by defining a transformation. For example, you can compare revenue numbers from this year to last year. For detailed steps to add a transformation, see To add a transformation to a metric.
If you selected a non-grouping function, such as data mining, date, OLAP, and ranking functions, you are presented with options to 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 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. To view a list of the arguments and parameters for the function, click Details at the bottom of the dialog box. Perform the following steps:
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.
Click Insert.
Click the Insert Function icon.
From the drop-down list, select Logical Operators. In the pane below, select the logical operator that you want to add to the metric. Click Next. The dialog box for the logical operator opens.
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 you type, matching objects are displayed in a list. You can select an object or continue to type. For a description of each argument, click Details.
Click Insert when finished to add the logical operator to the metric formula.
Repeat the appropriate steps above as desired to define your metric formula. To delete the metric formula and begin again, click the Clear Content icon .

After you complete the metric formula, click the Syntax Validation icon . 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.

You can format the metric header and the metric values. Formatting includes how numeric values are displayed, font styles and sizes, and cell display colors. This formatting is used for the particular metric, regardless of the report on which it is placed. For steps, see Format Dialog Box for Metrics.

You can select additional options to determine the functions that can be used to total the metric on a report, the function used for dynamic aggregation, and so on. For an overview of these options and steps, see Advanced Metric Options Dialog.
Click Save.

Metric Formula Editor

To Create a Metric Using the Formula Editor

Related Topics