August 4, 2010 at 2:20 pm
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
August 4, 2010 at 3:01 pm
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.
August 5, 2010 at 2:34 am
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!!
August 5, 2010 at 2:43 am
... 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