KB16100: How to write a custom metric formula in MDX to filter on an attribute in MicroStrategy Engine

• Strategy

This article describes how to write custom MDX metrics in MicroStrategy

Filtering an individual metric in OLAP Cube data sources using custom metric formulas requires awareness of whether the filtering attribute is required on the template, or should be omitted.
In MDX, attributes may not be used in the SELECT and WHERE clauses simultaneously. Only attributes in the WHERE clause are considered true filters, in the sense that they restrict the data set prior to producing the aggregated data. For instance, to execute a SQL report with Category on the template, but filtering on Quarter, the database must exclude all fact-level data that do not meet the Quarter condition, and then aggregate the remaining data to the Category level. This is handled similarly in MDX.
A SQL report that filters on Quarter and also includes Quarter on the template uses a similar execution flow. MDX handles this situation very differently, however. The use of Quarter on the template means that Quarter cannot also go in the WHERE clause. To render the Quarter filter, the specific attribute elements are placed into the SELECT clause. OLAP cube databases execute this query by choosing the metric values only for the specified elements, and no others. The end result is the same, but the methodology is entirely different.
The difference in methodology means that there are separate MDX formulas to restrict metric results for the scenario of the filtering attribute being present on, or absent from, the template. The two syntaxes are not interchangeable. Using a formula incompatible with the template design will produce unexpected results, or errors from the OLAP Cube data source.
Filtering a Metric When the Filtering attribute is Not on the Template
The general syntax to filter a metric when the filtering attribute is not used on the template is to place the metric and the attribute element together in parentheses:
(Metric technical name, Element ID)
For example, to filter the Revenue metric based on the Northeast region, the metric formula should be:


"([Measures].[REVENUE2], [REGION2].[01])"

If multiple attribute elements need to be used in the filter, a different formula should be used. This formula will work equally well with a single element, making it suitable for required element list prompts.
Non-prompted:


"Aggregate({[CATEGORY2].[3], [CATEGORY2].[4]}, [Measures].[REVENUE2])"

 ApplySimple("Aggregate({[CATEGORY2].[3], [CATEGORY2].[4]}, #0)", Revenue)

Prompted:


ApplySimple ("Aggregate({#0}, #1)" ?[Category Prompt], Revenue)

NOTE: This formula depends on supplying a set of hierarchy members inside the curly braces { }. If the prompt is optional and the user does not answer the prompt, the set will be empty and the metric will return empty values. Thus, it is not advised to use this type of formula for optional prompts.

Filtering a Metric When the Filtering Attribute is on the Template
According to MDX documentation, the correct way to handle this is to compare the current member of a hierarchy to a specific element using the IS operator.
For instance, if a metric (here, against Microsoft Analysis Services) should return a value only for one element of the Department Level 02 attribute, the following formula may be used:


ApplySimple("IIf([Department].[Departments].CurrentMember IS [Department].[Departments].&[2], #0, NULL)", Amount)

Not all OLAP cube data sources support the use of IS in this context. Microsoft Analysis Services supports it; SAP BW has returned errors in Strategy's testing. For SAP BW, the following syntax may be used.


"IIf([REGION2].CurrentMember.Name = [REGION2].[01].Name, [Measures].[REVENUE2], NULL)"

It is essential to include the Name function when using = in place of IS. If the function is omitted, the condition will compare the metric value for the current member against the metric value for the specified member. It is always possible that two or more elements might have the same metric value; in that case, that value would be displayed for each of those elements, not just the one indicated in the filter.

For additional details on creating custom MDX metric formulas in Strategy, consult the following Strategy Knowledge Base article.

KB16057: How to create customized metric expressions for OLAP Cube data sources in Strategy Engine

Comment

0 comments

Details

Knowledge Article

Published:

May 11, 2017

Last Updated:

May 11, 2017