Subtotal error on calculated field in a Reporting Services Matrix

Question

I've got a Reporting Services report that has two row groups: Category and SubCategory. For columns it has LastYearDataA, ThisYearDataA, LastYearDataB, ThisYearDataB.

I added two columns (one for A and one for B) to handle an expression calculation (to show a percentage different from LastYear to ThisYear for each). That's working. The problem comes in the SubTotal for each category. The raw numbers are totaling correctly. If SubCat1 has 10//5 for LastYear/ThisYear A, and SubCat2 has 5//1, then I get 15/5 for the totals. But I get the percentage reported in the total column as "50%", matching SubCat1. Percentages for each Subcategory are being calculated correctly (according to my backup math, anyway). But the sub total % always matches the first SubCategory in the group. Is this impossible to do in Reporting Services 2005?

Answer 1

Well, I never got to the bottom of this. I changed my sproc to return category totals and a grand total along with all the data. This is going to come up again, so I'll keep looking and maybe I'll discover why with more searching.

Answer 2

The solution is change the value of the detail field in the Matrix. So where you have:

=Fields!FieldName.Value

Change it to:

=Sum(Fields!FieldName.Value)

When it's displaying a value in a normal row or column, I guess it just sums the one value, so it doesn't change it, but when it's in a subtotal column, it sums them like you wanted. Counter-intuitive I know. I had to search to figure it out too.