Search code examples
sqlreporting-servicesreportaggregate

SSRS SUM() a grouped value outside of group

I have a dynamically driven grouping, in this case group of values based on 3 items of their net and qty given a dynamic date range, grouped by month. I would like to get the total Net & QTY for each row, as I already have a group total by column.

So under [Total Quantity], I would like to get the sum of the result under Net which is already a value of [Sum(Net)]. I have used the following code: =Sum(ReportItems!Textbox15.Value) to only get the error:

The Value expression for the textrun 'Textbox8.Paragraphs[0].TextRuns[0]' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers.

What would be a better way to get these results?

enter image description here

enter image description here

Solution

  • Change your expression to

    =SUM(Fields!Net.Value)

    The expression will be exactly the same as the Net column that is inside your Product column group.

    SSRS evaluates expressions based on the scope of the containing object, so in your case the scope of Textbox15 is inside both a row and column group, so the SUM only sums values inside those groups. By adding a column outside your Product column group, that textbox's scope is only within the row group, so the SUM adds up everything within that row group only and ignores column groups.

    You can achieve the same thing by right-clicking your column group name (below the main report design) and selecting "Add Total / After"