Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

IIF then Else in SSRS Expand / Collapse
Author
Message
Posted Wednesday, April 1, 2009 4:45 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 1, 2012 8:16 PM
Points: 237, 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
Post #688467
Posted Thursday, April 2, 2009 1:06 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 10:59 PM
Points: 229, 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

Post #688569
Posted Thursday, April 2, 2009 10:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 1, 2012 8:16 PM
Points: 237, 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?
Post #689166
Posted Thursday, April 2, 2009 11:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 1:09 PM
Points: 22, Visits: 136
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)
)

Post #689193
Posted Thursday, April 2, 2009 1:10 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 1, 2012 8:16 PM
Points: 237, Visits: 764
Thanks
But ToNumber is not present in SSRS and i get the same error #error
Post #689269
Posted Thursday, April 2, 2009 1:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 1:09 PM
Points: 22, Visits: 136
I'm not familiar with ToNumber, but it sounds like it may be the same as FormatNumber. Take a look at that.
Post #689297
Posted Friday, April 3, 2009 1:01 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 10:59 PM
Points: 229, 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

Post #689614
Posted Tuesday, April 13, 2010 6:01 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 6:29 PM
Points: 513, 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
Post #902820
Posted Wednesday, April 14, 2010 6:38 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 15, 2013 1:11 PM
Points: 138, Visits: 698
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
Post #903068
Posted Wednesday, April 14, 2010 10:29 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 6:29 PM
Points: 513, Visits: 82
Thanks for your reply.
Post #903336
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse