About Metric Qualifications

A metric qualification is a filtering condition based on the value of a metric. It contains an output level, which determines the level at which the metric is calculated and to which attributes the metric applies. Like every filter, a metric qualification changes the nature of the metric calculations, unlike a report limit, which is applied after the metrics are calculated.

Recall that the level of the Basic Report is Region and Employee—the attributes on the report. The output level of the metric qualification can remain at the report level, or it can be changed.

If the output level is the same as the report level, the results are usually the same as using a report limit. This is just a coincidence, however, because report limits and metric qualifications are calculated differently and at different times in the report execution cycle.

If the output level differs from the report level, the metrics are calculated at the output level. In the example that follows, the report level is region and employee. In the previous reports, the metrics were calculated for each employee using all brands and all products. When a metric qualification with an output level is applied to the report, the metrics are calculated with only the data that meets the metric qualification. Working through the sample report will help you better understand metric qualifications and output levels.

Regardless of whether the output level differs from the report level, a metric qualification affects the report result set. On the other hand, a report limit is applied after the metrics are calculated.

Example: Metric Qualification

Right-click the Sort by Revenue Rank report on MicroStrategy Developer and select Edit to edit the report. Add a metric qualification by following the steps that follow.

To Add a Metric Qualification

Double-click in the Report Filter pane.
Select Add a Set qualification and click OK.
A set qualification is based on a metric or attribute relationships.
Click the browse button next to Output Level.
Select Calculate the output for the list of attributes. This allows you to select the output level for the metric qualification.
Select Brand under the Products folder and click > to add it to the Selected objects list.
Click OK.
Click the browse button next to Metric.
Select Revenue in the Sales Metrics folder.
Click OK.
Keep the Function as Metric Value, but select Greater than from the Operator drop-down list.
Do not change Value, but enter 320000 in the box next to it.
Click OK.

Execute the report. The results are displayed in the following figure.

This report is saved as Sort by Revenue Rank - Report Filter - Metric Qualification at the Brand Level.

The metric values on the report are different from those calculated for the Sort by Revenue Rank report. The Sort by Revenue Rank report produces values for each employee for all products. On the other hand, the metrics on this report are calculated only on those brands with revenue greater than $320,000 because of the metric qualification.

In the Sort by Revenue Rank report, Fred Strome's revenue rank was nine, with revenue of $541,361. On this metric-qualified report, his revenue is $353,170, because any brands with revenue less than $320,000 were not included in the Revenue metric calculation. While his unfiltered revenue rank remains the same, he has moved up to eight in the revenue ranking. The unfiltered metric does not include the metric qualification, so it is calculated on all brands, and therefore, all products. In contrast, the metric qualification affects the other Rank metric, just as it affects the Revenue, Cost, and Profit metric calculations. Therefore, only brands with more than $320,000 of revenue are included in those calculations.

You can think of a metric qualification as creating a temporary report. When the report is executed, the metric qualification first generates a temporary report in the background. In the earlier example, that report is a list of brands. The qualification is applied, so the report is trimmed to include only those brands with revenue in excess of $320,000. This report looks like the following.

This report is saved as Revenue by Brand.

Then this temporary report is applied to the actual report. Metrics are calculated including only those brands listed on the temporary report—Sony, Sharp, Panasonic, and so on. In essence, this report is the same as creating a filter for the set of brands Sony, Sharp, Panasonic, and so on. However, unlike that filter, the metric qualification is dynamically calculated based on the Revenue metric at the brand level. When new revenue data is added, the values can change.

In many cases, a report limit can generate more efficient SQL than a metric qualification. A metric qualification is contained in a separate pass of SQL, generating a temporary table at the output level. When this table is joined to the rest of the output, it limits the data included in the other metric calculations. Because it is another table, a metric qualification is a separate step in report execution. In contrast, a report limit is contained in a HAVING or WHERE clause in one of the final SQL passes. Therefore, using a report limit reduces the number of SQL passes needed to execute the report. However, since they often yield different results, do not choose a report qualification or a limit based solely on SQL efficiency.