We already know how to create filters on metrics to restrict data returned by a query using a HAVING clause (Chapter 4, Objects – Facts and Metrics). In this recipe, we will go a step further; we want to filter on a metric value calculated at a different level than the report. Say that you want to know which products sold more than 1,000 units on a yearly basis all over the world, and you also want to see the countries where the products were sold. You have a report with Year, Product, and Country, so the Sum OrderQuantity from FactResellerSales metric is aggregated at year, product, and country level; but we need it to be aggregated only at year and product level for the filter to work. Here comes the qualification Output Level, as shown in the following screen capture:
The qualification Output Level is independent from the report, it only affects the filter. You need to ask yourself a question: how do I want the metric to be aggregated before filtering the report? If we need the products that sold more than 1,000 units per year, we need the order quantity aggregated by year and product. It's like creating a pre-report with Year, Product, and the metric and then use the pre-report as a filter. Those combinations of Year and Product that pass the filter (greater than 1,000) are later displayed in the real report, together with Country.
First, we create a report with the year on pages, Product on rows and OrderQuantity from FactResellerSales on columns filtered on values greater than 1,000, as shown in the following image:
You can see an example of the eight products that sold more than 1,000 units in the year 2006. We will use this report as a reference to check the results.
Now we create another blank report with these objects:
- On page-by: Year.
- On rows: Country and Product.
- On columns: Sum OrderQuantity from FactResellerSales.
- Now add the same metric to the report filter, Set Qualification appears.
- In the Output Level group see that there's None selected, click on the ellipses button.
- In the Level dialog box, click on the last radio button labeled Calculate the output for the list of attributes.
- Then in the shopping cart select Year on the left and move it to the right.
- Move the Product attribute also from left to right and confirm with OK.
- Change Operator to Greater than and in Value type
1000. - Click on OK and run the report.
- Notice that the numbers are indeed less than 1,000, that's because the filter considers only Year and Product, while the report groups by Year, Product, and Country.
- We can easily confirm the correctness of the results thanks to dynamic aggregation. Click on the Country header and drag it onto the Report Objects list.
- The Intelligence Server automatically aggregates the result, displaying the same numbers as in the reference report, as you can see in the following image:
In this type of filter, MicroStrategy runs a first SELECT with group by a11.ProductKey, a12.CalendarYear having sum(a11.OrderQuantity) > 1000, and stores the result in a temporary table. It then runs another SELECT, joining the fact table with the temporary table, this time grouping on a15.CountryRegionCode, a11.ProductKey, a12.CalendarYear.
I find this type of filter very useful and a big time saver.
We can get to the same result creating a Sum OrderQuantity from FactResellerSales (Year, Product) level metric, removing Report Level and adding Year and Product in the dimensionality grid. As often there is not a single way to achieve the desired outcome, and if the numbers are correct, it really depends on how comfortable you feel with one solution or the other. Check the SQL view every now and then to assess the impact of a query also on the DBMS side.
Filtering a report based on a metric is different than putting a filter inside a metric definition (refer to Chapter 4, Objects – Facts and Metrics); a filter inside a metric only affects that particular calculation not the entire report.