Thursday, May 23, 2013
   
Text Size
Login

Performance Report - Background colors based on dataset values

Print

Last Updated on Friday, 02 December 2011

Why?
I've recently written a report for SQL Server Reporting Services 2008 R2 (SSRS) which will compare up to 4 reports and will compare the time taken for each one. The breakdown or what I was able to measure with the default installation are the times taken for "data retrieval", "processing", "rendering", and then the totals of these.

I haven't Googled this at the time of print so there may be a million better solutions out there, this is just how I did it. This may look like a horrible report which would fail an accessibility test but visually it says straight away which is the better report; and when comparing to the previous runs (using a second dataset) you can tell where changes were made and how this affected the reports' performance.

What?
What I'm trying to do is display a set of results (comparing various reports) in a table and then to color the backgrounds based on whether they are the fastest or slowest in the set.

Something like:
Displaying a color-based resultset


The DataSet
This should work on your reporting server as well. I'm using the database "ReportServer".

  1.  select * from 
  2.  ( 
  3.  SELECT TOP 1 
  4.       c.[Name] AS [ReportName] 
  5.       , e.[ReportID] AS [ReportID] 
  6.       , e.[TimeStart] AS [TimeStamp] 
  7.       , e.[Parameters] AS [Parameters] 
  8.       , e.[ByteCount] AS [Size] 
  9.       , e.[TimeDataRetrieval] AS [Data Retrieval] 
  10.       , e.[TimeProcessing] AS [Process Time] 
  11.       , e.[TimeRendering] AS [Render Time] 
  12.       , DATEDIFF(MILLISECOND, e.TimeStart, e.TimeEnd) [Total Time] 
  13.       ,RANK() OVER (ORDER BY e.[TimeStart] DESC) [Rank] 
  14.  FROM             
  15.       [ReportServer].[dbo].[ExecutionLogStorage] e 
  16.       INNER JOIN  
  17.       [ReportServer].[dbo].[Catalog] c ON e.ReportID = c.ItemID 
  18.  WHERE 
  19.       e.[ReportID] = @ReportParameter1 
  20.   
  21.  union all 
  22.        
  23.  SELECT TOP 1 
  24.       c.[Name] AS [ReportName] 
  25.       , e.[ReportID] AS [ReportID] 
  26.       , e.[TimeStart] AS [TimeStamp] 
  27.       , e.[Parameters] AS [Parameters] 
  28.       , e.[ByteCount] AS [Size] 
  29.       , e.[TimeDataRetrieval] AS [Data Retrieval] 
  30.       , e.[TimeProcessing] AS [Process Time] 
  31.       , e.[TimeRendering] AS [Render Time] 
  32.       , DATEDIFF(MILLISECOND, e.TimeStart, e.TimeEnd) [Total Time] 
  33.       ,RANK() OVER (ORDER BY e.[TimeStart] DESC) [Rank] 
  34.  FROM             
  35.       [ReportServer].[dbo].[ExecutionLogStorage] e 
  36.       INNER JOIN  
  37.       [ReportServer].[dbo].[Catalog] c ON e.ReportID = c.ItemID 
  38.  WHERE 
  39.       e.[ReportID] = @ReportParameter2 
  40.   
  41.  union all 
  42.        
  43.  SELECT TOP 1 
  44.       c.[Name] AS [ReportName] 
  45.       , e.[ReportID] AS [ReportID] 
  46.       , e.[TimeStart] AS [TimeStamp] 
  47.       , e.[Parameters] AS [Parameters] 
  48.       , e.[ByteCount] AS [Size] 
  49.       , e.[TimeDataRetrieval] AS [Data Retrieval] 
  50.       , e.[TimeProcessing] AS [Process Time] 
  51.       , e.[TimeRendering] AS [Render Time] 
  52.       , DATEDIFF(MILLISECOND, e.TimeStart, e.TimeEnd) [Total Time] 
  53.       ,RANK() OVER (ORDER BY e.[TimeStart] DESC) [Rank] 
  54.  FROM             
  55.       [ReportServer].[dbo].[ExecutionLogStorage] e 
  56.       INNER JOIN  
  57.       [ReportServer].[dbo].[Catalog] c ON e.ReportID = c.ItemID 
  58.  WHERE 
  59.       e.[ReportID] = @ReportParameter3 
  60.   
  61.  union all 
  62.        
  63.  SELECT TOP 1 
  64.       c.[Name] AS [ReportName] 
  65.       , e.[ReportID] AS [ReportID] 
  66.       , e.[TimeStart] AS [TimeStamp] 
  67.       , e.[Parameters] AS [Parameters] 
  68.       , e.[ByteCount] AS [Size] 
  69.       , e.[TimeDataRetrieval] AS [Data Retrieval] 
  70.       , e.[TimeProcessing] AS [Process Time] 
  71.       , e.[TimeRendering] AS [Render Time] 
  72.       , DATEDIFF(MILLISECOND, e.TimeStart, e.TimeEnd) [Total Time] 
  73.       ,RANK() OVER (ORDER BY e.[TimeStart] DESC) [Rank] 
  74.  FROM             
  75.       [ReportServer].[dbo].[ExecutionLogStorage] e 
  76.       INNER JOIN  
  77.       [ReportServer].[dbo].[Catalog] c ON e.ReportID = c.ItemID 
  78.  WHERE 
  79.       e.[ReportID] = @ReportParameter4           
  80.  ) AS t1 
  81.  ORDER BY 
  82.       t1.[TimeStamp] ASC 
Dataset for all reports (used for the dropdown parameters):

  1.  SELECT        Name, ItemID 
  2.  FROM            Catalog 
  3.  WHERE        Type = 2 
  4.  ORDER BY Name 
If you want to use these datasets, parameter options should be that the Name is the "Label" and ItemID is the "Value", just in case you didn't pick up on this. If no type is specified (type=2) then datasources and folders will also show.

The Side-by-Side Table
As you know, most datasets will return the rows as ... rows. My pic above shows my 4 reports results side by side. To do this, I had to insert a Matrix rather than a Tablix:
  1. Insert a "Matrix" into the body of your report.
  2. Drag "ReportName" into the field that says "Columns".
  3. Right-click on the grey left margin and select "Insert Row" then "Outside Group - Below".
  4. Delete the row with the left parenthesis
    Displaying a color-based resultset
  5. Click on the dropdown of "Data" and select "TimeStamp".
  6. Click in the left of that column and type the word "TimeStamp".
  7. Right-click on the left grey margin of this and select "Insert Row" then "Below".
    Displaying a color-based resultset
  8. Repeat with all values you want to compare.
    Displaying a color-based resultset
  9. For each Data cell, Set the background fill expression as per the MDX expression below.

The MDX Expression
This is to be applied to the "Fill" expression of the textbox with the field name changed for each cell you apply this to; note that in the following example, "Data_Retrieval" is the field to compare (of all "Data_Retrieval" values) and "Report1" is the name of my dataset:

  1.  =IIF( 
  2.       Fields!Data_Retrieval.Value=MAX(Fields!Data_Retrieval.Value, "Report1"),  
  3.       "Red",  
  4.       IIF( 
  5.            Fields!Data_Retrieval.Value=MIN(Fields!Data_Retrieval.Value, "Report1"),  
  6.            "Green",  
  7.            IIF( 
  8.                 Fields!Data_Retrieval.Value<AVG(Fields!Data_Retrieval.Value, "Report1"),  
  9.                 "Yellow",  
  10.                 "Orange" 
  11.            ) 
  12.       ) 
  13.  ) 
Additional but Trivial Note: The MDX expression above says "Yellow" = "Below Average" which is true. But if you are comparing 4 reports and these have the data values of say 1, 2, 3, 24; then 1 will be green and 24 will be red, but both 2 and 3 will be yellow because 3 is still less than the average of all 4 (average=7.5).

Previously on SSRS...
If you want the second dataset I use in my report to show how the reports you are comparing performed in their previous run, here it is:

  1.  select * from 
  2.  ( 
  3.  SELECT TOP 2 
  4.       c.[Name] AS [ReportName] 
  5.       , e.[ReportID] AS [ReportID] 
  6.       , e.[TimeStart] AS [TimeStamp] 
  7.       , e.[Parameters] AS [Parameters] 
  8.       , e.[ByteCount] AS [Size] 
  9.       , e.[TimeDataRetrieval] AS [Data Retrieval] 
  10.       , e.[TimeProcessing] AS [Process Time] 
  11.       , e.[TimeRendering] AS [Render Time] 
  12.       , DATEDIFF(MILLISECOND, e.TimeStart, e.TimeEnd) [Total Time] 
  13.       ,RANK() OVER (ORDER BY e.[TimeStart] DESC) [Rank] 
  14.  FROM             
  15.       [ReportServer].[dbo].[ExecutionLogStorage] e 
  16.       INNER JOIN  
  17.       [ReportServer].[dbo].[Catalog] c ON e.ReportID = c.ItemID 
  18.  WHERE 
  19.       e.[ReportID] = @ReportParameter1 
  20.   
  21.  union all 
  22.        
  23.  SELECT TOP 2 
  24.       c.[Name] AS [ReportName] 
  25.       , e.[ReportID] AS [ReportID] 
  26.       , e.[TimeStart] AS [TimeStamp] 
  27.       , e.[Parameters] AS [Parameters] 
  28.       , e.[ByteCount] AS [Size] 
  29.       , e.[TimeDataRetrieval] AS [Data Retrieval] 
  30.       , e.[TimeProcessing] AS [Process Time] 
  31.       , e.[TimeRendering] AS [Render Time] 
  32.       , DATEDIFF(MILLISECOND, e.TimeStart, e.TimeEnd) [Total Time] 
  33.       ,RANK() OVER (ORDER BY e.[TimeStart] DESC) [Rank] 
  34.  FROM             
  35.       [ReportServer].[dbo].[ExecutionLogStorage] e 
  36.       INNER JOIN  
  37.       [ReportServer].[dbo].[Catalog] c ON e.ReportID = c.ItemID 
  38.  WHERE 
  39.       e.[ReportID] = @ReportParameter2 
  40.   
  41.  union all 
  42.        
  43.  SELECT TOP 2 
  44.       c.[Name] AS [ReportName] 
  45.       , e.[ReportID] AS [ReportID] 
  46.       , e.[TimeStart] AS [TimeStamp] 
  47.       , e.[Parameters] AS [Parameters] 
  48.       , e.[ByteCount] AS [Size] 
  49.       , e.[TimeDataRetrieval] AS [Data Retrieval] 
  50.       , e.[TimeProcessing] AS [Process Time] 
  51.       , e.[TimeRendering] AS [Render Time] 
  52.       , DATEDIFF(MILLISECOND, e.TimeStart, e.TimeEnd) [Total Time] 
  53.       ,RANK() OVER (ORDER BY e.[TimeStart] DESC) [Rank] 
  54.  FROM             
  55.       [ReportServer].[dbo].[ExecutionLogStorage] e 
  56.       INNER JOIN  
  57.       [ReportServer].[dbo].[Catalog] c ON e.ReportID = c.ItemID 
  58.  WHERE 
  59.       e.[ReportID] = @ReportParameter3 
  60.   
  61.  union all 
  62.        
  63.  SELECT TOP 2 
  64.       c.[Name] AS [ReportName] 
  65.       , e.[ReportID] AS [ReportID] 
  66.       , e.[TimeStart] AS [TimeStamp] 
  67.       , e.[Parameters] AS [Parameters] 
  68.       , e.[ByteCount] AS [Size] 
  69.       , e.[TimeDataRetrieval] AS [Data Retrieval] 
  70.       , e.[TimeProcessing] AS [Process Time] 
  71.       , e.[TimeRendering] AS [Render Time] 
  72.       , DATEDIFF(MILLISECOND, e.TimeStart, e.TimeEnd) [Total Time] 
  73.       ,RANK() OVER (ORDER BY e.[TimeStart] DESC) [Rank] 
  74.  FROM             
  75.       [ReportServer].[dbo].[ExecutionLogStorage] e 
  76.       INNER JOIN  
  77.       [ReportServer].[dbo].[Catalog] c ON e.ReportID = c.ItemID 
  78.  WHERE 
  79.       e.[ReportID] = @ReportParameter4           
  80.  ) AS t1 
  81.  WHERE 
  82.       t1.[Rank]=2 
  83.  ORDER BY 
  84.       t1.[TimeStamp] ASC 
  • ssrs background color based on results
  • ssrs colours depending on field values
Add Comment

Name:

Email:

Website:

Message:



Human Check:

Security code
Refresh

Please type what you see in the image above:

Main Menu

Latest Posts

  • Joes Revolver Map (JRM)

    • Fri 17-May-13
      Hmmm... Sounds like a problem with the identifier. Was it working before and has there been a change ...
      Joel Lipman  
    • Fri 17-May-13
      Hello Joel: Yes, I do have it published on all pages of the site. I just went back to Revolver maps to ...
      Bill Duncan  
    • Fri 17-May-13
      Hi Bill, From the developers of RevolverMaps, "the module would need to be published on every page ...
      Joel Lipman  
    • Fri 17-May-13
      Hi Bill, I'll investigate further as you're not the first to say this happens. In the meantime, simply ...
      Joel Lipman  
    • Fri 17-May-13
      Its a great extension. But when I set it up I only show my presence on the 3D map and no other visitors ...
      Bill Duncan

Joes Word Cloud

Copyright © 1999-2013 Joel Lipman .Com

Restore Default Settings