Answered by:
How to add calculated column from dynamic columns to a matrix
Question

Hi all,
I’m creating a report in SSRS 2005 with an olap cube.
I have created a data set that retrieves the following data:
Year
Type
SubType
Profit
2008
Commercial
Fee
200
2008
Commercial
Interest
34
2008
Investment
Fee
245
2008
Investment
Interest
32
2009
Commercial
Fee
125
2009
Commercial
Interest
32
2009
Investment
Fee
123
2009
Investment
Interest
45
I added a matrix to the report to show the sum of profits like this
2008
2009
Commercial
Fee
200
125
Interest
34
32
Investment
Fee
245
123
Interest
32
45
I would like to add a calculated column based on column 2008, 2009. For example add a column
2008
2009
20092008
Commercial
Fee
200
125
75
Interest
34
32
2
Investment
Fee
245
123
122
Interest
32
45
13
Somebody can help/advise how to accomplish that?
Thank you,
Monday, February 1, 2010 3:21 PM
Answers

Hi Martha,
Since you are running SSRS, i would suggest you achieve this in report services. By default, on the matrix, the subtotal is the total of the line and cannot do the subtraction operation. But we can overwrite their values via an expression as requested. For your report, i assume the matrix column group is "matrix1_Years". Now, you can click the matrix data cell and locate its value property in the Properties box at the right hand side, and type the following expression in the value expression box:
=IIF(Inscope("matrix1_Years"),SUM(Fields!Profit.Value),SUM(IIF(Fields!Years.Value="2009",Fields!Profit.Value,0))SUM(IIF(Fields!Years.Value="2008",Fields!Profit.Value,0)))
and enable the column group's subtotal and input "20092008" in its header cell.
After these steps, you should be able to get the report just like the last figure in your post.
thanks,
Jerry Marked as answer by Jerry Nee Tuesday, February 16, 2010 7:38 AM
Tuesday, February 9, 2010 3:21 AM
All replies

Hi Martha,
Since you are running SSRS, i would suggest you achieve this in report services. By default, on the matrix, the subtotal is the total of the line and cannot do the subtraction operation. But we can overwrite their values via an expression as requested. For your report, i assume the matrix column group is "matrix1_Years". Now, you can click the matrix data cell and locate its value property in the Properties box at the right hand side, and type the following expression in the value expression box:
=IIF(Inscope("matrix1_Years"),SUM(Fields!Profit.Value),SUM(IIF(Fields!Years.Value="2009",Fields!Profit.Value,0))SUM(IIF(Fields!Years.Value="2008",Fields!Profit.Value,0)))
and enable the column group's subtotal and input "20092008" in its header cell.
After these steps, you should be able to get the report just like the last figure in your post.
thanks,
Jerry Marked as answer by Jerry Nee Tuesday, February 16, 2010 7:38 AM
Tuesday, February 9, 2010 3:21 AM 
Hi Jerry
Thanks for your response above, this works well for me if the years are static, as in your example. However my years (in my case, its actually months) are dynamic, i.e. they are selected by the user in a parameter list. So i assume i need to refer to the parameters like so:
SUM(IIF(Fields!Calendar_Month.Value=ParameterName.Value(0))
and
SUM(IIF(Fields!Calendar_Month.Value=ParameterName.Value(1))
However when i try this, the calculation doesn't happen, i just get 0 in the difference column. If i change it back to static values it works.
Any ideas how i can get this working with dynamic column names?
Thanks!
Doodles
Thursday, February 3, 2011 3:41 PM 
another question!
when you click on Add Totals as you describe above, should an expression be automatically generated for you as it's not doing it for me. I have to manually type in an expression...
Thursday, February 3, 2011 4:06 PM