Level metrics in MicroStrategy

The level metric are very important topic in MicroStrategy Advanced reporting. Many of us has confusion that when we can use what type of filtering options while defining the level metrics.

Below are the basics of level metric filtering options.

The filtering setting for a level metric governs the relationship between the report filter and the calculation of the metric. The filtering options are:

1. Standard filtering - allows the report filter to interact as usual in the metric calculation. The metric calculates only for the elements found in the filter definition. The filter criteria for the report is found in the WHERE clause of the SQL statement which calculates the metric.
2. Absolute filtering - changes the filter on descendants of the target. It raises it to the level of the target, if possible.
• If the attribute in the metric filter is a parent of the attribute in the report filter, calculations are performed only on elements to which the report filter applies.
• If the attribute in the metric filter is of the same level or a child of the attribute in the report filter, calculations occur as specified by the report filter. Absolute filtering influences what is displayed on the report, not its calculations. It includes the report criteria in a subquery rather than in the WHERE clause itself.
• Notes : Lets say I have a report having the objects Category, Subcategory and Revenue metric. Report filter on few of the subcategory elements.  If you want to calculate the revenue at category level for displayed categories in the report(which contains only sub category elements in report) then you set filtering to absolute.
  
  Absolute filtering calculates the revenue at child level if the target is set to parent level. In the same scenario if the target is  set to the subcategory level and the template contains Items attribute Absolute filtering calculates the revenue at Item level.
3. Ignore filtering - omits filtering criteria based on the attribute in the target and its related attributes (parents and children). The report filter does not appear anywhere in the SQL for a metric with this setting.
4. None - can be summarized as unspecified-the filtering behavior for the target is not determined by this component. Instead, the target and group components of this level unit define the filter.
   • If the report includes an attribute in the same hierarchy as that indicated by the metric filter, aggregation takes place at the level of that attribute.
   • If the report does not include other attributes in the same hierarchy as that indicated by the metric filter, aggregation defaults to the "Absolute" option.

How Absolute and Ignore Filtering modify the results of the report:

Take for example the following report and metric:

Report Filter: Year = 2004

Because the Filtering is currently set to standard, then the report filter will interact with the metric calculation normally and apply the filter to the metric.

Now if the Filtering is changed to absolute, then again, nothing will change. Because the target is set at the Report Level, then the level of the target is not raised and the results remain unchanged.

However, if the Filtering is set to Ignore, then the Report Filter is ignored and additional 2003 data is displayed for the Level Profit as shown below:

This is because ignore Filtering will remove any report filters that are related to the target (parent or child). Because the Target is Report Level, which is the Month & Quarter, and Year is a parent of both of those, it is removed.

Major Difference between filtering Ignore and none in level metric.

Ignore:-  Specific attribute related will be ignored while metric calculation . It means In a metric calculation when filtering is set to ignore the metric filter and report filter will not get involved in the SQL pass where the metric is calculated.

None:- Entire report filter will not be used for that particular Metric calculation . It means In a metric calculation when filtering is set to none only a metric filter would get applied and the report filter will get ignored in the SQL pass where the metric gets calculated.

Below are the sample image which will shows you how data changes when we apply the different combination of filtering and grouping.