CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-2008ssrs-2008-r2
David
David

Reputation: 113

Getting "#ERROR" in certain fields when using Split function in SSRS

I am using a split function to separate a column with two street addresses.

The information is separated by ,.

Some of the rows only have one address associated with them.

In those rows for my Street Address 2, I'm getting #ERROR when I want it to be null.

I've tried an IIF() statement for the expression, but I am having trouble with it.

Split(Fields!Street.Value, ",").GetValue(2)

Upvotes: 0

Views: 1398

Answers (2)

Frank Ball
Frank Ball

Reputation: 1126

(Use a custom function for each Address.
Adapted from: Split String

     Public Function GetAddress1(ByVal a as String)
            Dim b() as string
            b=Split(a,",")
            Dim str_1(b.Length) As String
            Dim i As Integer
            For i = 0 To b.Length - 1
                str_1(i) = b(i).Split(",")(0)
            Next
       return str_1
    End Function

    Public Function GetAddress2 (ByVal a as String)
         Dim b() as string
         b=Split(a,",")
         Dim str_1(b.Length) As String
         Dim i As Integer
         For i = 0 To b.Length - 1
              str_1(i) = b(i).Split(",")(1)
         Next
        return str_1
End Function

Upvotes: 1

Jesse Potter
Jesse Potter

Reputation: 847

Unlike the If statement, IIf statements evaluate all code paths even though only one code path is used. This means that an error in an unused code-path will bubble up to an error in the IIf statement, preventing it from executing correctly.

To fix this, you need to use functions that won't throw an error when there is nothing to split.

Here is an example of code that should do what you want:

=IIf(
InStr(
    InStr(
        Parameters!Street.Value
        , ","
        ) + 1
    , 
    Parameters!Street.Value
    , ","
    ) = 0
, Nothing
, Right(
    Parameters!Street.Value
    , Parameters!Street.Value.ToString().Length - (
        InStr(
            InStr(
                Parameters!Street.Value
                , ","
                ) + 1
            , 
            Parameters!Street.Value
            , ","
            )
        )
    )
)

Let's break this down.

I've used a combination of InStr(), Right(), Length(), and IIf() functions to split the string without throwing an error.

InStr() is used to find the position of the string "," within the address. This returns 0 rather than an error if it can't find the string. 

InStr(Parameters!Street.Value, ",")

Since you appear to be looking for the second comma in your split function you will need to nest the InStr function. Use the location of the first comma as the start location to search for the second comma. Don't forget to +1 or you will find the first comma again.

InStr(InStr(Parameters!Street.Value, ",") + 1, Parameters!Street.Value, ",")

Now you can find the second comma without throwing an error even if no commas exist.

Based on the location of the second comma use the Right() function to grab all characters to the right of the second comma. Since Right() needs to know how many characters from the end rather than from the beginning, you will need to subtract the location of the comma from the Length() of the string. This effectively splits the string at the comma. 

Right(
    Parameters!Street.Value
    , Parameters!Street.Value.ToString().Length - (
        InStr(InStr(Parameters!Street.Value, ",") + 1, Parameters!Street.Value, ","))
    )
)

If you have more than 2 commas you can grab just the string between the 2nd and 3rd comma by following up with a Left() function that finds the location of the 3rd comma.

Now you can use your IIf() function to return NULL (Nothing) if there is not a 2nd comma. The function at the top shows how this all fits together.

This could be cleaned up by using functions, but the provided code shows you how it can be done.

Upvotes: 0

Related Questions