Search code examples
vbareporting-servicesssrs-2008iif

embedded IIF States in SSRS

Thank you for taking your time to help me today. I am trying to use multiple if statements to control what value is displayed depending on whether each statement is true. So right now I have this below which is essentially:

IIF(expression = NULL
, CompanyAddress
, IIF(Expression='TX'
    , IIF(BOOL=TRUE
        ,CompanyAddress
        , SWITCH(DEALER ADDRESSES))
    ,CompanyAddress)
)

I have tested each individual IIF statements separately and I get the outcomes which I expect. Currently in the first IIF statement and the Expression = NULL is TRUE , It just outputs #Error and not the "Nothin" OR in my real case Company Address. But if Expression = NULL is FAlSE, I do get the correct output of either the companyAddress or the Dealer. 

=IIF(IsNothing(Fields!CoOppId.Value)
,("nothin")
, (IIF(Fields!Addr1.Value.Contains("TX")
    , IIF(Fields!UDFCustProv.Value = 1
        , Fields!Addr0.Value
        , Switch(
            Fields!UDFMake.Value.Contains("Chevy")
            , "Knapp Chevrolet" + chr(10) + "PO box " + chr(10) + "Houston TX 77210"
            , Fields!UDFMake.Value.contains("Ford") 
            , "Sterling McCall Ford" + chr(10) + "6445 Southwest Freeway" + chr(10) + "Houston TX 77074"
            , Fields!UDFMake.Value.contains("International")
            , "Pliler International" + chr(10) + "2016 S. Eastman Rd" + chr(10) + "Longview TX 75602"
            , Fields!UDFMake.Value.contains("Freightliner")
            , "Houston Freightliner, Inc" + chr(10) +"9550 North Loop East" + chr(10) + "Houston TX 77029"
            , Fields!UDFMake.Value.contains("RAM")
            , "Max Haik Dodge Chrysler Jeep" +chr(10)+ "11000 I-45 North Freeway" + chr(10) + "Houston TX 77037")),Fields!Addr0.Value)))
Solution

  • I agree with @Daniel, the error is most likely being produce by the Fields!UDFMake.Value.Contains when the value is null, as IIF does not short-circuit. Alternatively to the good options that @Daniel mentioned you can replace the contains method by the function InStr as:

    ... , Switch(
            InStr(Fields!UDFMake.Value,"Chevy") > 0
            , "Knapp Chevrolet" + chr(10) + "PO box " + chr(10) + "Houston TX 77210" ...

    this will not produce an error even when the value of the field is Null.