locked
IIF expression RRS feed

  • Question

  • Hi

    I am new to SSRS and stuck with this problem

    I want to merge a few IIF expression into one:

    (1) =IIF(Fields!abc.Value = "", "Missing abc","")
    (2) =IIF(Fields!def.value = "", "Missing def","")
    (3) =IIF(Fields!ghi.value = "", "Missing ghi","")

    and so on....i have 9 such arguements

    basically i want to disply something like this...display only missing info for each id. If one id is missing more than one field value that id should be repeated with another missing info (e.g. ID 2 is missing two field values so it is repeated twice.)

    Hope it explains what i am trying to say...

    Please help me out....

    Thanks

    <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} -->

    ID

    Desc

    1

    Missing abc

    2

    Missing abc

    2

    Missinf def

    3

    Missing ghi

    4

    Missing abc

    4

    Missing ghi

    Thursday, April 15, 2010 4:27 PM

Answers

  • It looks like this would be best handled with a Switch statement and not Iif:

    =SWITCH(Fields!abc.Value = "", "Missing abc",Fields!def.value = "", "Missing def",Fields!ghi.value = "", "Missing ghi")

    Either this, or you could nest your IIF statements, which would be the best solution if you need the final ELSE:

    =IIF(Fields!abc.Value = "", "Missing abc",IIF(Fields!def.value = "", "Missing def",IIF(Fields!ghi.value = "", "Missing ghi","")))

    Aaron Jarboe

    -Remember to mark as an answer if this post has helped you.
    • Marked as answer by Mike_Do Thursday, April 15, 2010 5:42 PM
    Thursday, April 15, 2010 4:50 PM
  • I assume that your data is something like this:

    id ABC DEF GHI
    1 'a' '' ''
    2 '' 'a' ''

    Of course this is a sample structure based on which I build the expression:

    =IIF(Fields!ABC.Value="","Missing ABC;","") & IIF(Fields!DEF.Value="","Missing DEF;","") & IIF(Fields!GHI.Value="","Missing GHI;","")

    This will do the job for you (unless the structure of the data is completely different) but I think you've got the point :)

    Cheers,

    Ivan

    • Marked as answer by Mike_Do Thursday, April 15, 2010 5:42 PM
    Thursday, April 15, 2010 5:37 PM

All replies

  • It looks like this would be best handled with a Switch statement and not Iif:

    =SWITCH(Fields!abc.Value = "", "Missing abc",Fields!def.value = "", "Missing def",Fields!ghi.value = "", "Missing ghi")

    Either this, or you could nest your IIF statements, which would be the best solution if you need the final ELSE:

    =IIF(Fields!abc.Value = "", "Missing abc",IIF(Fields!def.value = "", "Missing def",IIF(Fields!ghi.value = "", "Missing ghi","")))

    Aaron Jarboe

    -Remember to mark as an answer if this post has helped you.
    • Marked as answer by Mike_Do Thursday, April 15, 2010 5:42 PM
    Thursday, April 15, 2010 4:50 PM
  • Thanks a lot Aaron for replying back ......

    I have tried both option but this only displays "Missing abc" in the report although there's
    a missing def value for the same id.

    e.g.     Desired Result:

        ID    Desc
        1    Missing abc
        2    Missing abc
        2    Missinf def
        3    Missing ghi
        4    Missing abc
        4    Missing ghi


        Current result:
       
        id        description
        1        Missing ABC
        2        Missing ABC
        3        Missing ABC

    arguments for field abc and def are independent of each other....meaning field abc and def has nothing to do with each other.

    Any idea what's wrong?

     

    Thanks again

    • Proposed as answer by Jeevan Dasari Tuesday, April 20, 2010 6:12 AM
    Thursday, April 15, 2010 5:02 PM
  • Hi Mike and Aaron,

    Aaron's logic is working perfectly but I guess it needs some additions as if ABC is missing then the IIF is always true at the first evaluation and you got only "Missing ABC". I think you have two possibilities. You can display all missing values in a single field if you concatenate the results of the nine IIFs. Then the result will be something like this:

    id description
    1 Missing ABC
    2 Missing ABC,Missing DEF
    ........

    But this does not comply with your original request. In order to achieve this result you have to manipulate the SQL statement in the source dataset by including an inline UDF with APPLY operator.

    For using the APPLY operator, please refer to http://www.sqlservercentral.com/articles/APPLY/69953/

    Ivan

    Thursday, April 15, 2010 5:17 PM
  • Thanks Ivan for your suggestion...actually the source database is Informix and am not sure how to do UDF and APPLY operator..anyway you said i can display all missing values in a single field....could you please explain me in more detail how do i do that??

     

    Thanks a lot

    Thursday, April 15, 2010 5:23 PM
  • I assume that your data is something like this:

    id ABC DEF GHI
    1 'a' '' ''
    2 '' 'a' ''

    Of course this is a sample structure based on which I build the expression:

    =IIF(Fields!ABC.Value="","Missing ABC;","") & IIF(Fields!DEF.Value="","Missing DEF;","") & IIF(Fields!GHI.Value="","Missing GHI;","")

    This will do the job for you (unless the structure of the data is completely different) but I think you've got the point :)

    Cheers,

    Ivan

    • Marked as answer by Mike_Do Thursday, April 15, 2010 5:42 PM
    Thursday, April 15, 2010 5:37 PM
  • Thanks a lot Ivan......
    Thursday, April 15, 2010 5:43 PM