MDX Recipes for Non-Measure Calculated Members

Defining Age Members

It is often useful to have members that group records by age. To define such members, use an existing time level and the special NOW member. For example, consider the MonthSold level in the HoleFoods sample. You could define a calculated member named 3 Months Ago with the following value_expression:

[dateofsale].[actual].[monthsold].[now-3]

For example:

WITH MEMBER CalcD.[3 months ago] as '[dateofsale].[actual].[monthsold].[now-3]' 
SELECT calcd.[3 months ago] ON 0, {MEASURES.[units sold], MEASURES.target} ON1 FROM holefoods
 
                              3 months ago
1 Units Sold                             37
2 Target                             254.00

Defining a Hardcoded Combination of Members

In many cases, it is useful to define a coarser grouping that combines multiple members of the same level. To do so, create a non-measure calculated member that has a value_expression of the following form:

%OR({member_expression, member_expression,...})

For example:

%OR({colord.red,colord.blue,colord.yellow})

Each non-measure member refers to a set of records. When you create a member that uses the %OR function, you create a new member that refers to all the records that its component members use.

For example:

WITH MEMBER CalcD.[primary colors] as '%OR({colord.red,colord.blue,colord.yellow})' 
SELECT calcd.[primary colors] ON 0, 
{MEASURES.[%COUNT], MEASURES.[avg test score]} ON 1 FROM demomdx

Defining a Combination of Members Defined by a Term List

Term lists provide a way to customize a Business Intelligence model without programming. A term list is a simple (but extendable) list of key and value pairs. You can use term lists in the multiple ways; one is to build a set of members, typically for use in a filter.

In this case, you use the %TERMLIST function and the %OR function; create a non-measure calculated member that has a value_expression of the following form:

%OR(%TERMLIST(term_list_name))

Where term_list_name is a string that evaluates to the name of a term list.

For example:

%OR(%TERMLIST("My Term List"))

This expression refers to all records that belong to any of the members indicated by the term list (recall that %OR combines the members into a single member).

The %TERMLIST function has an optional second argument; if you specify "EXCLUDE" for this argument, the function returns the set of all members of the level that are not in the term list.

Aggregating Ranges of Dates

Another useful form uses a range of members aggregated by %OR:

%OR(member_expression_1:member_expression_n)

The expression member_expression_1:member_expression_n returns all members from member_expression_1 to member_expression_n, inclusive. This form is particularly useful with time levels, because you can use it to express a range of dates in a compact form.

For time levels, you can also use the special NOW member. The following expression aggregates sales records from 90 days ago through today:

%OR(DateOfSale.DaySold.[NOW-90]:DateOfSale.DaySold.[NOW])

Or use the following equivalent form:

%OR(DateOfSale.DaySold.[NOW-90]:[NOW])

You can also use the PERIODSTODATE function to get a range of dates. For example, the following expression gets the range of days from the start of the current year to today and aggregates these days together:

%OR(PERIODSTODATE(DateOfSale.YearSold,DateOfSale.DaySold.[NOW]))

Defining a Member as an Intersection of Other Members

In some cases, typically when you define a filter, it is useful to define a member that is an intersection of members. Suppose that you need a filter like the following (which does not show literal syntax):

Status = "discharged" and ERvisit = "yes" and PatientClass="infant"

Also suppose that you need to use this filter in many places.

Rather than defining the filter expression repeatedly, you could define and use a calculated member. For this calculated member, specify Expression as follows:

%OR({member_expression,member_expression,...}

For example:

%OR({birthd.year.NOW,allersevd.[003 LIFE-THREATENING]}

The expression (birthd.year.NOW,allersevd.[003 LIFE-THREATENING]) is a tuple expression, which is the intersection of the member birthd.year.NOW and the member allersevd.[003 LIFE-THREATENING] — that is, all patients who were born in the current year and who have a life-threatening allergy.