CODEWITHSUNDEEP
functionreporting-servicessplit
oOo Testing1 oOo
oOo Testing1 oOo

Reputation: 197

Need Help on SSRS SPLIT function

Hi All I need your advice,

1) I have a dynamic string which changes each time.

for example today the string will be "ItemA,ItemB,ItemC" and tomorrow it will be "itemA,ItemB" only.

2) I have an SSRS report which has 3 columns

3) I want to split this string "ItemA,ItemB,ItemC" and want to show split substrings in these 3 columns of ssrs table. the Delimiter is "," (comma)

4) I had used 

=Split("ItemA,ItemB,ItemC",",").GetValue(0) in the first column of the report 

=Split("ItemA,ItemB,ItemC",",").GetValue(1) in the second column of the report

=Split("ItemA,ItemB,ItemC",",").GetValue(2) in the third column of the report

5) everything works fine until my string is "ItemA,ItemB,ItemC" ,

BUT WHEN MY STRING CHANGES TO "ItemA,ItemB" I am seeing "#Error" in the third column.

I understood the error, for

=Split("ItemA,ItemB,ItemC",",").GetValue(2) we don't have any value because the string now has only 2 values after applying split function.

Is there any way i can avoid #Error in the third column.

NOTE: I have to compulsorily use 3 columns in the ssrs report.

I had tried using =Replace(Split("ItemA,ItemB",",").GetValue(2),"#Error","NULL") in the third column but that also wont worked.

Upvotes: 1

Views: 18787

Answers (3)

RaduSun
RaduSun

Reputation: 1

You can append a dummy element and then split:

yourText = "ItemA,ItemB"

item0 = Split(yourText & ",", ",").GetValue(0)
item1 = Split(yourText & ",", ",").GetValue(1)
item2 = Split(yourText & ",", ",").GetValue(2)

item0 = ItemA
item1 = ItemB
item2 =

Upvotes: 0

FistOfFury
FistOfFury

Reputation: 7155

you could change the visibility of the 3rd column to hidden if the name returns an error

Upvotes: 0

Chris Latta
Chris Latta

Reputation: 20560

This is similar to the divide by zero issue in using IIF statements. The problem is that IIF is not an expression, it is a function with three parameters:

IIF(Condition, ValueIfTrue, ValueIfFalse)

Accordingly, ALL parameters are evaluated BEFORE being passed to the function, which results in the error because the erroneous expression is still evaluated even when the condition should mean that it isn't.

I can't see a way to use the usual workaround tha solves the problem in the divide by zero case. What we need is a real language that doesn't have this problem. Fortunately, this is availble in the form of custom code.

Do the following:

  • Right-click on an area of the report surface that has no report objects
  • Select Report Properties
  • Click on the Code tab

  • Insert the following code:

    Public Function ExtractCode(Combined As String, Position As Integer) As String
    if (Split(Combined, ",").Length >= Position) Then
        Return Split(Combined, ",").GetValue(Position-1)
    Else
        Return ""
    End If
End Function

  • Click OK and go back to the report

  • Right-click on the cell you want the expression in and click Expression

  • Insert the following expression:

    =Code.ExtractCode(Fields!Combo.Value, 3)

The value 3 is the "column" that you want to extract from the Combo field, so to get the second "column" you would use 2. If there isn't a column at that position, an empty string is returned.

Upvotes: 3

Related Questions