How to report child groups total values separately in ssrs

0
votes

I have a report in SSRS 2012 containing two groups.

one is parent and the other one is a child group. both are row groups, parent group is according to month number (1 to 12) and child group is according to the values of a column from dataset named "State" ranging from 1 to 5. at the end of each child group, there's a total row and at the end of the report is the grand total of all child groups.

I need to show the sum of each child group at the beginning of report body (above table) or in report header. is there any way to do that in ssrs?

Btw child group is named "State" and parent group is "Month"

I put this expression in a random textbox : 

iif(Fields!State.Value=1, "Sum(Fields!Amount.Value, "State")", "0")

but it doesn't work!

2
sql-serverreporting-services

2 Answers

0
votes

There are a number of reasons your expression didn't work

iif(Fields!State.Value=1, "Sum(Fields!Amount.Value, "State")", "0")
  1. iif(Fields!State.Value=1) When inside of the group, this statement references the current row and is evaluated for every row in the group. If you are putting this in a random textbox, you've removed the row level context. You'd need to use First | Last | Max | Min to tell SSRS for which row you want to evaluate the State field
  2. The true portion of your expression "Sum(Fields!Amount.Value, "State")" is not a valid expression in any language. Although you have the correct number of double quotes, they are not in the correct place. When your if statement is true, the value you're asking it to evalulate is Sum(Fields!Amount.Value,. However, from there to the comma separator for the false expression you also have State")". What is it supposed to do with that?
  3. You can't embed an expression as a string value and have it be evaluated. If your expression was IIF(Fields!State.Value=1, "John","0"), the true value would return John. A string literal would only ever be evaluated as a string literal. Once the double quotes issue is addressed, your true value would return the text "Sum(Fields!Amount.Value, "State"), it would not evaluate it as an expression and return the Sum of the Amount across the State group or dataset.

It is possible to pull an aggregation out of the tablix and use it elsewhere on the report, but there are very few good reasons to do so and there are some problems you'll have to deal with to ensure it is accurate. By design SSRS makes it difficult because it is so easy to make a mistake (by you not SSRS) and present incorrect information. I highly recommend you start with a simple report and display your group totals within the group header/footer. You can also experiment with embedding objects (a tablix within a list?) so that you don't have to hack the report model.

0
votes

The place you used to add totals to your groups, it gives you two options for totals.

  1. Add Totals Before
  2. Add Totals After

You must have picked Add Totals After option.

  1. Now you can simple delete the totals row in your report.
  2. Select the Group Row and right click and select the option of Add Totals Before