Example 3: Removing report level - Microstrategy

Report requirement

You want to compare the sales performance of the targeted areas to the total sales in the company everywhere and for all time.

Solution

To display this report, remove the report level target that is present by default, and add any attribute as the target with no grouping, rather than including multiple attribute levels in one metric.

By removing the report level from the target and with no grouping for any other available attribute, there is no GROUP BY clause in the SQL. Any attribute can be used for this purpose. There is no 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 in order to obtain the desired outcome.

Condition

Metric conditionality applies a filter to the metric calculation. You can think of conditionality as attaching a filter to each metric. For example, a report shows revenue by region and uses the revenue metric. Revenue for all years is included on the report. If you associate a 2003 filter to the revenue metric and re-execute the report, the report displays data for 2003 only.

Only metrics with an aggregate operator in the formula can use conditionality. Only one filter can be associated with a simple metric, although that filter can contain as many filtering conditions as needed.

To create a report that compares the monthly sales to January sales, define the following metrics:

• Revenue (report level)
• Sum(Revenue) {~}
• January Revenue (level of Month of Year, standard filtering, no grouping; condition of January)
Avg(Revenue) {~, [Month of Year]} <January>

Consider Revenue as a metric for calculating January Revenue

• Monthly Revenue (level of Month, standard filtering, standard grouping)
• Sum(Revenue) {Month}
• Variance from January
• ([Monthly Revenue] - [January Revenue])

Place these metrics on a report with Month to achieve the following report:

Advanced options for metric conditionality

The process of merging the report and metric filters is accomplished by embedding one filter in the other or by embedding both filters in a new, empty filter. The Advanced options for conditionality allow you to select how the report filter and metric filter interact, as described below:

• Merge report filter into metric: is the default setting. The report filter criteria is applied to the data first. Then the metric filter is applied to the results of the first evaluation.
• Merge metric condition into report: evaluates the metric filter first, then applies the report filter to those results.
• Merge into new: intersects the metric and report filter.

These options are relevant only if at least one filter is dynamic, meaning that the filter results depend on when it is executed. For example, filtering on “country=US” always yields the same results. However, filtering on “country where revenue is greater than $1000” can return different results if the data is for 2002 only, 2003 only, or both years combined. For example, you want to identify the bottom ten items in terms of revenue but you have placed an additional qualification on this: only those items where sales are greater than$30. You can solve this problem using the embedding options of metric conditionality.

The first qualification, the bottom ten items in terms of revenue, is contained within the metric, and the second, items with a value greater than $30, is in the report filter. By changing the embedding methods to control the interaction of these two filters, you alter the outcome. • Merge into New: Merge into New intersects the metric filter and the report filter. In the example above, the result set includes only those items that were in the bottom 10 in terms of sales and had sales greater than$30. The report returns 10 rows of data.
• Merge Report Filter into Metric: This is the default setting. First, the report filter criterion is fulfilled and then the metric filter is applied to that result set. With this option chosen, first all the items having sales greater than $30 are found. Of these items, the bottom 10 sales are determined, so the report returns 10 rows again, although the data is different. • Merge Metric Filter into Report: In this case, the metric filter is evaluated first, and based on this result set, the report filter is applied. That is, the bottom 10 sales are determined and then only those items with sales greater than$30 are returned on the report. The number of rows on the report will probably be less than 10, since some of the 10 items returned by the metric filter are likely to have sales less than \$30.

The Remove related report filter elements check box also influences the interaction between the metric filter and the report filter. When it is selected, if the report filter contains a qualification based on an attribute related to an attribute in the metric filter qualification, the metric filter qualification takes precedence.

For example, the metric filter is set to New York only and the report filter to all western regions. The metric filter overwrites the report filter, so only New York is included in the report. If the check box is cleared, the results are the intersection of the filters. In this case, New York and West exclude each other, so the combined filter is empty.

By default, this option is selected since metric filters ignore report conditionality by default.

You can remember the difference between the advanced options available under the Level and the Condition components of a metric by remembering the following:

• The Add attributes in the filter to the level option determines whether the metric filter is applied to the metric calculation.
• The Remove related report filter elements option defines whether the report filter is applied to the metric calculation.

The Remember option setting check box allows you to save your settings as defaults.

Transformation

Transformations are generally used for time-series analysis, for example, to compare values at different times such as this year versus last year, or month-to-date.

A transformation metric is an otherwise simple metric that takes the properties of the transformation applied to it. For example, a metric calculates total revenue. Add a transformation for last year and the metric now calculates last year’s total revenue. Any transformation can be included as part of the definition of a metric and multiple transformations can be applied to the same metric.

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

Microstrategy Topics