MDX for Months across and years down

There was a question recently in the Olap newsgroup at microsoft.public.sqlserver.olap about how to create an MDX query with months across and years going down. Typically the answer to such a question would be to break up the time dimension and put years and months in their own dimensions. By default, and in tools such as Excel this is typically an impossible query. The rules of MDX state that a given dimension cannot participate in more than one axis in a query.

However...

You can create calculated measures (or indeed calculated members in any other dimension) that will give you the effect you are after. Below is an example of some MDX that works against the Foodmart database that will give you the first 6 months across the page and the years going down the page.

Why only the first six months?

Because:

a) I'm lazy
b) it is a lot of repetition and 
c) you can get the idea from just the first six months . :)  

 

WITH  
  MEMBER
Measures.[Jan] as 'sum(filter(descendants(time.currentmember,[time].[month]),Time.currentMember.name ="1"),Measures.[Unit Sales])'
  MEMBER Measures.[Feb] as 'sum(filter(descendants(time.currentmember,[time].[month]),Time.currentMember.name ="2"),Measures.[Unit Sales])'
  MEMBER Measures.[Mar] as 'sum(filter(descendants(time.currentmember,[time].[month]),Time.currentMember.name ="3"),Measures.[Unit Sales])'
  MEMBER Measures.[Apr] as 'sum(filter(descendants(time.currentmember,[time].[month]),Time.currentMember.name ="4"),Measures.[Unit Sales])'
  MEMBER Measures.[May] as 'sum(filter(descendants(time.currentmember,[time].[month]),Time.currentMember.name ="5"),Measures.[Unit Sales])'
  MEMBER Measures.[Jun] as 'sum(filter(descendants(time.currentmember,[time].[month]),Time.currentMember.name ="6"),Measures.[Unit Sales])'

SELECT
{Measures.[Jan]
  ,Measures.[Feb]
  ,Measures.[Mar]
  ,Measures.[Apr]
  ,Measures.[May]
  ,Measures.[Jun] }
ON COLUMNS,
time.year
.members ON ROWS
FROM
[Sales]

Print | posted on Wednesday, September 28, 2005 11:48 AM

Comments on this post

No comments posted yet.

Your comment:

 (will show your gravatar)