Filtering - Microstrategy

What is a filter?

A filter is used to select the data for calculating the metrics in the report. It also restricts the attribute elements included in the report. In our example, we use the Month filter, which does not allow April, May, and December data to be included in the metric calculations. For our purposes, these months are not representative of the normal business cycle, so the filter excludes them from calculations.

The Month filter is included in the Supporting Objects directory in the Advanced Reporting Examples folder.

Report filter example

Add the Month filter to the Basic Report, in Design View. For step-by-step instructions, refer to the online help. When you re-execute the report, it looks like the following

If you do not want to create it yourself, this report is saved as Filter - Month Report Filter in the Tutorial.

Notice that the metrics have different values than in the Basic Report. For example, Leanne Sawyer’s contribution to revenue is $198,076. In the unfiltered report, her revenue was$316,786. In the Basic Report, all data for all months was retrieved from the data warehouse. The Revenue metric wascalculated using all months. In this filtered report, April, May, and December amounts are not considered, so this metric does not include them in its calculations.

What is a report limit?

After all the metrics are calculated, you may need to further restrict the data, without changing how the calculations were performed. For example, you want to see only the top ten employees from a report that ranks all the employee sales. If you apply a report limit, the data used to calculate the sales rank is not affected.

A report limit specifies a set of criteria used to restrict the data returned in the report data set after the report metrics are calculated. Because it is based on the report’s metric values, a limit is applied after all of them are calculated.

The Report Editor allows you to set limits on any metric you want to apply to the report. Report limits are defined using operators such as between and less than.

Report limit example

Open the Basic Report again and note that the number of rows is 34. Add a report limit to the Basic Report by following the instructions that follow.

1. Select Report Data Options from the Data menu.
2. Choose Report Limit under the Calculations folder.
3. Click Modify to access the Report Limit Editor.
4. Open the Sales Metrics folder. Double-click Revenue.
5. Change the Operator to be Greater than.
6. Enter a value of 320,000.
7. Click Save and Close.

The report is redisplayed, as shown below.

The report limit restricts the report data to only those employees with revenue above $320,000. For example, Sawyer is included on the Basic Report, but because herrevenue is only$316,786, she is not included on this report. Notice that the number of rows is now 32, where it was 34 before the report limit was applied.

The difference between report limits and report filters is very important, but it may still be a little hard to see in these examples. The next series of sample reports use other MicroStrategy functionality to further differentiate these two features.

The difference between report filters and report limits

Rank metrics apply a ranking number to the metric values for a given attribute. For an example, open the Sales Rank report. As shown in the following figure, this report is the Basic Report with two additional metrics-Revenue Rank and Revenue Rank (unfiltered).

These metrics rank employees based on the Revenue metric. The Revenue Rank metric uses the report filter in its calculation, while the Revenue Rank (unfiltered) metric ignores this report filter. This feature allows both filtered and unfiltered values on the same report. For example, when a filter for the Northeast region is added to the report, the calculation for Revenue Rank (the filtered metric) uses only the employees in that region. The unfiltered metric uses all the employees, regardless of region, to calculate its rank numbers. A complete example is provided in Filtering with rank below. Metric level filtering is also explained in more depth in Chapter 6, Metrics. In the report sample above, these two metrics display the same value because the report does not contain a filter.

Sorting on rank

To make the order of ranking easier to view, sort by the rank metric. In Grid View, right-click the Revenue Rank column and select Sort rows by this column. As you can see from the following report sample, the rows are re-arranged based on the value in the Revenue Rank column. The report data does not change, only its order on the report changes.

This report is saved as Sort by Revenue Rank.

Filtering with rank

Switch to the Design View to add the Month filter to the sorted report. When you re-execute it, note the changed values in the Revenue Rank metric. In the following sample, the rankings that have changed are highlighted.

This report is saved as Sort by Revenue Rank -Month Report Filter.

Look at Ian Benner to understand what has occurred. In the previous report, his revenue was $526,867, which placed him as the tenth highest revenue producer. In this new report, his revenue is calculated at$393,866 because the report filter is applied before the metric value is determined. The revenue does not include April, May, and December. His new revenue rank is calculated as five, since the report filter affects the data used to calculate the Revenue metric. However, the Revenue Rank (unfiltered) metric still returns a ten because it is set to ignore the report filter.

Report limits with rank

Open the Sort by Revenue Rank report. Notice that the highest rank is 34 and there are 34 rows in the report. Now, add a report limit of revenue greater than $320,000, as described in the Report limit example. Re-execute the report to see the following results. This report is saved as Sort by Revenue Rank - Report Limit - Revenue > 320K. Notice that the highest rank is now 32 and there are only 32 rows on the report. The last two rows from the previous report have disappeared because the revenue in each row was less than the report limit. None of the metrics changed values because a report limit does not affect how the metrics are calculated; the limit is applied at the level of the report after the metrics are calculated. Simultaneous report filters and limits Both report filters and report limits can be used on the same report because they are applied at different stages of the execution cycle. Right-click the Sort by Revenue Rank report on the Desktop to open it in the Design View for editing. Add the Month filter as the report filter and a report limit of revenue greater than$320,000, as described previously. Execute the report. The results appear as displayed in the following figure.

This report is saved as Sort by Revenue Rank - Report Filter & Report Limit.

Notice that the report is much smaller than either the Sort by Revenue Rank - Month Filter report or the Sort by Revenue Rank - Limit - Revenue > 320K report. Only 15 rows are returned, as opposed to 34 or 32. Also notice that the Revenue, Cost, Profit, and Revenue Rank values are the same as the filtered report. However, the Revenue Rank(unfiltered) values are the same as the Revenue Rank - Limit.
Why is this?

The first step in creating this report is calculating metrics. The data used in the metrics is restricted by the report filter, so information from April, May, and December is not included. All the metrics are calculated using this data, except for the unfiltered metric, which ignores the report filter. Its values are calculated on the full year’s worth of data.

The results after all the metric calculations are completed form the report data set. The report limit is applied to this data set. The employees with revenue less than $320,000 (the report limit) are removed from the display before the report is presented. Because the revenue is calculated on fewer months than the Revenue Rank - Month Filter report, more employees are discarded than from the previous limit. In other words, the limit stays the same (greater than$320,000), but the filter changes the data considered in calculating each employee’s rank.

A report filter affects the data used to calculate metrics, whereas a report limit does not affect how the metrics are calculated. Report limits are applied at the level of the report after the metrics are calculated.

What is a metric qualification?

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.

Whether or not the output level differs from the report level, a metric qualification affects the report data set. On the other hand, a report limit is applied after the metrics are calculated.

Metric qualification example

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

1. Double-click in the Report Filter pane to add a qualification.
2. Select Add a Set qualification and click OK. A set qualification is based on a metric or attribute relationships.
3. Click the browse button next to Output Level.
4. Select Calculate the output for the list of attributes. This allows you to select the output level for the metric qualification.
5. Select Brand under the Products folder and click > to add it to the Selected objects list.
6. Click OK.
7. Click the browse button next to Metric.
8. Select Revenue in the Sales Metrics folder.
9. Click OK.
10. Keep the Function as Metric Value, but select Greater than from the Operator drop-down list.
11. Do not change Value, but type 320000 in the box next to it.
12. 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. That is, only brands with more than $320,000 of revenue are included in those calculations. An alternative explanation of metric qualification To help you understand metric qualification better, you can think of it 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.

What is report as filter?

Report as filter allows you to create a report and use it as a filter to generate another report. It is a different way to achieve the same results as a metric qualification, but it is easier to understand and create. Because the logic used to generate the final report is clearer, MicroStrategy recommends using it rather than the metric qualification.

In Desktop, you select Add a Shortcut to a Report to access the report as filter functionality.

Report as filter example

To create the same report as the metric qualification example, open the Sort by Revenue Rank report in the Report Editor. Add a new report filter. Select Add a Shortcut to a Report and choose the Revenue by Brand report. Execute the report. Sample report results are shown in the following figure.

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

As with the metric qualification report, the metric values differ from the unfiltered Sort by Revenue Rank report. The values shown in the earlier figure are calculated only for the brands that are returned on the Revenue by Brand report chosen as the filter.