Metric Formula Editor

Metrics are objects that represent business measures and key performance indicators. From a practical perspective, metrics are the calculations performed on data stored in your database, the results of which are displayed on a report. Metrics are similar to formulas in spreadsheet software. For a brief overview of metrics, see About Metrics.

The Metric Formula Editor allows you to:

Create a compound metric (which combines multiple metrics, such as Net Sales - Refund Amount)
Build custom expressions such as percent-to-total
Type the metric formula, if you are experienced with metrics

The metric formula determines the data to be used from your data source and the calculations to be performed on that data. An example of the formula of a metric is Sum(Line Void Count), where Line Void Count is a fact stored in the data source. This metric formula calculates the sum of line voids recorded in the data source to determine a statistical measure of line voids for stores, cashiers, etc.

A metric formula can also look like Rank<ASC=False, ByValue=True, BreakBy={[Region]}>([Net Sales]). This metric ranks net sales values (from the Net Sales metric) within regions. The highest net sales is ranked as 1. The Rank function is a non-grouping function.

To create a metric using the Formula Editor:

Navigate to the Shared Reports or My Reports folder or to the Search page.
From the icon bar, click the Create Metric icon. This displays the Select a Function or Template dialog box.
Click Switch to Formula Editor. This displays the Formula Editor.
- Create the metric formula
- The metric formula determines the data to be used from your data source and the calculations to be performed on that data, as described above.
You can type the metric expression, if you are experienced with metrics and know the metric expression. As you type, matching objects are displayed in a drop-down list. In the list, you can click an object to select it, or click Browse to browse for the object. You can also continue typing the expression.
To select a function, complete the following steps:
1. Click Insert Function . The Select a Function or Template dialog box opens.
2. Do one of the following:
2. If you know the function that you want to use, begin typing the function name in the field. As you type, matching objects are displayed in the Search Result list. You can click an object to select it, or continue to type the name.
3. To browse through functions to find the one that you want, from the Select a category drop-down list, select All Functions. All the functions are listed alphabetically in the list. (To reduce the number of functions displayed, you can select a function category instead.)

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

Select the function to use, then click OK.
If you selected a grouping function (such as Sum or Average), the function requires an expression (the data to be used). Optionally, you can add levels, a condition, and transformations. The Function Editor opens. After you specify the expression, click Insert. You are returned to the Formula Editor.

Specify the expression by doing one of the following:
- To specify the expression by typing the name of a 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 Net Sales-Refund Amount.
- To specify the expression by choosing an object, click the Browse icon . The Select an Object dialog box opens. Either navigate to and select an object, or search for an object.
If you selected a non-grouping function (such as Rank or Moving Average), the function requires arguments and/or parameters. The dialog box for the function opens. To view information about the arguments and parameters for the function, click Details at the bottom of the dialog box. Click Insert when you have specified the arguments and parameters. You are returned to the Formula Editor.
- Arguments are the input values of a function. For example, the arguments of the Average function can be the metric to be averaged.
  
  For each argument listed, type a value or click the Browse icon to find the metric, fact, prompt, or other compatible object to use.
- Parameters allow you 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.
  
  For each parameter listed, type a value or select the option from the drop-down list.

To add a logical operator, complete the following steps:

Click Insert Function . The Select a Function or Template dialog box opens.
From the Select a category drop-down list, select point to Logical Operators, and select either IF or NOT. 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.
After you have defined all the arguments, click Insert. You are returned to the Formula Editor.

To delete the metric formula and begin again, click Clear Content Clear Content icon .

Validate the metric formula

When you have completed the metric formula, click the Syntax Validation icon Syntax Validation icon . The Formula Editor displays a status message, at the bottom of the metric expression, indicating whether or not the new metric expression is correct. When the message displays as Valid Metric Formula, the metric expression is correct.

Complete the metric

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 Metric dialog box.

You can select the functions that can be used to total the metric on a report. For steps, see Advanced Metric Options dialog box: Subtotals.

You can select the function used for dynamic aggregation, which is the rollup of metric values that occurs when an attribute is moved from the report grid to the Report Objects. For steps, see Advanced Metric Options dialog box: General.

Click Save to save your changes. This displays the Save As dialog box.

Navigate to the folder where you want to save the metric. Type a Name and Description for the metric, and click OK. Your new metric is saved, and you are returned to the Function Editor.