Matrix Subtotals

I looked at RS a couple years back, around the release of SP1. The biggest issue I had was the lack of Aggregates of Aggregates. This still seems to be the case in RS 2005, is that correct?

So help me with a work around.

In a Matrix report how can I?

Have a calculation (Aggregate) that has a scope that is for both the Row and the Column.

Example:

What is the percent of total sales and percent of total category sales?

Q1 Q2 Total

Beverages

Lemonade

Sales $ 100 150 250

Percent of Bev 44%

Percent of Total 19% ?How do I do these Percentages in a Matrix?

Soda

Sales $ 125 125 250

Percent of Bev

Percent of Total

Total Beverages 225 275 500

Snacks

Peanuts

Sales $ 100 150 250

Percent of Snacks

Percent of Total

Cookies

Sales $ 200 250 450

Percent of Snacks

Percent of Total

Total Snacks 300 400 700

Total Sales 525 675 1300

In RS 2000 you could not have an Aggregate of an Aggregate, nor could you have a “compound scope”. Because of this I went back to that other software.

I would love a way to deal with this, I have many reports that use this functionality, and would like to move to RS. If I could use the Matrix, I could author reports in 10% of the time.

Ideas?

Unfortunately, this is still a limitation of the matrix. While we would like to have addressed it for SQL 2005, we did not have enough time. It is very high on the list of items for the post 2005 release.|||Thanks for your reply.
I did get a work around, thanks to Brian Larson.
Maybe in 2005 SP1?|||Can you please tell us how?|||My work around did not work as well as I had hoped. I Did get an E-mail from Brian Welker that the Aggregates of Aggregates issue is about 3 on the priority list. He said that they were currently working on the reporting engine. I did not get an ETA. Having this functionality will be a huge time saver for me over what I do in Crystal Today.|||

OK I can give a partial work around for this.First of all let’s assume that the column group name is “Quarter”.

Getting the 19% figure is easy, it is Sum(Fields!Sales.Value) / Sum(Fields!Sales.Value, “Quarter”)

Getting the 44% is easy but you have to hard code some things.

Create another field called “Beverages”.In the calculated expression put in something like: IIF(Fields!Group.Value = “Beverages”, Fields!Sales.Value, 0).You may need to do some conversion to Decimal or etc.

Then you can use this formula to get the 44%: Sum(Fields!Sales.Value) / Sum(Fields!Beverages.Value, “Quarter”)

This is the best I can come up with for a work around until Microsoft can get the Aggregates issue on the drawing board.(They will in the coming year I would expect)