SSRS Previous Function not calculating first time around

0
votes

I have a report that I built in SSRS that calculates a sum of numbers for every business day going back 10 business days. There is a column group for each business day, and there are row groups for each item being totaled. The function to calculate the sum is a simple:

=Sum(Fields!INCOME.Value)

This returns data for example as such:

Data Output

Now I am creating a report to indicate the change in data from day-to-day. I figured this would be simple with the Previous() function in SSRS. So I took the same dataset and in a new tablix I replaced the original calculation with:

=Sum(Fields!INCOME.Value)-Previous(Sum(Fields!INCOME.Value),"ASOFDT2")

The Column group is called ASOFDT2. This seems to work for all but the very first column, I get the following as a result:

Data Output with Previous function

My question is, does anyone know why this calculation is not performing in the first group, but is for all the rest? How can I make it properly calculate the first iteration of this? For informational purposes, I'm using SQL Server 2014, and I am building the report in Visual Studio 2013.

I have also reviewed the SO question: Use of Previous() function in reverse date sorted data in SSRS? though I am not sure how to apply this to my problem. The first column is my most recent set of data and I cannot select one column newer and just hide it.

1
reporting-servicesssrs-grouping
Why do you think it's not working for the first column? What value do you expect to see there? The difference between 0 and 9.50 is 9.50, so it seems to be working to me. - Tab Alleman
The first column should show me the difference between 9.50 and 9.50, i.e the change from Thursday to Friday, I'm starting to see that the problem has to do with the way the dates are sorted, it's doing all the differences backwards to what I want. Though I'm not sure how to fix it yet. - BigDevJames
No, that's what the result under Thursday shows you. The current column (Thursday) minus the Previous column (Friday). The result under Friday shows you current (Friday) minus previous (which there is no previous, so zero). - Tab Alleman
How would I make it work in the other direction? I want to compare Friday to Thursday, not Thursday to Friday. - BigDevJames
Another solution is to return the desired data in your dataset instead of using report expressions - Tab Alleman

1 Answers

1
votes

The Previous() function gets the data in the Previous column. It doesn't have any understanding of what that data is, so if your columns are in Descending order, the Previous column will be the Next day, not the Previous day

It shouldn't be too hard to include the differential data in your dataset instead of trying to calculate it in the report. Just join the table to itself on YourDateColumn = DATEADD(dd, -1, YourDateColumn), and the right side of the join will have the previous day's data, and you can subtract for the difference.