Using Split in SSRS

Epicor ERP 10
21

Oh, if it’s on the detail grouping then remove the “First( )” function:

=Code.GetItem(Fields!Description.Value,< zero-based item # >)

Also, maybe it doesn’t like the ByVal in the function definition.

22

I tried both and neither made a difference.

=Code.GetItem(Fields!Description.Value, “JobHead”,24)

=Code.GetItem(Fields!Description.Value,24)

Error:

The Value expression for the text box ‘Textbox26’ refers directly to the field ‘Description’ without specifying a dataset aggregate. When the report contains multiple datasets, field references outside of a data region must be contained within aggregate functions which specify a dataset scope.

23

It’s odd you don’t get that error message on Textbox26 with your previous expression with the

First(Fields!Description.Value, "JobHead"

:thinking:

Did you type the expression in or copy/paste? If the latter then make sure your double quotes are straight and not fancy.

24

Yes Calvin, I have some issues to fix after I solve this.

25

I think I copied and Pasted. Then replaced with the number.

26

I’m looking at your example string, now I’m wondering how they were origianlly generated?

and just wanted to make sure a string should look like this if opened in NotePad++ ?
image

and description(s) in part entry looks like this?
image

27

chr(13) instead of char(13)?

2 Likes
28

Correct. Too many languages in my head… Fixed.

29

=iif(IsNothing(Fields!Description.Value),"",Replace(Fields!Description.Value,VbCr,VbCrLf))

And the formula above seems to work for a quick test… assuming my string actually matches what is going on?
image

30

Bruce,

Can you make it three columns? I don’t know how to do multiple columns in SSRS from an array. Unfortunately, SSRS only recognizes a subset of HTML and the table element is not one of them.

31

That’s one reason I used individual fields so I can make columns and place as needed.

1 Like
32

I have used a mono-spaced font (like Courier) and some spacing logic to get the same effect but mixing fonts does look like someone who just got a new Macintosh in 1994.

33

I have something that seems to work.

Public Function ConvertToTable(ByVal s as String,itm as integer) as String
Try
Dim sa() as string
sa = split(s, chr(13))
Return sa(itm)
Catch
End Try
End Function

Expression:
=Code.ConvertToTable(First(Fields!Description.Value, “JobHead”),24)

With each field with the itm number I want to show.

34

Ahhh… now I understand… ConvertToTable…

35

Here’s one way make multiple coulmns in SSRS…

36

I forgot to add without a tablix but this is the way to do columns. It is clever and versatile.

37

If the original goal is just to make a long narrow string appear as multiple columns - and that you don’t need the individual elements - just break it into 3rds.

image
image773×521 12.9 KB

The first row of this is the original data (one long string with CR’s embedded in it)
The second row is a rectangle with three text boxes in it (TextboxCol1, TextboxCol2, and TextboxCol3).

The expression for each textbox is:

TextboxCol1

=Left(Variables!st.Value, InStr(Variables!st.Value,Variables!st.Value.Split(vbCrLf)(Ceiling(Variables!st.Value.Split(vbCrLf).Length/3))))

TextboxCol2

=Mid(Variables!st.Value, InStr(Variables!st.Value,Variables!st.Value.Split(vbCrLf)(Ceiling(Variables!st.Value.Split(vbCrLf).Length/3)))+1, InStr(Variables!st.Value,Variables!st.Value.Split(vbCrLf)(Ceiling(Variables!st.Value.Split(vbCrLf).Length/3*2))) -InStr(Variables!st.Value,Variables!st.Value.Split(vbCrLf)(Ceiling(Variables!st.Value.Split(vbCrLf).Length/3)))
    )

TextboxCol3

=Mid(Variables!st.Value, InStr(Variables!st.Value,Variables!st.Value.Split(vbCrLf)(Ceiling(Variables!st.Value.Split(vbCrLf).Length/3*2)))+1,

    Len(Variables!st.Value)
    )

One short fall of this, it hiccups on duplicated rows.

Probably cleaner to make functions in the Report Code and call them.

2 Likes
38

And MUCH cleaner as a a Report function. You’d use the same call in each columns expression. Just modify the colNum parameter.
=Code.ColText(Variables!st.Value, vbCrLf, 1, 3)

' s : string to splt, 
' delim: delimiting string
' colNum: the column to return (1 is first col, 2, is 2nd, etc)
' colCount: the number of columns to split it into 
' returns a string of text for the specified colNum
Public Function ColText(s As String, delim As String, colNum As Integer, colCount As Integer) As String
  Dim retVal As String = ""
  Dim rowCount As Integer, i As Integer

  If (s.Split(delim).Length = 0) OR colCount = 0 OR colNum > ColCount Then Return s

  rowCount = s.Split(delim).Length / colCount

  For i = 1 to rowCount 
    if(s.Split(delim).Length) > (i-1 + ((colNum-1) * rowCount)) Then
      retVal = retVal + s.Split(delim)(i-1 + ((colNum-1) * rowCount))
    Else
      Exit For
    End If
  Next i

  Return retVal
End Function
1 Like