MDX to normalize set of measures

Question

I am trying to create a dataset for an SSRS report as documented here:

http://sqlblog.com/blogs/stacia_misner/archive/2010/10/08/29249.aspx

The challenge is that I have multiple measures who's data I want to include in the measure column and I want to include the name of the measure in the RowValue column. So where the following query returns only data for measure "Sales Amount":

with
member [Measures].[Measure] as [Measures].[Sales Amount]
member [Measures].[RowValue] as [Product].[Category].CurrentMember.Name
member [Measures].[ColumnValue] as [Date].[Calendar Year].CurrentMember.Name
select {[Measures].[Measure], [Measures].[RowValue], [Measures].[ColumnValue]} on columns,
non empty ([Product].[Category].[Category].Members, [Date].[Calendar Year].[Calendar Year].Members) on rows
from [Adventure Works]

What I want to do is run the following type of query but have the data returned in the structure of the query above which would allow me to plug it into an SSRS report matrix:

WITH 
       MEMBER measures.SalesAmount AS [Measures].[Sales Amount]
       MEMBER measures.CustomerCount AS [Measures].[Customer Count]
       MEMBER measures.InternetFreightCost AS [Measures].[Internet Freight Cost]
SELECT [Date].[Calendar Year].[Calendar Year].Members ON COLUMNS, 
    {measures.SalesAmount,measures.CustomerCount,measures.InternetFreightCost} ON ROWS
FROM [Adventure Works]

Do any of the MDX ninjas know if this is even possible with MDX?

What flexibility do you need with regard to the query structure described in the article you cite? Do you have other queries that need to feed the same report that have only one measure and an arbitrary hierarchy in the rows and another in the columns? Or do all your reports have one hierarchy in the columns (like the years in your sample), and one or more measures in the rows? — FrankPl
Hello Frank, I have only one hierarchy in the columns (always date). But I do have multiple measures that I need to include. — Brian Amersi

FrankPl FrankPl · Accepted Answer · 2013-10-10T16:10:27

with member [Geography].[City].[Sales Amount] as 1
     member [Geography].[City].[Customer Count] as 1
     member [Geography].[City].[Freight Cost] as 1
     member [Measures].[RowValue] as [Geography].[City].CurrentMember.Name
     member [Measures].[ColumnValue] as [Date].[Calendar Year].CurrentMember.Name
     member [Measures].[Measure] as 
            CASE
                WHEN [Geography].[City].CurrentMember IS [Geography].[City].[Sales Amount]
                     THEN ([Measures].[Internet Sales Amount], [Geography].[City].[All Geographies])
                WHEN [Geography].[City].CurrentMember IS [Geography].[City].[Customer Count]
                     THEN ([Measures].[Customer Count], [Geography].[City].[All Geographies])
                WHEN [Geography].[City].CurrentMember IS [Geography].[City].[Freight Cost]
                     THEN ([Measures].[Internet Freight Cost], [Geography].[City].[All Geographies])
            END

select {[Measures].[RowValue], [Measures].[ColumnValue], [Measures].[Measure]}
       on columns,
       { [Geography].[City].[Sales Amount], [Geography].[City].[Customer Count], [Geography].[City].[Freight Cost]}
         * 
       [Date].[Calendar Year].[Calendar Year].Members
       having [Measures].[Measure] <> null
       on rows
from [Adventure Works]

should deliver what you want. I used [Geography].[City] as an utility hierarchy. This can be any hierarchy unused in the query. I chose this one, as it is unrelated to both measure groups used in the query, and hence very unlikely to be used in any query. Some Cube designers create one or two one-member dummy dimensions in their cubes that are unrelated to any measure group, and can be used just like here in order to create calculated members on them.

One of the difficulties with ReportingServices queries is that measures must always be in the columns, and no other hierarchy may be in the columns. Hence, if we want to have the measures in the rows, we must move them to another hierarchy. This is done in two steps: First, we create dummy members on the utility hierarchy, and then map these to the measure needed in the CASE construct of the [Measures].[Measure] definition, where we need to use the default member of the utility dimension (in most cases the All member) in order to get something different than the 1 that I used for the dummy value.

Finally: non empty does not work properly with this construct, as [Measures].[RowValue] and [Measures].[ColumnValue] are never null. Hence I replaced it by HAVING, which can look at specific column values within the row.

MDX to normalize set of measures

1 Answers