The SSRS Split function is a Text function that will split the given string into the number of substrings using the specified delimiter. It may be space, comma, etc., and returns a zero-based one-dimensional array. So, to access the splitter substring, you must use the index, which starts from 0 to n-1. The syntax of the SSRS Split function to perform the string split using the delimiter is as shown below.
=Split(Fields!ColumnWithCommas.Value, ",")
To demonstrate the SSRS Split function, we use the Employee table below, which has 15 records. The image shows the records in the table report in a single column. Our job is to split them into multiple columns.
SSRS Split function Example
For this, let me add a new column to the right side of the FullName column. Next, right-click the textbox under the ID Name and choose the Expression. To understand the report, I suggest you refer to the articles on charts, tables, grouping, and format tables in SSRS.
It opens the following expression window to perform string split. The below SSRS Split function will split the FullName field using the pipe delimiter and store it in a one-dimensional array. Here, (0) will access the first value from the array, i.e., Employee. ID.
=Split(Fields!FullName.Value, "|")(0)
Similarly, let me create seven more new columns to the ID’s right. We changed the array index value to add the particular column data.
=Split(Fields!FullName.Value, "|")(1)
=Split(Fields!FullName.Value, "|")(2)
=Split(Fields!FullName.Value, "|")(3)
=Split(Fields!FullName.Value, "|")(4)
=Split(Fields!FullName.Value, "|")(5)
=Split(Fields!FullName.Value, "|")(6)
=Split(Fields!FullName.Value, "|")(7)
The Sales column (index 6) contains many decimal values, so if we replace the Split( Fields!FullName.Value, “|”)(6) code with the FormatNumber function, it shows only two decimal precision values.
=FormatNumber( Split(Fields!FullName.Value, "|")(6), 2)
The date and time are also very long for the HireDate (index 7) column. So, let me use the FormatDateTime function to show the general date. To do so, replace the Split(Fields!FullName.Value, “|”)(7) expression with the below code. For more functions >> Click Here!
=FormatDateTime( Split(Fields!FullName.Value, "|")(7),
DateFormat.GeneralDate)
Please click the preview tab to see the report.
