How to add calculated column from dynamic columns to a matrix
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
Sub-Type
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
2009-2008
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,
February 1st, 2010 6:21pm
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 "2009-2008" 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
Free Windows Admin Tool Kit Click here and download it now
February 9th, 2010 6:21am
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
February 3rd, 2011 11:02am
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...
Free Windows Admin Tool Kit Click here and download it now
February 3rd, 2011 11:24am