SSRS - Sum fields that are in group rows - a custom RUNNING TOTAL excercise

Here's the problem: You are writing an SSRS report and you want to sum a value that is in a group header. In Crystal we would do this using a running total field. (sigh)

In the image below, we're written a SOP report and we want to sum the DOCAMNT field. If we just do that, we get a number that is way too big, because it sums for each line in the order. The SSRS way to fix this is to use variables; but that approach is so inelegant.

What we really want is in the DOCAMNT2 column:

Here's a technique that makes this very simple

Related Articles

... and you 'll find more on the SQL (General) Menu

The query below is the data source for the order. All the magic happens in the DOCAMNT2 field. I've included the ROWNUMBER field in the example so you can better see what's happening. We put the DOCAMNT into the data set only once, so standard SSRS summing will work. No variables needed

 

select top 100
        h.sopnumbe, 
        --we won't use this field
        h.docamnt,
        l.itemnmbr,
        l.quantity,
        l.unitprce,
        L.xtndprce,
        --we won't use this field either, it's here for demo purposes
        ROW_NUMBER() over (partition by h.sopnumbe order by h.sopnumbe) as RowNumber,
        --this is where the magic happens
        case when (ROW_NUMBER() over (partition by h.sopnumbe order by h.sopnumbe)) = 1 then h.docamnt else 0 end as docamnt2
    from sop10100 h
        join SOP10200 l on l.SOPNUMBE = h.sopnumbe
    where h.SOPNUMBE in ( '529605','570683')

Here is the report design. Column 2 is bad, Column 3 gives us what we want. Nothing tricky in SSRS, all the magic is in SQL

Resulting data set. You can easily see why a sum of DOCAMNT would be too high, and a sum on DOCAMNT2 would be perfect.

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences