SSRS Expressions query - SQL Server Report Builder

Andy 1 Reputation point
2021-02-19T09:17:38.737+00:00

Morning all!

Wonder if anyone could help. I have set up a simple report in Microsoft SQL Server Report Builder with a list of jobs from a data source and a basic bar chart with the "Job_Number" as the value (count) and the "Error_Number" as the category.

Easy enough right? Unfortunately I only just noticed that some of the records in the "Error No" column contain multiple values as in the example table below.

70028-image.png

I thought I had found a work around using the code below, however it appears it only counts the first error code from the records with multiple values. So in the bar chart the total number of errors was 10 instead of 13.

I have done a few searches on Google to find a solution but could not find anything similar to this situation.

Does anyone have any idea how I could resolve this? Any help would be greatly appreciated.

Thanks
Andrew

=SUM(IIF(Fields!erromsg.Value LIKE "3.2","3.2",
IIf(Fields!erromsg.Value LIKE "3.3","3.3",
IIf(Fields!erromsg.Value LIKE "3.4","3.4",
IIf(Fields!erromsg.Value LIKE "3.5","3.5","Other")
)))
)

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,772 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. ZoeHui-MSFT 32,081 Reputation points
    2021-02-19T09:47:19.76+00:00

    Hi anonymous user-3513,

    Please try like this:

    =Sum(IIf(Fields!erromsg.Value like "*,*" ,2,1), "DataSetName")  
    

    If I misunderstand your meaning, please incorrect me.

    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.

    What can I do if my transaction log is full?--- Hot issues November

    How to convert Profiler trace into a SQL Server table -- Hot issues November


  2. ZoeHui-MSFT 32,081 Reputation points
    2021-02-22T06:13:30.81+00:00

    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  
    

    70456-screenshot-2021-02-22-141017.jpg

    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