Hi anonymous user-3513,
Thanks for your detailed description, your requirement is a little hard to meet with SSRS expression.
I'd suggest that you may process your data with T-SQL and then add the dataset to the report builder.
If your SQL Server version is 2016 and later, you may directly using the below code.
create table Job
(Job_number int identity(1,1),
Error_Number varchar(20))
insert into Job (Error_Number) values
('3.2'),
('3.3'),
('3.2'),
('3.2,3.4'),
('3.4'),
('3.2,3.3'),
('3.4'),
('3.5'),
('3.4,3.5'),
('3.3')
SELECT value Error_Number,count(value) Count_of_job_numbers
FROM Job
CROSS APPLY STRING_SPLIT(Error_Number, ',')
group by value
If your version is earlier, please create the function and then use it as show.
CREATE FUNCTION [dbo].[SplitString]
(
@List NVARCHAR(MAX),
@Delim VARCHAR(255)
)
RETURNS TABLE
AS
RETURN ( SELECT [Value] FROM
(
SELECT
[Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
FROM sys.all_objects) AS x
WHERE Number <= LEN(@List)
AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
) AS y
);
SELECT value Error_Number,count(value) Count_of_job_numbers
FROM Job
CROSS APPLY [dbo].[SplitString](Error_Number, ',')
group by value
And then we could easily use it into our SSRS reports.
Regards,
Zoe
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October