CODEWITHSUNDEEP
sqlvisual-studioreporting-servicesiif
user1086159
user1086159

Reputation: 1055

iif blank show zero

I have a visual studio report that I want to state that if a calculation box is blank due to there being no figures available on that particluar project then show zero.

My calculation is :- =((Sum(Fields!TotalCost.Value, "Accrued") + Sum(Fields!TotalCost.Value, "serv1")) / (Sum(Fields!Quantity.Value, "serv1") + Sum(Fields!Quantity.Value, "Accrued"))
)

And I want to try and include an IIF statement in that to show zero if blank.

Any ideas on how to best achieve my aim?

so far I have got to =iif((Sum(Fields!TotalCost.Value, "Accrued") + Sum(Fields!TotalCost.Value, "serv1")) / (Sum(Fields!Quantity.Value, "serv1") + Sum(Fields!Quantity.Value, "Accrued"))
) = "" ,false,0 ) but I am getting a little confused.

Upvotes: 0

Views: 216

Answers (1)

suff trek
suff trek

Reputation: 39777

Most likely value is not blank string but a Nothing. Try following construct:

=IIf(IsNothing(((Sum(Fields!TotalCost.Value, "Accrued") + Sum(Fields!TotalCost.Value, "serv1")) / (Sum(Fields!Quantity.Value, "serv1") + Sum(Fields!Quantity.Value, "Accrued"))), 0, ((Sum(Fields!TotalCost.Value, "Accrued") + Sum(Fields!TotalCost.Value, "serv1")) / (Sum(Fields!Quantity.Value, "serv1") + Sum(Fields!Quantity.Value, "Accrued")))

It's a bit awkward since you have to repeat the expression twice, to avoid it you may want to write a custom function.

Upvotes: 2

Related Questions