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

  • 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 months, 3 weeks ago by  shelts.
  • Thanks for posting your issue and hopefully someone will answer soon.

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

  • 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)
    Intro to Tally Tables and Functions

  • 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.

  • 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