Chart Percentage Differences in Reporting Services

1
votes

How can I chart year on year percentage change in a reporting services chart?

From my source data (which is a SharePoint 2010 list) I can use the "Previous" function in a table to work out the percentage change over time in years. I have row groups (the years shown below) and column groups (A, B and C).

      A | B | C
-----------------
2010  
2011  3%  4% 5%
2012  2%  3% 1%

The formula in the table is (year 2 rate - year 1 rate)/year 1 rate. The first row is blank because I have an iff function checking if there is a previous row, and to show nothing if that is the case. Otherwise it does the calculation.

I cannot use the previous function in a chart. How can I graph this? If this was a normal database I would look to doing something at that level and present the to reporting services, but with a SharePoint list I can not and have to do the grouping and other work in reporting services (2008 R2) itself.

Any ideas? I am open to presenting the data in another way if that can effectively provide some view of how the data has changed over time in a chart.

1
reporting-serviceschartsssrs-2008

1 Answers

0
votes

To extract another years set of use the lookupset function and the sum function e.g.

=sum(fields!number.value) - sum(lookupset(fields!year.value - 1, fields!year.value, fields!number.value, "dataset")

Further reading http://msdn.microsoft.com/en-us/library/ee240819.aspx

Also the lookupset function returns all matched values and the lookup function returns the first only.