MicroStrategy ONE
About the report level of a metric
Before you begin
This topic assumes a basic understanding of level metrics. See Level metrics: A practical overview for background information.
When you create a metric, the level is set by default as the report level. This means the metric calculates at the level of the attributes on the report on which the metric is placed. The level of a metric is indicated between curly braces ({ }) in the metric definition, as shown in the following simple Revenue metric:
Sum(Revenue) {~+}
The tilde (~) represents the report level with standard filtering, denoted by the plus sign (+). If you add item as a level, the metric definition changes to reflect the addition:
Sum(Revenue) {~+, Item+}
Notice that the report level has not been deleted. Keeping the report level allows the metric calculation to adapt to different reports. For example, create a metric with a level of Item. When you use the metric on a report with the Region attribute, that attribute affects the metric calculation, along with the level explicitly set on the metric (Item). If you put the same metric on a report with Customer, that attribute, as well as the metric level, is used in the metric calculation. In other words, the report level in the metric level tells the engine to group by all the attribute IDs on the report. The content of the report is reflected in the metric calculation.
Removing the report level
If the report level is removed, only the level explicitly set on the metric affects the metric calculation, regardless of the attributes placed on the report. For example, remove the report level from the revenue metric described previously. The metric definition now looks like the following:
Sum(Revenue) {Item}
Place this metric on a report with the Region attribute and two other revenue metrics, one with report level only and the other with report level and item. The report results are shown below.
The Revenue: Report Level and Revenue: Report & Item metrics calculate the same result, providing the revenue for each region. The Revenue: Item metric calculates the revenue for all items, for all regions, in each row. The number is therefore the same for each row, since the metric does not differentiate between regions. The number is the same as the grand total of the report.
As shown above, removing the report level allows you to easily remove grouping and calculate the metric across all hierarchies. The Revenue: Item metric can be used to compare the sales performance of targeted regions to the total sales in the company everywhere and for all time. You could include multiple attribute levels in a revenue metric, to create a "total sales" metric. A quicker and easier solution is to remove the report level target that is present by default and add any attribute as the target with no grouping, as in the Revenue: Item metric above.
By removing the report level from the target and with no grouping for any other available attribute, the SQL does not generate a GROUP BY clause. Any attribute can be used for this purpose. You do not need to add more than one attribute, unless a special filtering behavior is required for the metric. If a special filtering behavior is required, then other attributes are required but they should not be grouped.
This is a quick and easy way to do something that might otherwise involve multiple steps. It is especially helpful if you have many dimensions represented on a report that need to be included in the metric calculation to obtain the desired outcome.