question

NatalieLLoyd-9229 avatar image
0 Votes"
NatalieLLoyd-9229 asked cooldadtx answered

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

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
image.png (11.8 KiB)
image.png (15.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

cooldadtx avatar image
0 Votes"
cooldadtx answered

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.





5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.