none
IIF statement query RRS feed

  • Question

  • Hello

    I have an output in my queries that gives me:

    xxxxxxx xxxxxxxxx : 123456 (xx) - xxxxxxx...

    or

    xxxxxxx xxxxxxxxx : 12345678 (xx) - xxxxxxx...

    basically text before either a 6 or 8 digit number then text after

    I've created a column in a table in SSRS that just shows the number, and I used this expression:

    =Mid(Fields!my.Value,InStr(Fields!my.Value,":")+1,Instr(InStr(Fields!my.Value,":")+3,Fields!my.Value," ") - InStr(Fields!my.Value,":") -1)

    Which now gives me an output of 123456 or 12345678.

    However I'd like to do an additional thing. I'd like to create an output that if the digit is 6 numbers long returns London and if the digit is 8 numbers long then it returns Paris .  Is this possible - I've tried with IIF but have singularly failed so far.

    Help much appreciated - thanks.


    Tuesday, August 20, 2019 7:38 AM

Answers

  • Hi William,

    Try

    =IIF(Len(Mid(Fields!my.Value,InStr(Fields!my.Value,":")+1,Instr(InStr(Fields!my.Value,":")+3,Fields!my.Value," ") - InStr(Fields!my.Value,":") -1))=8,"London",IIF(Len(Mid(Fields!my.Value,InStr(Fields!my.Value,":")+1,Instr(InStr(Fields!my.Value,":")+3,Fields!my.Value," ") - InStr(Fields!my.Value,":") -1))=6,"Paris","Wrong Number"))


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 20, 2019 8:14 AM

All replies

  • Hi William,

    Try

    =IIF(Len(Mid(Fields!my.Value,InStr(Fields!my.Value,":")+1,Instr(InStr(Fields!my.Value,":")+3,Fields!my.Value," ") - InStr(Fields!my.Value,":") -1))=8,"London",IIF(Len(Mid(Fields!my.Value,InStr(Fields!my.Value,":")+1,Instr(InStr(Fields!my.Value,":")+3,Fields!my.Value," ") - InStr(Fields!my.Value,":") -1))=6,"Paris","Wrong Number"))


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 20, 2019 8:14 AM
  • Thanks very much for getting back Lukas. That nearly works but gives a return of 'wrong number' for everything! Do you have any idea of a tweak to get it there? Thanks for your help with this.
    Tuesday, August 20, 2019 8:38 AM
  • Hi William,

    So we could know for now the expression is working since returns "wrong number" as last argument from it.

    You could add expression to a textbox:

    =Len(Mid(Fields!my.Value,InStr(Fields!my.Value,":")+1,Instr(InStr(Fields!my.Value,":")+3,Fields!my.Value," ") - InStr(Fields!my.Value,":") -1))

    Use this expression to check the real digit of the string.  Maybe it not 6 or 8. Change the number in my above post expression to the real digit.

    Also we could check your expression, since I couldn't test it in my environment, you could test it yourself to check if desired result returns.

    Regards,
    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 21, 2019 1:55 AM
  • That is really, really helpful - thank you very much Lukas for your time and patience. Query two sorted a lot of things out and then I was easily able to adapt query 1 above. All working perfectly.
    Wednesday, August 21, 2019 7:24 AM
  • So glad this helps.

    Cheers! :)


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 21, 2019 8:07 AM