Calculated Measure does not appear in all the drilldowns

Hello,

I created a hierarchy [Time].[Year-Month] that has the following

-Year

--Period

English Month

I want to return the headcount from the 1^st Period only. This works fine. I did it by making a new measurement called LastActive by using the LastNonEmpty aggregate function. I then made a calculated measure called BeginHeadCount as the following:

([Measures].[LastActive], [Time].[Period].&[1]). This works OK, sort of.

When I browse the cube, and I put the [Time].[Year-Month] hierarchy on the columns I don’t see the BeginHeadCount for each English Month, but I do see it for the totals. For example, I see the BeginHeadCount for the Year, then I hit the + and drill-down to the Period, I see it in Period, I hit the + again to drill-down to the English Month – the breakout is now English Month and Total. The Total does show the BeginHeadCount but it is blank in the English Month.

How do I get the BeginHeadCount calculated measure to also appear in the English Month?

I assume that this has something to do with referring specifically to the &[1] period. But I don’t know how to resolve this.

Thank you for the help.

Your issue here is that you have specified a tuple to calculate your value.

A tuple can best be described as an intersection in multi-dimensional space, so what you are essentially saying is to return the [BeginHeadCount] measure where Period.&[1] intersects with each of the members in the current query. What this gives you are intersections where no data exists such as period 1 and month 12.

I am guessing that what you really want to say is "regardless of the current time members, show me the amount from Period 1". You would do this by using an aggregate function that will return a numeric amount from the calculation rather than a tuple. Something like the following should do the trick, you would have to include the current member from the year attribute in the calcuation so that it does not sum up period 1 for every year (which would not make any sense).

SUM( {([Time].[Year].CurrentMember,[Time].[Period].&[1])}, [Measures].[LastActive])

|||

You didn't describe the attribute relationships among the attributes: [Year], [Period] and [English Month] of the [Time].[Year-Month] hierarchy. My guess is that this isn't a natural hierarchy - if [Period] is like [Quarter Of Year] and [English Month] like [Month Of Year] in the Adventure Works [Date] dimension, you might try:

([Measures].[LastActive], [Time].[Period].&[1], [Time].[English Month].[All])

|||

Darren/Deepak,

Thank you for the great answers. They were both a great help. I am now able to see the data the way I wanted to.

Another quick question... Deepak, when you say that it was your guess that the relationships between the attributes wasn't a natural hierarchy, what do you mean by that? I did create a new hierarchy that has the Year-Period-English month. Is this different then a natural hierarchy? What make a natural hierarchy?

Thanks for the information. Hopefully my new MDX book will come this week and I wont ask these newbie questions

|||

"What make a natural hierarchy?" - here's an entry from Mosha's blog which discusses this:

http://sqljunkies.com/WebLog/mosha/archive/2006/11/09/natural_hierarchy.aspx

>>

What are the natural hierarchies and why they are a good thing

...

A natural hierarchy is composed of attributes where each attribute is a member property of the attribute below. For example, the Geography hierarchy Country, State, City and Name is a natural hierarchy if City is a member property of Name; State is for City; and Country is for State. The hierarchy Gender-Age is not a natural hierarchy because Gender is not a member property of Age.

...

It should be clear by now, that whenever possible, unnatural hierarchies should be avoided. But this doesn't mean that unnatural hierarchies are always bad ! Any guidance should be considered within its reasoning. For example, in my article about Time Calculations in UDM, I showed how using unnatural hierarchies in the form Year -> QuarterOfYear -> MonthOfQuarter -> DayOfMonth, actually simplifies a lot writing time relation calculations.

...

>>

|||Thank you for all your help!