Report Builder 3.0 - difference between one row and next where value is an aggregate

Natalie LLoyd 21 Reputation points
2021-02-24T17:52:33.583+00:00

I have a table like the following

71638-image.png

Where the total count is a sum of values for the month/year, so the row groups are Year, Month

I would like to include another column that shows the running difference in count from one month to the next, like follows

71742-image.png
I am not sure how to achieve this. Any help would be appreciated.
Thank you

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,849 questions
0 comments No comments
{count} votes

Accepted answer
  1. Michael Taylor 50,026 Reputation points
    2021-02-24T18:56:18.313+00:00

    In your example you are trying to get data from the next row and I don't believe that is supported. However you can get to the previous row using the previous aggregate. I think this makes more sense anyway as you are showing the differences and differencing requires 2 rows so naturally the difference would appear on the second row.

    Note that previous, and a few other aggregates, cannot be added as a calculated field to a dataset as the ordering is not known at that point. You can however add the column to the table that is rendering that data.

    1. Add a new column to the Tablix (not the dataset)
    2. Set the value to the expression Fields!Total.Value - Previous(Fields!Total.Value)

    The report should then diff the current to the previous row. Note that you'll likely want to clear out the first row since it doesn't make sense to diff from 0.

    0 comments No comments

0 additional answers

Sort by: Most helpful