index was outside bounds of array in SSRS when parameter come as blank

Question

I am working on SSRS report where user!userid variable coming with 3 pipe delimited values

E.g :

ReportUser |500|100

I split the values using below expression

=(Split(Parameters!QueryString.Value, "|")).GetValue(0)
=(Split(Parameters!QueryString.Value, "|")).GetValue(1)
=(Split(Parameters!QueryString.Value, "|")).GetValue(2)

When the parameter is coming with values above expression works fine. But, when any parameter coming as blank, I am getting below below error during report execution.

index was outside bounds of array for the parameter.

I tried below workarounds with iif expression

=iif((Split(Parameters!QueryString.Value, "|")).GetValue(0)=NOTHING,0,
      (Split(Parameters!QueryString.Value, "|")).GetValue(0))

=iif((Split(Parameters!QueryString.Value, "|")).GetValue(0)="",0,
      (Split(Parameters!QueryString.Value, "|")).GetValue(0))

Still I am getting the same error. Could someone help how to handle the blank values with this expression?

Alan Schofield · Accepted Answer · 2017-10-21T15:55:07.703

I had initially though that this would work

This code fails

=SWITCH
(
Split(Parameters!QueryString.Value, "|").Length <3, "",
True, Split(Parameters!QueryString.Value, "|").GetValue(1)
)

As Switch stops evaluating at the first True result but for some reason I still got an Error. I'm not sure why this happens but I got round it by writing a function to do the work.

This does work

I added this code to the Reports Code property (it can probably be made better but I've not done any VB.Net for years)

Public Function GetSplitValue(inputString as String, delim as string, index as Integer) as String

    Dim arr() AS String = Split(inputString, delim)
    Dim result AS string

    TRY
        result = arr(index)
    CATCH
        result = ""
    END TRY

    RETURN result

End Function

You can then call this in your expression using something like this in your text boxes or whatever you are populating.

=Code.GetSplitValue(Parameters!QueryString.Value,"|",0)
=Code.GetSplitValue(Parameters!QueryString.Value,"|",1)
=Code.GetSplitValue(Parameters!QueryString.Value,"|",2)

If you want something other than an empty string, just edit the code in the CATCH block.

index was outside bounds of array in SSRS when parameter come as blank

1 Answers1