question

Andy-3513 avatar image
0 Votes"
Andy-3513 asked ·

SSRS Expressions query - SQL Server Report Builder

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
image.png (8.4 KiB)
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Zoehui-MSFT avatar image
0 Votes"
Zoehui-MSFT answered ·

Hi @Andy-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


· 1 · Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Zoehui-MSFT !,

That would count the number of times a field has a comma in. I need it to count all of the error codes by error code as in the expected results table below.

Thanks
Andrew

70017-image.png


0 Votes 0 ·
image.png (20.4 KiB)
Zoehui-MSFT avatar image
0 Votes"
Zoehui-MSFT answered ·

Hi @Andy-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






· 5 · Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

may I know how's the issue going on now?

0 Votes 0 ·
Andy-3513 avatar image Andy-3513 Zoehui-MSFT ·

Hi @Zoehui-MSFT !

Apologies for the delay, I had a high priority job come in.

Okay for the code you suggested inserting values how would I input a query into that? would it be simple as SELECT * FROM Table WHERE record is between dates?

Just trying to understand how I could aapt it to my situation.

Thanks
Andrew

create table Job
(Job_number int identity(1,1),
Error_Number varchar(20))

insert into Job (Error_Number)
SELECT * FROM TABLE
WHERE CRATED DATE IS BETWEEN '01-04-2019' AND '31-03-2020'

SELECT value Error_Number,count(value) Count_of_job_numbers
FROM Job
CROSS APPLY STRING_SPLIT(Error_Number, ',')
group by value

0 Votes 0 ·

Hi @Andy-3513, the code you share seems is correct, you may have a test and if there's something wrong you may @ me.

0 Votes 0 ·
Show more comments