I have generated a Matrix report using SQL reporting services from a table in Database.
Table
Date - Item - cost
10/31/2009 - a - 1
10/31/2009 - b - 2
11/30/2009 - a - 5
11/30/2009 - b - 6
12/31/2009 - a - 9
12/31/2009 - b - 10
SQL Reporting Matrix Report
Item - 10/31/2009 - 11/30/2009 - 12/31/2009
a - 1 - 5 - 9
b - 2 - 6 - 10
Now, I need to calculate diff of each month in the report. The dates in the report are dynamic because the SQL reporting matrix auto generates columns, something like a pivot. Problem is I don't know how to refer these columns that are generated dynamically. Is there any way I can do this in SQL reports?
EDIT: Example.
In the above DataTable, the dates are not static, the new date gets every month.(one day for every month) So in the matrix report I dragged the [date] filed into columns position. So the [date] in the matrix report are dynamic.
The template looks like this
SQL Reporting Matrix Report template
Item - [date]
a - [Value]
b - [value]
Now the actual report which should look like is below. The Change10/31-11/30 is generated by (value of 11/30/2009)-(value of 10/31/2009). Now I want to calculate [changepercentage] column.. How do I calculate this? I can not refer [Change10/31-11/30] or [Change11/30-12/31] because I don't know how many dates are going to be in the data table. You know what I mean?
SQL Reporting Matrix Report
Item - 10/31/2009 - 11/30/2009 - 12/31/2009 - Change10/31-11/30 - Change11/30-12/31 - Changepercentage
a - 1 - 5 - 9 - 4 - 4 - 0%
b - 2 - 6 - 10 - 4 - 4 - 0%