none
Issue with calculation in matrix and inscope expression RRS feed

  • Question

  • Hello,

    I have created a matrix report and I have there are 4 data elements in the report.  Those elements are Month, ShipDate, Actual and Plan.  In the matrix, the shipdate is in my row group and the month and static values of actual and plan are in the column group.  I have then added a column to the right of the plan column that is a formula calculating the percent effective (actual / plan).  When I add in the subtotal, the value of the total percent is incorrect.  I believe this is an inscope issue but I can not get my expression to work.

    This is the expression that I am using in the percent effective cell.  Is this expression wrong?  Does my error relate to calling a specific cell (Fields!SHIPDATE.Value)?

    =iif(InScope(Fields!SHIPDATE.Value),(Sum(Fields!actual.Value, "AccessDB")/Sum(Fields!plan.Value, "AccessDB")),(Fields!actual.Value/Fields!plan.Value))

    Any help would be greatly appreciated.

    Wednesday, March 17, 2010 8:33 PM

Answers

  • Yes.  I think the corrected expression should be the following because if you use a direct field reference without aggregate function in a matrix cell, it picks the first row (not all rows) of that matrix cell scope:

     

    =iif(InScope(Fields!SHIPDATE.Value),(Sum(Fields!actual.Value, "AccessDB")/Sum(Fields!plan.Value, "AccessDB")),(Sum(Fields!actual.Value)/Sum(Fields!plan.Value)))

    HTH,
    Robert

     


    Robert Bruckner   http://blogs.msdn.com/robertbruckner
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Saturday, March 20, 2010 4:17 AM
    Moderator