SSRS Report - Subgroup Totals

Question

I have an SSRS report that is currently pulling a single dataset. This dataset contains records of inventory we have. Each record is a separate asset.

I want my report to group by a certain field, and then subgroup by certain criteria that are determined with a couple different fields. Basically there is one parent group, and three adjacent subgroups. My grouping functionality is working correctly, however I am finding it difficult to add totals to each of the adjacent subgroups. When I add a total, it is totaling the specific field within the scope of the entire dataset, instead of limiting the total to just that subgroup.

How can I add totals per field within subgroup?

EDIT: Added sample data and explanation:

You can ignore the function code field, that is what I am using to group on the parent group.

asset number,description,first year,acquisition cost,function code
190,random asset,2008,5000,100
193,random asset45,2008,56000,100
197,random asset26,2014,3000,100
191,random asset27,2014,7000,100
192,random asset36,2013,15000,100

I can't seem to attach screenshots, so here goes..

In the report you can see three subgroups; Assets, AssetAdditions, AssetDeletions. In the tablix, you can see where these groups are positioned. You can also see a row directly beneath the group that is supposed to total the subgroup at the end. However, for some reason the scope is only taking into account the entire dataset. I have tried to modify the expression in the Sum function [Sum(acq_cost), "Assets"], adding in the scope parameter but then it does not allow me to even run the report. It yells at me saying that "Assets" is an invalid scope.

Answer 1

The easiest way I have done this in 2012 VS is to have it return as part of the data set and have it sum up the value.

For instance if you have a quantity for inventory, and you have a subset where you only want the total quantity for that set, you add another column to your dataset called TotalSetQuantity and the subtotal field will have the expression =SUM(Fields!TotalSetQuantity.Value) rather than =SUM(Fields!Quantity.Value).

You can try iif statements within your report like =sum(iif(Fields!ColA.Value=1,Fields!Quantity.Value,0) but I had some troubles getting that to work.

Hope that helps, I ran into this issue this past week and the first option worked like a charm for me.