Business Intelligence with MicroStrategy Cookbook

Changing the level of a metric filter

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 ...