SSRS Question -- Multi-group rollups -- recursive heirarchy 1

[hwkranger]

Anyone know how to do multi-group roll-ups?

Example:

Events Happen daily.
Events Happen During Morning, Noon, Night
People Do the events
People Report to people.

I have a drill down that shows: People reporting to people, the bottom person's events grouped and drilled to the day.

I want to have a sum of the events throughout the hierarchy.

Example data would look like:

Bob
Did Something - 150 Times
Did Something Else 300 Times

Carol (who reports to bob)
Did something 75 times
Did something else 200 times

John (who also reports to bob)
Did something 75 times
Did smeothing else 100 times

Bob gets Carol and John's sums.

underlying data looks something like:

Bob (all nulls)
Carol (Listing of data) (parent Id of bob)
John (listing of data) (parent Id of bob)

I'm able to get counts recursively, at each level, but not sub grouped (events)

Any help is appreciated

Cheers

 

[DietSquirt]

hwkranger, I keep hoping there is some way to aggregate multiple layers of groups, but I'm getting the sinking feeling that there isn't. Here's a bit of a thread on microsoft.com that I ran across, where someone is pleading for a way to do it, and finally someone from Microsoft answers him. The two paragraphs below are at the end of a long thread. I hope this isn't the final answer and someone comes up with a tidy way, but all over the forums people are asking the same question. (If I understand yours correctly.)

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=266724&SiteID=1

Microsoft: "Aggregate of aggregates" is a feature we don't currently support. It's a high priority item on our wishlist for a future release. For now, there is a workaround for this that involves using custom code (similar to what Andrew has suggested) and can be quite tricky to get it to work. The idea is that you would pass a detail field value to as an argument to the custom function, do the calculation in the function, add it to a variable you define in the custom code, and at the end (in the table group), return the variable value which is the grand total you are looking for.


Workaround Suggestion:
How about creating a second table in the report that uses the same datasource, and uses groups/filters to accomplish what you want.
My test shows that the approach of a second table that references the same datasource works well, and you don't get a performance hit, since the data is only retrieved once. That is good news, and for me this is a reasonable approach.
I have found every tool will have limits. The important thing is to find a tool that gives you many ways to solve a problem.

 

[hwkranger]

Thanks for the reply Diet, I've been struggling with this issue for weeks.

My approach to solving this was to use a cube to return my results -- which I didn't get all the way through, but from what I started, it looked promising.