locked
Nested IIF Statement in SSRS 2012 RRS feed

  • Question

  • I am trying to show employee names in place of their id in an SSRS report. I wrote an expression in the field: =IIF(Fields!INSPBY.Value Is Nothing, " ", IIF(Fields!INSPBY.Value= 4242, "Curt Edelmann", IIF(Fields!INSPBY.Value = 0733, "Jonathan Dye", "Not Assigned"))) but I'm getting #Error in places where the field is null in the DB. I am not quite sure how to fix it.
    Monday, January 26, 2015 7:50 PM

All replies

  • Nested IIf statements can be difficult to debug and for that reason you should consider using Switch. It does pretty much the same thing but is easier to read. Your statement would look like the below as a switch:

    =Switch(
         Fields!INSPBY.Value Is Nothing, " ",
         Fields!INSPBY.Value= 4242, "Curt Edelmann",
         Fields!INSPBY.Value = 0733, "Jonathan Dye",
         1=1, "Not Assigned"
    )

    You can find information about the Program Flow statements in the TechNet Wiki: http://social.technet.microsoft.com/wiki/contents/articles/24959.ssrs-expressions-part-1-program-flow.aspx

    If you are wondering about the 1=1, "Not Assigned", it is a way of doing a catchall that will be returned when everything before is false, like your final false condition.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Monday, January 26, 2015 8:15 PM
  • Hi raymond71,

    According to your description, when you specify the expression in the report, the #error return when the corresponding value is null.

    As we tested in our environment, if the value is NULL, we can return blank value when using the expression like yours. In your scenario, since you mention the #error return when the corresponding value is NULL, please run the report in the BIDS then check the warning message in output to see the detail information about the #Error. Regarding our test results, please refer to the screenshots below:

    If you have any question, please feel free to ask.

    Best regards,
    Qiuyun Yu



    Qiuyun Yu
    TechNet Community Support



    Tuesday, January 27, 2015 12:11 PM
  • This formula seems to be working. I did find, however, that the INSPBY fields can be blank in the DB instead of null and as a result, I am still getting the #Error in some of my fields. Any ideas?

    Monday, February 2, 2015 3:45 PM
  • Since I am rather new to SSRS, where would I find/run the BIDS? I found the INSPBY field in the DB is blank instead of null.
    Monday, February 2, 2015 3:48 PM
  • Hi raymond71,

    As we tested in our environment, the data type of the Week field (in your scenario is INSPBY field) is string, if the there is a blank value exists in the database, we should change the expression like below:

    =IIF (Fields!Week.Value=" ", " ", IIF(Fields!Week.Value= "4242", "Curt Edelmann", IIF(Fields!Week.Value ="0733", "Jonathan Dye", "Not Assigned")))

    In your scenario, if you are using SSRS 2012, you should use SQL Server Data Tool instead of Business Intelligence Development Studio. At the beginning of the SSRS 2012, Business Intelligence Development Studio is replaced by SQL Server Data Tool. If you have installed this feature, you can find it by clicking Start\ Microsoft SQL Server 2012. Then run the report in SQL Server Data Tool to check the warning message.

    If issue persists, please provide sample data for our analysis. Regarding our test data and results , please refer to the screenshots below:

    If you have any question, please feel free to ask.

    Best regards,
    Qiuyun Yu


    Qiuyun Yu
    TechNet Community Support


    Tuesday, February 3, 2015 5:34 AM
  • Sorry I cant understand why you need to use nested IIF for showing EMplyeenames. Ideally your dataset will have a column for employee names which you will bring along with the ids provided you want them to be shown in the reports. Then its just a matter of using thefield from dataset directly in the report.

    Or do you mean employee names come from a different db table?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, February 3, 2015 8:58 AM
  • The employee name resides in another table. The table I am using has the employee id in it and I am trying to show the Employee name instead of the id for ease of identifying the inspector.
    Tuesday, February 3, 2015 3:23 PM
  • Lookup is used to relate data in your current dataset (personid) to data in another dataset (personname). But is better to simply write your query to join the two table and include the name with the id.

    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Monday, February 9, 2015 10:48 PM