SSRS Total from sub-group to parent group

Question

I'm trying to calculate the total sum of a filtered child group in the cell "Anzahl" in the parent group. As you can see on the example screenshot, in the child group are two numbers 15 and 5 and the cell in the parent group show 24 instead of 20.

Screenshots:

https://dl.dropboxusercontent.com/u/17838009/screenshot.jpg https://dl.dropboxusercontent.com/u/17838009/screenshot2.jpg

=Countrows("table1_dc_evn_vpkn") 

This throws the error: "The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset."

All attempts with =Sum, =Count and =IIF queries give wrong results because it calculates without the filter of the child group.

I've also tried with some custom code:

Public Total As Integer =0

Public Function Sum(ByVal Value As Integer) As Integer
    Total = Total + Value
    Return Value
End Function

This didn't work either.

I'm searching for a possibility to get access on the content within the child group cells from the parent group. I think about something similar to:

=Sum(ReportItems!Textbox90.Value)

which doesn't work because of: "Aggregate functions can be used only on report items contained in page headers and footers".

How can I calculate the total sum of a filtered child group?

Answer 1

Try Sum(Max(Fields!FieldNameValue, "childGroupName")). Grouping at the Sum level doesn't make sense. You want to apply this group before you start summing.

Answer 2

You should put the aggregate functions you wish to apply to the group inside of a group header or footer. That way any filtering or grouping will be applied to them as well. Also, you should generally apply the aggregate function to field values and not report items.

If you need to have the data outside of the grouped rows you can set the scope parameter of the aggregate function to the name of the group in the report. The scope parameter is the second, optional, parameter passed to an aggregate function. In your case (I think) the name of the group is "table1_dc_evp_vpkn". So your aggregate expression would look something like:

=Sum(Fields!FieldName.Value, "table1_dc_evp_vpkn")

The scope parameter limits the aggregate to a scope within the report. This can come in handy when you want to reference an aggregate of the entire dataset from inside a group or other filtered section of the report as you can set the scope to be the name of the dataset itself and it will aggregate without fields.