Creating metrics in the Report Editor - Microstrategy

You can create metrics in the Metric Editor, the Report Editor, or the Command Manager. This chapter as a whole describes the concepts behind metric creation in all of these components.

This section discusses metrics created on the fly in the Report Editor, which are called derived metrics. Derived metrics allow you to create calculations based on the data already available on a report. A particular group of derived metrics, shortcut metrics, uses pre-built formulas.

One category of shortcut metric, the transformation metric, is not a derived metric. Unlike other shortcut metrics, they must be calculated in SQL rather than in the Analytical Engine.

Derived metrics

A derived metric is developed in the context of a report, allowing you to create calculations based on the data already available in the report. For example, a report contains Revenue and Cost metrics. To calculate profit on the fly, create a derived metric in the Report Editor with the following definition:

([Revenue Metric] - [Cost Metric])

Derived metrics are always evaluated by the Analytical Engine. That is, a derived metric performs math between metrics (column math) in report data after it has been returned from the data warehouse. The definition of a derived metric is visible in the formula bar of the Report Editor. Formulas of non-derived metrics are not visible. In their structure, derived metrics:

  • may include one or more metric functions
  • are based on the attributes and metrics that currently exist in the report
  • may be simple or compound, and therefore will inherit the structure of whichever type you use.

For example, if you have a report with Calling Center, Unit Price, and Units Sold, you could create the following derived metrics:

[Unit Price] * [Units Sold] Max (Units Sold) {}

Derived metrics are local to the report in which they are created. A derived metric created in a report is not a new metric and is not available to other reports.

Derived metrics are usually compound metrics based on the metrics that already exist in the report. The expressions of these metrics may include simple functions and OLAP functions, as shown in the examples below:

Rank([Units Sold]) RunningSum<SortBy=(Month)>(Revenue)

Aggregation and Dimensionality

When you create a derived metric, you cannot access all of the advanced metric functionality, such as transformations and conditions. However, you can employ levels, or dimensionality.

If you define a derived metric using an aggregate function such as sum or maximum, you must also specify the level. The default is to group by nothing, as in SUM(Metric1){}. You can list specific report attributes, such as SUM(Metric1){Region, Employee} to force a specific level of aggregation. You cannot use other advanced metric capabilities. For example, filtering options such as Absolute and Standard filtering are not supported.

When aggregating data within a derived metric, the metric values are first calculated for the metric at the grid level and then the aggregate function in the derived metric is applied from the grid level to the level specified in the derived metric. For example, consider a report with Category, Subcategory, and Item in the report objects and Category, Subcategory in the grid. With a derived metric Category Sales defined as Max(Revenue) {Category}, the Revenue metric will be calculated at the Category and Subcategory level and the Category Sales metric will be calculated as the maximum of those Revenue values at the Category level.

Inserting Embedded Metrics

While you can create a non-derived metric such as SUM(Fact){~+} directly in a report, it becomes an embedded metric and exists only in the context of the report. Its definition can be edited or viewed, just as with a regular object. Metric formulas that contain facts or attribute forms will result in embedded metrics and will not be evaluated by the Analytical Engine like derived metrics.

Shortcut metrics

Shortcut metrics are based on metrics already included in a report and provide a quick way to add new metrics to the report. They are available when you right-click on a metric column or metric header and are based on the selected metric.

Shortcut metrics belong to one of the following categories:

  • percent-to-total metrics
  • transformation metrics
  • rank metrics

All shortcut metrics are derived metrics except for the transformation metrics, which must be calculated in SQL. All the shortcut types are described in more detail below.

Percent-to-total metrics

Percent-to-total metrics display the percent, in relation to a selected total of each item affected by the metric. The total can be by column, by row, by page, for each value of the attribute, or the grand total. Associated calculation levels are shown in the following table.

Percent-to-total metrics

An example of a percent-to-total metric begins with the following report.


If Percent-to-Column Total is selected as the metric type, the information is displayed as follows.


The following conditions apply to percent-to-total metrics.

  • Row and column percent totals refer to the uppermost and extreme-left positions, respectively.
  • Page percent totals affect all attributes on a page.
  • “Percent to All -> A1”, where A1 is an attribute, indicates that the calculation is performed across all elements of that attribute. An example is percent-to-all stores.
  • If a report does not contain attributes at a given percent-to-total level, the level is unavailable for that report.
  • In some cases, two or more percent-to-total calculations at different logical levels yield the same result. For example, Percent-to-Page Total data can be the same as Percent-to-Grand Total data in a single-page report.
  • The level of a percent-to-total metric remains constant once the metric has been calculated; subsequent manipulation does not affect it.

Transformation metrics

Transformation metrics apply offset values, such as “four months ago,” to the selected attribute. For the MicroStrategyTutorial, the offset values for the shortcuts are

  • two weeks ago
  • last month
  • month to date
  • last year
  • previous
  • last quarter

These transformations are included as examples in the Tutorial. In your project, the options are the transformations that have been created for the project.

For each of these values, you can select what to calculate:

  • normal to show unit figures for both the current values and the corresponding ones for the interval selected
  • variance to display the difference between the current values and the corresponding ones for the interval selected, for example, Revenue - (Last Year’s (Revenue))
  • variance percentage to calculate the difference, expressed as a percentage, between the current values and the corresponding ones for the interval selected, for example, Revenue - (Last Year’s (Revenue))/(Last Year’s (Revenue))

Rank metrics

Rank metrics apply a ranking number to the metric values for a given attribute. When selected, this shortcut metric provides break-by options for each attribute on the report.

By default, the rank derived metric performs an ascending sort. To change it to descending, edit the metric and replace <ASC=True> with <ASC=False>.

Using a transformation metric affects the results of all subsequent executions of the report.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Microstrategy Topics