SSRS sum values based on row number

Question

I would like to get the sum of all values in a column IF they have a rowNumber greater than the current row

so if i have 4 rows:

Row 1 would be the sum of rows 2,3 and 4

Row 2 would be the sum of rows 3 and 4

Row 3 would be the value of row 4

Row 4 would be 0

This is the query I currently have:

SELECT
  no_,
  name AS member,
  amount,
  effective,
  balance

FROM c

WHERE 
    status != 'closed'
    AND amount != 0.00
ORDER BY no_, effective

In my ssrs table I'm using the expression RowNumber("member") to assign row numbers for each row per member

Gordon Linoff Gordon Linoff · Accepted Answer · 2018-10-02T14:25:37

You would seem to want a cumulative sum in reverse order, excluding the current value. I would express this as:

select sum(val) over (order by rownum desc) - val

I am clear how this relates to your query.

You can also express this using a window frame:

select sum(val) over (order by rownum rows between 1 following and unlimited following)

The only difference is that this would return NULL for the last row rather than 0.

SSRS sum values based on row number

1 Answers