SSRS Totaling column with many groups

1
votes

I have an SSRS report thats query returns many results. They are grouped to aggregate them by item group and division, as well as a couple of other groups. Whats happening is the total line is taking the total for ALL of the result lines instead of the grouped lines. I have tried adding a scope to the sum expression in the report but to no avail. All I really need it to do is sum up the visible columns. Heres a shot of my groups: enter image description here

enter image description here

enter image description here

If i run the report, look at the totals for the columns, they are way off: enter image description here

This should be 29,329 but it is (i believe) totaling everything outside of the groups. Is there a way to just sum the column so I can get the amount desired?

1
sql-serverreporting-servicesssrs-2008-r2
This isn't clear: are you trying to build a sub-total row, or do you have some expression that is setting the visibility of certain rows, and you want to apply the same expression to your total row? – Tab Alleman
I have a single total row with all rows and columns visible. I just wish to sum a column, as in the example. the red lines in the second picture denote where the groups are – rigamonk
It still isn't clear. You say you believe that everything is being summed in the total. Well, what shouldn't be summed in the total? Why are some rows not showing? – Tab Alleman
Because (and this is just an educated guess) the query returns many rows. these rows are grouped by the item process group. This group does not show all item process groups, but only those with what are called NCRs. I've added a picture of the datasets fields that show how they are grouped, and what is shown. Its really hard to explain. The groups filter in the rows that are applicable. not ALL rows in the query. so i'm only showing the rows for some of the items. Thats why I want to simply total a column based on the values that are on the columns that show up on the report. – rigamonk
Ok, so can you apply the same filter to the Totals row that you apply to the item process group? – Tab Alleman

1 Answers

0
votes

You may need to recreate your groups again.

from the looks of your diagram, your total row is Outside your group Item_Process_Group, so the [sum(Sales_Quantity)] is summing all groups (as your comment suggest.

If your total ([sum(Sales_Quantity)]) was on the (empty)cell above where its displayed on the pic above, it should total correctly