KB16096: How to write a custom metric formula in MDX to implement a transformation in MicroStrategy Engine

MDX provides functions to operate on hierarchy members: for instance, to move to the previous or next member in the same level. Other functions, such as MTD, WTD, or YTD, construct a set over which measures should be aggregated based on time hierarchy designs. These functions can be used to create transformation metrics in Strategy.
The following report includes a one-to-one transformation to display the previous quarter's revenue, and a many-to-one transformation calculating year-to-date revenue. Note that the year-to-date metric must use the MDX Sum() function, which takes two arguments: the set over which the measures should be aggregated, and the measure itself.
"(., .CurrentMember.Prevmember)"
"sum(YTD(.CurrentMember), .)"

• Show the Revenue value for the previous member of this row's current member.
• Add up the Revenue values for a set of all quarters this year up to and including the current Quarter member.

 
Standard MDX functions that support transformation behavior include:

• Member functions: These functions return a single attribute element and are intended for one-to-one transformations. They may, as above, be placed in parentheses along with a metric identifier to shift the metric's reference point.
  
• .PrevMember: Returns the immediately preceding element at the same level (like a "previous period" transformation).
• .Lag(n): Returns the n-th preceding element at the same level. PrevMember is the same as Lag(1).
• .NextMember: Returns the immediately following element at the same level.
• .Lead(n): Returns the n-th later element at the same level. NextMember is the same as Lead(1).
• ParallelPeriod(Level, Number, Member): Returns the corresponding element relative to a parent element's sibling. This function is good for transformations such as "same quarter/month/day last year."
  
• Level: The parent serving as the reference point. In the "same x last year" example, this would be Year.
• Number (optional): The number of parent elements to look backward. If not specified, it is assumed to be 1. To look forward, use a negative number.
• Member (optional): The child element whose cousin will be looked up relative to the parent level. ParallelPeriod(., 1, .) would return January 2004. Normally, the member argument will involve the CurrentMember function in some way. If not specified, this will be the current member of the dimension containing the given level.
• Set functions: These functions are designed for one to many transformations (one attribute element for the result row, referring to several elements over which the metric will be calculated). An aggregation function should be used along with the set transformation, e.g., Sum({ YTD(.CurrentMember) }, .).
  
• PeriodsToDate(Level, Member): Returns the siblings under the same parent element, up to and including the given member. For instance, PeriodsToDate(., .) would return the set {January 2005, February 2005, March 2005} -- i.e., year-to-month. If March 2005 were replaced by a specific date, the function would behave as year-to-date.
  
• Level: The parent level.
• Member: The child member that will be the end of the set.
• YTD(Member): Year-To-Date. This and the similarly-named functions that follow depend on the existence of a time dimension in the cube. The time dimension is specified in the cube's design and must be configured in the cube provider's interface. There is no way in MDX for Strategy to force a dimension to take the time dimension role.
  
• QTD(Member): Quarter-To-Date.
• MTD(Member): Month-To-Date.
• WTD(Member): Week-To-Date.
• LastPeriods(Number, Member): Returns the previous number siblings of the input element. This function is ideal for "rolling n-period" analysis. For instance, LastPeriods(3, .CurrentMember) would support a rolling three-month metric, provided Month is on the report template. If a different attribute were on the template, the groups of three would be evaluated relative to that attribute.
  
• Number: How many siblings to include, going backward. To look forward, use a negative number.
• Member: The reference element for the set.

Note: There may be syntax or implementation differences between OLAP Cube providers. Consult the MDX documentation for the OLAP Cube database platform for details on member and set functions that may be used for transformations.
Note: Whenever a custom MDX metric formula uses the CurrentMember function on a hierarchy, one or more attributes from that hierarchy must be present on the report. If this condition is not met, the metric may return empty values, or the report may fail with an error.
It is valid to include the transformation attribute in the report filter while excluding it from the template, provided that only one element of that attribute is chosen. A report with Category on the template and a month-to-date metric is valid as long as it filters on a single date. Attempting to filter on more than one date in this context is likely to produce an error or incorrect results, because the month-to-date in metric must have a different value for each selected date. Without the date attribute on the template, the report would have to display multiple values in the same cell, which is not possible. If it is necessary to filter on multiple elements, the corresponding attribute must be on the template.
If the attribute in the transformation is not present in either the template or filter, then by definition the transformation cannot apply. This is also true in the Strategy SQL Generation Engine, where transformations are omitted if there is no reference to one of the transformation's member attributes in the report.
For additional details on creating custom MDX metric formulas in Strategy, consult the following Strategy Knowledge Base technical note.
B16057 (KB5200-81x-2345): How to create customized metric expressions for OLAP Cube data sources in Strategy Engine 8.1.0