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