Monday, February 20, 2012

MDX Hierarchy help

I have two calendar hierarchies in a 2005 SSAS cube. I have to make some calculations based on the date level, but of course, my calculated measures dont work when the wrong hierarchy is chosen.

I have tried to look at the .hierarchy and hierarchy() function but am unable to really understand how they work and I haven't been terribly successful looking online for info on them.

Has anyone had a similar issue? How did you test for the current hierarchy?

Here's an Adventure Works example, when either the Calendar or Fiscal hierarchy is selected on rows:

>>

With Member [Measures].[DateHierarchy] as

Axis(1).Item(0).Item(0).Hierarchy.Name

select {[Measures].[Order Quantity],

[Measures].[DateHierarchy]} on 0,

Non Empty [Date].[Calendar].[Calendar Year] on 1

from [Adventure Works]

-

Order Quantity DateHierarchy
CY 2001 11,848 Calendar
CY 2002 60,918 Calendar
CY 2003 124,615 Calendar
CY 2004 77,395 Calendar

>>

Another approach is to test whether the current member of each hierarchy is the default member (assuming that only 1 hierarchy has been navigated).

No comments:

Post a Comment