CODEWITHSUNDEEP
visual-studioreportingaggregateiif
elphj
elphj

Reputation:

Sum IIF Expression

Im trying to sum an expression in visual studio and just keep getting an #error but not sure why as this si the first time i have tried to sum an expression, only ever done it on a single field before. Any Suggestions!!!

 =IIf(Fields!STATUS.value = "Completed" AND Fields!DONOTINVOICE.value = True, Fields!ORDERCOST.Value, "")

Upvotes: 0

Views: 8707

Answers (5)

Vonrapp
Vonrapp

Reputation: 1

For Visual Studio, wrap the IIf in a Cdbl: 

=SUM(CDbl(IIf(Fields!STATUS.value = "Completed" AND Fields!DONOTINVOICE.value = True, Fields!ORDERCOST.Value, 0)))

Upvotes: 0

user3635498
user3635498

Reputation: 1

=Sum(IIf(Fields!STATUS.value = "Completed" AND Fields!DONOTINVOICE.value = 1.0, Fields!ORDERCOST.Value, 0.0))

You have to use ".0" at the end of value: this make sure that your return value of if expression is not string

Upvotes: 0

recursive
recursive

Reputation: 86074

The value of the IIf() will evaluate to a string ("") when your condition is false. You can't sum a string. Try using 0 instead.

Upvotes: 2

elphj
elphj

Reputation:

Ok couldnt figure out the sum on an expression so ive just used a case statement in a new dataset to build the sum feature. Example below, this is in an inner query and i have done a sum in the main bit. Just incase anyone else gets this issue this is how i resolved it.

CASE WHEN TBL_REPAIR_ORDER.STATUS = 'Completed' AND TBL_REPAIR_ORDER.DONOTINVOICE = 1 THEN TBL_REPAIR_ORDER.ORDERCOST ELSE 0 END AS Completed

Upvotes: 0

elphj
elphj

Reputation:

Do you mean like this, just tried that and dosent sum anything just get 0 :(

=Sum(IIf(Fields!STATUS.value = "Completed" AND Fields!DONOTINVOICE.value = 1, Fields!ORDERCOST.Value, 0))

Upvotes: 0

Related Questions