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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics