Running value on formula field

  • Hi,

    SSRS 2005:

    In the details section of my report I have got the following formula:

    =IIf (Fields!YearMonthRep.Value = Parameters!YearMonthRep.Value AND Fields!TransactionType.Value = "Realisations", Fields!Quantity.Value, 0)

    I made a grouping on EmployeeNumber.

    Now I would like to add a sum of this formula into the EmployeeNumber group. So the total of Fields!Quantity.Value should be visible in the EmployeeNumber group.

    Somehow I always get "0" (zero) or "#Error" as result, while some rows in the details section show quantities. I tried mainly RunningValue as aggregate function.

    I think the formula should be something like this:

    =IIf (Fields!YearMonthRep.Value = Parameters!YearMonthRep.Value AND Fields!TransactionType.Value = "Realisations", RunningValue(Fields!Quantity.Value, sum, "EmployeeNumber"), 0).

    .. but apparently that's not correct. It end up with a zero.

    I tried some other variants, but all in vain ...

    =Sum (IIf (Fields!YearMonthRep.Value = Parameters!YearMonthRep.Value AND Fields!TransactionType.Value = "Realisations", Fields!Quantity.Value, 0)) ends up with an "#Error".

    I think it is quite simple, but I can't get it figured out.

    Someone any idea what the correct syntax might be??

    Thanks,

    Michiel

  • There is a simpler approach. Name the text box with the formula in the detail that works. For my example I will call it txtFormula

    In your group use a formula of = Sum(ReportItems!txtFormula.value)

    The expression builder may give you a little bit of a hard time over ReportItems, but save it and preview the report anyway and see if it works.

  • Hi

    Using ReportItems didn't work.

    I solved it another way:

    - I created a new calculated field in the dataset (right click in dataset) with the exact expression that I used in my details section

    So new calculated field ("SumQty") with: =IIf (Fields!YearMonthRep.Value = Parameters!YearMonthRep.Value AND Fields!TransactionType.Value = "Realisations", Fields!Quantity.Value, 0)

    - I placed that new field in the details section (and converted it to double (Cdbl))

    So: =CDbl(Fields!SumQty.Value)

    - I placed a runningvalue formula in my group referencing to my new field

    So: =RunningValue(Fields!SumQty.Value, sum, "EmployeeNumber")

    ... and that did the job!!

  • ... oeps, I forgot to mention that the RunningValue has to be converted to double as well.

    So:

    =RunningValue(Cbl(Fields!SumQty.Value, sum, "EmployeeNumber")

    That's all folks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply