SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


IIF then Else in SSRS


IIF then Else in SSRS

Author
Message
keywestfl9
keywestfl9
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 764
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
Arunkumar S P
Arunkumar S P
SSChasing Mays
SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)

Group: General Forum Members
Points: 629 Visits: 870
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


keywestfl9
keywestfl9
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 764
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?
bikerdadHLV
bikerdadHLV
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 182
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)
)



Cool
keywestfl9
keywestfl9
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 764
Thanks
But ToNumber is not present in SSRS and i get the same error #error
bikerdadHLV
bikerdadHLV
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 182
I'm not familiar with ToNumber, but it sounds like it may be the same as FormatNumber. Take a look at that.
Arunkumar S P
Arunkumar S P
SSChasing Mays
SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)

Group: General Forum Members
Points: 629 Visits: 870
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))



[color=RED]Hope this above code will give you 100 % output.[/color]

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


adorable_412in
adorable_412in
Mr or Mrs. 500
Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)

Group: General Forum Members
Points: 537 Visits: 82
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
toolman-352714
toolman-352714
SSC Veteran
SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)

Group: General Forum Members
Points: 259 Visits: 712
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

toolman
Numbers 6:24-26
adorable_412in
adorable_412in
Mr or Mrs. 500
Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)

Group: General Forum Members
Points: 537 Visits: 82
Thanks for your reply.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search