Aggregation dependent on dimension attribute

Hi,

I have little tricky situation here and I'll try to describe it as accurately as possible...

Using Analysis Services 2005, I need to provide a measure in which the aggregation is basically a sum, but sometimes based on a maximum within a dimension member. Here's the situation:

Table: Event
Available fields: Event Group, Date, Attendance

Attendance is the measure and Event Group and Date (Time) are dimensions. Time has a Year - Month - Day hierarchy.

Event Groups have an attribute "Same Attendance" that signifies that the same people attended all events in that Event Group.

Example:
Event Group: "VB.Net Course" - Same Attendance = true
Related Events:
Nov 10, 2006 - Attendance = 12
Nov 20, 2006 - Attendance = 11
Dec 10, 2006 - Attendance = 10

Event Group: "SSAS Road Show" - Same Attendance = false
Related Events:
Nov 15, 2006 - Attendance = 40
Nov 25, 2006 - Attendance = 50
Dec 15, 2006 - Attendance = 60

What I need is:

Total attendance (Event Group - AllMember; Time - AllMember): 162 ( = max(Attendance) from VB.Net Course + sum(Attendance) from SSAS Road Show)
Attendance for Nov (Event Group - AllMember; Time - Nov): 102
Attendance for Dec (Event Group - AllMember; Time - Nov): 72
Attendance for Nov 20, 2006 (Event Group - AllMember; Time - Nov 10, 2006): 12

...so regardless what I select in the Time dimension, I always want it to count only Attendance = 12 for the Event Groups that have Same Attendance = true.

Please be as detailed as possible with your reply.

Thanks,
Sven

"...so regardless what I select in the Time dimension, I always want it to count only Attendance = 12 for the Event Groups that have Same Attendance = true" - but what happens when a time member outside the range of dates for that Event Group is selected? So, for Oct. 2006, would you count 12 or 0?|||

0

Good point. I should have said "to count a maximum Attendance of 12"...and yes, at least one of the Events of that Event Group must be in the selected period.

Sven

|||

I would probably use the attendance field from the database to create two physical measures. The [Sum Attendance] measure would use the Sum aggregate function and the [Max Attendance] function would use the Max aggregate function. Make those measures Visible=false. Then add a calc measure which would look something like this:

create member CurrentCube.[Measures].[Attendance]
as
([Event Group].[Same Attendance].[True],[Measures].[Max Attendance])
+ ([Event Group].[Same Attendance].[False],[Measures].[Sum Attendance]);

Will that work for you?

|||

Yes, it works....thanks.

I just have to add a little bit if [Same Attendance] is selected as a Dimension itself and drilled down into the individual members. That won't be a problem. Right now it still shows the Max + Sum when it should show only one or the other dependent on the CurrentMember.

|||

Sorry, I have to correct myself (again).

It is correct as long as you only look at the data by Event Group, which was all I needed so far, but as soon as you have multiple Event Groups with the [Same Attendance] = true, then it only takes the max of all of these. It would be nice to get the correct number across Event Groups.

I saw an approach with a recursive calculation that I may try to tweak to work here: Calculate for the Event Group first and then add the numbers up using the same function.

Sven

|||

I added the recursive calculation some time ago, but finally found a minute to post it here:

This is the new calculation: [Max Participants Per Event]:

IIF([Event].[Event Code].CurrentMember.Level IS [Event].[Event Code].[Event Code],

[Measures].[Max Attendance], *the one from furmangg's post

SUM(Descendants([Event].[Event Code].CurrentMember,[Event].[Event Code].[Event Code]), Measures.[Max Participants Per Event])

)

Then I take the max + sum as suggested.

Sven