locked
How to add calculated column from dynamic columns to a matrix RRS feed

  • 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

    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,

    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 "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
    • 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 "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
    • 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