IIF sum expression
I'm trying to sum the expression in visual studio and just keep getting #error but not sure why as this si the first time I tried to sum the expression, only ever did it in the same field before. Any suggestions!!!
=IIf(Fields!STATUS.value = "Completed" AND Fields!DONOTINVOICE.value = True, Fields!ORDERCOST.Value, "")
The IIf () value will evaluate the string ("") when your condition is false. You cannot sum a string. Use 0 instead.
You mean like this, just tried this and the docent sums nothing, just get 0 :(
= Sum (IIf (Fields! STATUS.value = "Completed" AND Fields! DONOTINVOICE.value = 1, Fields! ORDERCOST.Value, 0))
Ok couldn't figure out the sum in the expression, so ive just used a case statement on the new dataset to create a sum function. The example below is in an internal query, and I did the sum in the main bit. Just run someone who gets this problem, this is how I solved it.
CASE WHEN TBL_REPAIR_ORDER.STATUS = 'Completed' AND TBL_REPAIR_ORDER.DONOTINVOICE = 1 THEN TBL_REPAIR_ORDER.ORDERCOST ELSE 0 END AS Completed
=Sum(IIf(Fields!STATUS.value = "Completed" AND Fields!DONOTINVOICE.value = 1.0, Fields!ORDERCOST.Value, 0.0))
You must use ".0" at the end of the value: this will ensure that your return value if the expression is not a string
For Visual Studio, wrap IIf
in Cdbl
:
=SUM(CDbl(IIf(Fields!STATUS.value = "Completed" AND Fields!DONOTINVOICE.value = True, Fields!ORDERCOST.Value, 0)))