I have a matrix report that has the as of date (month end date) as a column. This part is fine, I have my as of dates going across the report from left to right For the row I have a currency field. I would like to add another row field that is the percentage change of my currency field from the current period to the prior period. For example, if I have $50 in Oct 2010, $40 in Sept 2010, $30 in August 2010, underneath column Oct 2010 I would like to have 25% for "Percent Change" field, and underneath Sept 2010 I would like to have 33% for "Percent Change" field. Any help is greatly appreciated. Thanks.

see if this link helps you - http://www.sqlservercentral.com/Forums/Topic556429-147-1.aspx#bm733379 You would have to make changes to your expression as per your requirement. Hope that helps. Cheers, ShalinShalin P. Kapadia

I tried that formula, making changes to suit my requirements, but that isn't quite working. It is always returning 1 or 100% I don't see how that formula is referencing the previous value? Would using the Previous Function be of any help? I tried using it but am getting a scope error. Thanks

Hi Jim, It can't be achieved in the current reporting services. I would suggest you use T-SQL to do it, then show the percent changes on your report. thanks, Jerry

Jerry, Thanks. That is what I ended up doing - doing all the calculation in the store proc. Jim

We needed to do something similar. We wanted to see the delta between the two columns (previous year and current year) and we came up with the following formula. It assumes that there are only two columns in the matrix (previous year and current year), that they are ordered lowest to highest (previous year is the first column, current year is the second column), and that the row amount being totalled is at the detail level. It was written with the table dataset being named DataSet1, the year field named QCY, and the amount field named Amount. Here is the formula: =IIF(CountDistinct(Fields!QCY.Value)=2, Last(Fields!Amount.Value)-First(Fields!Amount.Value), IIF(MAX(Fields!QCY.Value, "DataSet1")=MAX(Fields!QCY.Value),SUM(Fields!Amount.Value),-SUM(Fields!Amount.Value))) You place this formula on the detail row outside of the year group. Here is how the formula works: It first checks to see if there are two distinct values in the row for the year: IIF(CountDistinct(Fields!QCY.Value)=2,... If so, it takes the last amount value and subtracts the first amount value to get the delta between the two years (that is the easy part): ...Last(Fields!Amount.Value)-First(Fields!Amount.Value)... Otherwise, since there is an amount for only one of the years (there is no data row for the other year), we need to determine what year we have an amount for. So, we check to see if the year in the current scope is the oldest year or newest year by comparing it to the newest year in the entire dataset: ...IIF(MAX(Fields!QCY.Value, "DataSet1")=MAX(Fields!QCY.Value),... If the year in the current scope is the newest year, we simply return the sum of the amount as a positive number: ...SUM(Fields!Amount.Value)... If the year in the current scope is the oldest year, we simply return the sum the amount as a negative number: ...-SUM(Fields!Amount.Value)... That's it. You could of course modify your query instead to return the calcuated field instead of using this formula. Or, if you are sure you will always have an amount for both years on every row, you could simplify your formula quite a bit as follows: =Last(Fields!Amount.Value)-First(Fields!Amount.Value) Hope this helps, Bob