MicroStrategy ONE

Creating derived metrics in documents

A derived metric is a metric that is obtained dynamically, when a document is executed, directly from metrics on a document dataset. A derived metric is created using at least one of the metrics in the document. If more than one metric is used, they must be from the same dataset. Derived metrics permit the dynamic application of calculations to a document without requiring new metric definitions.

A derived metric:

  • Must be a compound metric. The reverse is not true -- a compound metric does not have to be a derived metric.

  • Must use metrics from the same dataset.

  • Can be reused within the document, including in Grid/Graphs and in conditional formatting expressions. You can use a derived metric in multiple places in a document, because derived metrics are created as dataset objects, not text fields.

  • Is calculated by the analytical engine based on metrics that are contained in the Datasets pane.

  • Cannot use transformation objects.

  • You can only use metrics already existing in a single dataset to create a derived metric. If the definition of the derived metric demands metric data not readily available, the information cannot be obtained dynamically, as the dataset must first be re-executed.

  • You can also create derived training metrics, which are used for Data Mining Services, on a Grid/Graph. A training metric analyzes data and generates a forecast, or predictive metric. For background information on training metrics and predictive metrics, including instructions to create a derived training metric on a Grid/Graph, see Using Data Mining Services.

For example, if your dataset contains the dollar sales for a particular region, and you want to view the same data in millions, you could create a derived metric with a definition of [Dollar Sales]/1000000.

When you create a derived metric, it is added to the Datasets pane but is not placed on the document. However, if you select a Grid/Graph object before creating the derived metric, the metric is added to the Grid/Graph as well as placed in the Datasets pane for future use.

After you create the derived metric, you can add it to the document just as you would any other dataset object. You can also use it in conditional formatting expressions.

Derived metrics from dataset reports

Unlike the derived metrics that you create in a document, any derived metrics on a dataset report are not displayed in the Datasets pane and therefore cannot be used in the document. For more information, see Using datasets in documents.

To create a derived metric

  1. Open the document in the Document Editor. How?

  2. Do one of the following:

    • Select the dataset in the Datasets pane that contains the metrics to be used in the derived metric.

    • To create a metric in a Grid/Graph, select the Grid/Graph in the Layout area. Double-click the Grid/Graph to enable Edit mode. A red hashed border displays around the Grid/Graph, and the menu options and toolbar change.

      The metrics used in a derived metric must be from the same dataset.

  3. From the Insert menu, select New Metric. The Input Metric Formula dialog box opens.

  4. The window on the left displays the objects in the selected dataset. Double-click the first object to use to define the derived metric. This moves the object to the window on the right, which is called the derived metric definition window.

  5. Add a function or operator in one of the following ways:

    • From the operator bar above the definition window, click an operator.

    • You can also use the Insert Function Wizard to guide you through the process of creating a function. How?

  6. Double-click the second object used to define the metric, or enter a constant.

    More than two existing metrics can be used in a derived metric's formula. Add additional metrics, functions, and operators as necessary.

  7. Once you have created the desired expression, click Validate. If the expression is not valid, click Clear to delete the entire expression and start again, or modify the expression until it is valid.

  8. In the Metric Name text field on the upper right, type a name for the new metric.

  9. Click OK to apply the new metric to your document. The Input Metric Formula dialog box closes. The new metric is added to the appropriate dataset in the Datasets pane. If you selected a Grid/Graph before creating the metric, it is also added to that Grid/Graph.

You can now use the derived metric as you would any other dataset object. For example, you can drag and drop it from the Datasets pane to the Layout area to place it into the document.

Editing a derived metric

You can edit the derived metric, by right-clicking the derived metric in the Datasets pane and selecting Edit. The Input Metric Formula dialog box opens.

Related Topics