Use of Previous() function in reverse date sorted data in SSRS?

3
votes

I'm trying to use the Previous() function in a report indicator to display an up or down arrow depending on if there has been an increase or decrease from last year. This works fine when the data is displayed in ascending order, but when displayed in ascending order the Previous() function no longer works, it just treats the top row as the oldest. I've created a custom code.PercentDifference (Num, PrevNum) to get the differences. To illustrate, this is a table in SSRS, Year Change is a calculated column using an expression:

Financial Year         Value     (Year Change) 
Apr 2007 - Mar 2008    30,000          0
Apr 2008 - Mar 2009    37,000        7,000
Apr 2009 - Mar 2010    42,000        5,000
Apr 2010 - Mar 2011    38,000       (4,000)

This works OK, since row 2 'sees' Current-Previous as 37000-30000. However when displaying by date descending like so I get this:

Financial Year         Value     (Year Change) 
Apr 2010 - Mar 2011    38,000          0
Apr 2009 - Mar 2010    42,000        4,000
Apr 2008 - Mar 2009    37,000       (5,000)
Apr 2007 - Mar 2008    30,000       (7,000)

Because row 2 sees Current-Previous as 42000-38000, and row 1 has no concept of row 2?!

It needs to look like this:

Financial Year         Value     (Year Change) 
Apr 2010 - Mar 2011    38,000       (4,000)
Apr 2009 - Mar 2010    42,000       (5,000)
Apr 2008 - Mar 2009    37,000        7,000 
Apr 2007 - Mar 2008    30,000          0

I don't think I can do it in the original dataset query, since this just pulls out lots of values against individual dates, then does some computed columns to e.g. work out financial year, month etc. The tables above are reporting sums grouped by year.

Any ideas how I can produce this?

Many thanks,

2
sql-server-2008vbareporting-services

2 Answers

1
votes

I used previous in a "reverse" way:

In my example I used the previous function for period and valuesale data, and normal value for previous data.

Also I hide the first row (no meaning to show), and show the last period at the footer

To control visibility of the first row use: =Previous(Fields!fiscalmonth.Value) is Nothing

enter image description here

enter image description here

0
votes

When you are sorting by date in the descending order, it seems instead of the previous value you need the next value. SSRS doesn't provide any next function unlike Crystal.

Here what you can try is creating a queried parameter which takes the value of field "Value" and write an expression like :

Parameters!Value.Value((RowNumber(nothing)) - Parameters!Value.Value((RowNumber(nothing)+1)

The main idea is you can get the value as an indexed array for example Parameters!Value.Value(i) will give you the ith record of the "Value" field.

And you'd need function RowNumber to get the running count of rows.

enter image description here