IIF then Else in SSRS
-
if isnull(field1)
or isnull(field2) then
0
else
ToNumber((field1 / field2) * 100)
==========
how do i rewrite it in the Field Expression in SSRS? Please assist me
-
Hope this will help you
=IIf(IsNothing(Fields!field1.Value)Or IsNothing(Fields!field2.Value) ,0,((Fields!field1.Value)/(Fields!field2.Value)*100))If you are not clear, revert back to me
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Please feel free to let me know if you are not clear or I’ve misunderstood anything.Thanks,
Arunkumar S P
-
Thanks Arunkumar
i tried it but in case of field2 being null 0 is substitued and when field1 is divided by 0 (field2) it gives # error.
I believe Tonumber in crystal version takes care of it and it displays as 0%
can you tell me how to fix this in SSRS?
-
keywestfl9 (4/1/2009)
if isnull(field1)or isnull(field2) then
0
else
ToNumber((field1 / field2) * 100)
==========
how do i rewrite it in the Field Expression in SSRS? Please assist me
IIF constructions in SSRS always evaluate the entire expression, thereby resulting in the error you see.
You can use a SWITCH instead, it'll work like a charm. Just use "1=1" as your "ELSE" condition.
=SWITCH(IsNothing(Fields!Field1.Value), 0
IsNothing(Fields!Field2.Value), 0
1=1,ToNumber((field1 / field2) * 100)
)
😎
-
Thanks
But ToNumber is not present in SSRS and i get the same error #error
-
I'm not familiar with ToNumber, but it sounds like it may be the same as FormatNumber. Take a look at that.
-
I think you are using code wrongly.
According to my code if any one value ( field1 or field2 ) is null , it won't divide it will return the value 0. Only if both has value, it will divide. Check my code once again.
=IIf(IsNothing(Fields!field1.Value)Or IsNothing(Fields!field2.Value) ,0,((Fields!field1.Value)/(Fields!field2.Value)*100))
But this code will give error if field2 is defaultly zero ( 0) and not null. To avoid this you can add this condition also in the above. And the code should look like below
IIf(IsNothing(Fields!field1.Value)Or IsNothing(Fields!field2.Value) Or Fields!field2.Value=0 ,0,((Fields!field1.Value)/(Fields!field2.Value)*100))Hope this above code will give you 100 % output.
I you get error again use custom code like below
Public Function DivisionCheck(ByVal Numerator As Object, ByVal Denominator
As object, ByVal DivZeroDefault As Object) As Object
If Denominator <> 0 Then
Return Numerator/Denominator
Else
Return DivZeroDefault
End If
End Function
Still if you find difficulties or error kindly revert back to me
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Please feel free to let me know if you are not clear or I’ve misunderstood anything.Thanks,
Arunkumar S P -
Can you please help me in writting the following Field Expression in SSRS
IF {INVOICE_MAIN.BILL_ADDR2} = "" then
{INVOICE_MAIN.BILL_ADDR1} + chr(10)+chr(13) +{INVOICE_MAIN.BILL_CITY} + ", " + {INVOICE_MAIN.BILL_STATE} + " " + {INVOICE_MAIN.BILL_ZIP}
Else
{INVOICE_MAIN.BILL_ADDR1} + chr(10)+chr(13) + {INVOICE_MAIN.BILL_ADDR2} + chr(10)+chr(13) + {INVOICE_MAIN.BILL_CITY} + ", " + {INVOICE_MAIN.BILL_STATE} + " " + {INVOICE_MAIN.BILL_ZIP}
The above expression is used in Crystal reports
Thank You
Shilpa
-
Substitute IIF( for IF, a comma for THEN and another comma for ELSE and place a close paren ')' at the end.
IIF syntax is IIF( check expression, true expression, false expression)
You will also want to strip out the { and } around the field names and use SSRS conventions for the fields Fields!fieldname.Value
HTH
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font] -
Thanks for your reply.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply