OLAP: MDX Examples

Problem 1: Displaying Information About Members

We want on row per quarter, but for each quarter, we also want to display the corresponding year. Here an example with an output where we do the same thing on the column axis with the Region dimension (displaying both the State and City):

Solution 1.1: Displaying Information in Cells

It is not possible to add have tuples with two members from the same dimension (here: Year and Quarter are both from the Time dimension), so one alternative is to display the year in a cell instead.

    with 
        member Measures.Year as 'time.currentmember.parent.name'
    select 
        { Measures.Year, [Measures].[Store Sales] } on columns,
        [Time].[Quarter].members on rows
    from Sales

And here we have a more complex but realistic example using the same method.

    with 
        member Measures.Year as 'time.currentmember.parent.name'
        member Measures.Quarter as 'time.currentmember.name'
    select 
        { 
            { ( [Store].[All Store], Measures.Year ) } , 
            { ( [Store].[All Store], Measures.Quarter ) } , 
            CrossJoin( { [Store].[Store Country].members } , {Measures.[Store Sales] }) 
        }  on columns, 
        [Time].[Quarter].members on rows
    from Sales

Solution 1.2: CrossJoin with Duplicate Dimension

Here we create an additionnal dimension [Year] with the years as members. We CrossJoin [Year] with [Time] and then Join the two dimensions.

    select
        { [Measures].[Store Sale] } on columns,
        Filter (
            CrossJoin(
                { [Year].members },
                { [Time].[Quarter].members }
            ),
            [Year].currentmember.name =
                Ancestor([Time].currentmember, [Time].[Year]).name
        ) on rows
      from [Sales]

Problem 2: Output in a Cell a Combination of Values

In this example below we display in each cell the "sale" value along the percentage it represents across all regions, displayed in parenthesis. We do this with the non-standard Format() function.

    with
        member [Measures].[Sale] as '
            Format([Region].currentmember, "$#,### ") ||
            Format([Region].currentmember /
            [Region].currentmember.parent, "(0%)")
        '
    select
        { [Period].[Period's Year].members } on columns,
        { [Region].[All Regions].children } on rows
    from [Sales]
    where ( [Measures].[Sale] ) 

Problem 3: Output in a Cell Different Values Depending on Some Condition

In the example below we add a <font color="red"> tag around the value along with the "number of units sold" if the "sale" value is below a certain threshold:

    with
        member [Measures].[Sale] as 'Iif (
            [Measures].[Total Price] > 1000000,
            Format([Measures].[Total Price], "$#,###"),
            "<font color=""red"">" ||
            (Format([Measures].[Total Price], "$#,###") ||
            ("</font><br/>Units: " ||
            (Format([Measures].[Total Units], "#,###"))))
        )'
    select
        { [Period].[Period's Year].members } on columns,
        { [Region].[All Regions].children } on rows
    from [Forecast]
    where ( [Measures].[Sale] ) 

References

• Introduction to Multidimensional Expressions
• MDX Function List