Split in Report Builder

Question

I am working on Microsoft Report Builder I need to show in my report a set of data that comes in a text string separated by "," (comma)

I have a field where what I need to separate comes from like this: data1, data2, data3 and I need to show them in the report like this (in the same column)

data1

data2

data3

I mean leave a line for each item in the split.

Split (Fields! Data.Value, ",") (0)

I have tried this, it only shows me the value of position 0. Could it be done somehow? thanks!

Answer 1

You would be better off doing this in your dataset query. Assuming you are using SQL Server. then you can do the following.

You will need a string split function. If using SQL 2016 or later you can use the built-in function string_split(). If you are using an older version then there are plenty of examples of functions you can use. I can supply one if you can't find one.

If all your data comes in a single row then you can do something like this, replacing the @string with your table column.

declare @string varchar(max) = 'Data1, Data2, Data3'
select result = ltrim(rtrim(value)) from string_split(@string,',')

This will give the following results

If you have data on several rows then you can do a similar thing but use CROSS APPLY to repeat the function for each row.

Here's an example

declare @t table (string varchar(max))
insert into @t VALUES
('Data1, Data2, Data3'),
('DataA, DataB, DataC')

select result = ltrim(rtrim(x.value)) from @t 
cross apply string_split(string,',') as x 

which gives the following result