Post reply

Receiving an error if field is Null when using a Trim/Split Expression

  • April 1, 2022 at 3:53 pm

    #4009398

    The Field PC_List contains, for example, "Ward:Blackrock"

    I wish to remove the word Ward and the colon from the string

    =Split(Fields!PC_List.Value,":")(1)

    The above command works, unless there is a null value in the field which returns an error.

    I have created a Calculated Field to display called Ward

    I have tried:

    =IIF(Fields!PC_List.Value=Nothing,"No Value",Split(Fields!PC_List.Value,":")(1)) but the error is still displayed.

    I then tried to do it where the Length of the field was = 0 but still gets the error.

    Is there any way in which this can be achieved as I am tearing my hair out!!

    TIA

    • This topic was modified 2 years, 7 months ago by  shelts.

  • April 2, 2022 at 4:10 pm

    #4009794

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • April 2, 2022 at 10:19 pm

    #4009874

    I don't know much about the language used in SSRS but why wouldn't you just find the position of the colon, add 1 to that, and use that as the starting position of a substring (or whatever it's called in SSRS).  That' bound to be faster than any splitting method.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 3, 2022 at 12:57 pm

    #4010049

    Cannot check this as I do not hve access to any SQL Server yet but IIRC IFF in SSRS is not short circuit and both results are evaluated and therefore you will always get the error. Also I think you should use IsNothing for the test and use it in both results.

    IIF(IsNothing(Fields!PC_List.Value),"No Value",Split(IIF(IsNothing(Fields!PC_List.Value),”:”, Fields!PC_List.Value),":")(1))

    Alternatively you could write .net code function to do it

    Far away is close at hand in the images of elsewhere.
    Anon.

  • April 5, 2022 at 4:58 pm

    #4011147

    Thanks David, it's not working still so might need to revert to Code to get it to work

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply