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
