Print

Performance Report - Background colors based on dataset values

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".
copyraw 
select * from
(
SELECT TOP 1
	c.[Name] AS [ReportName]
	, e.[ReportID] AS [ReportID]
	, e.[TimeStart] AS [TimeStamp]
	, e.[Parameters] AS [Parameters]
	, e.[ByteCount] AS [Size]
	, e.[TimeDataRetrieval] AS [Data Retrieval]
	, e.[TimeProcessing] AS [Process Time]
	, e.[TimeRendering] AS [Render Time]
	, DATEDIFF(MILLISECOND, e.TimeStart, e.TimeEnd) [Total Time]
	,RANK() OVER (ORDER BY e.[TimeStart] DESC) [Rank]
FROM            
	[ReportServer].[dbo].[ExecutionLogStorage] e
	INNER JOIN 
	[ReportServer].[dbo].[Catalog] c ON e.ReportID = c.ItemID
WHERE
	e.[ReportID] = @ReportParameter1

union all
	
SELECT TOP 1
	c.[Name] AS [ReportName]
	, e.[ReportID] AS [ReportID]
	, e.[TimeStart] AS [TimeStamp]
	, e.[Parameters] AS [Parameters]
	, e.[ByteCount] AS [Size]
	, e.[TimeDataRetrieval] AS [Data Retrieval]
	, e.[TimeProcessing] AS [Process Time]
	, e.[TimeRendering] AS [Render Time]
	, DATEDIFF(MILLISECOND, e.TimeStart, e.TimeEnd) [Total Time]
	,RANK() OVER (ORDER BY e.[TimeStart] DESC) [Rank]
FROM            
	[ReportServer].[dbo].[ExecutionLogStorage] e
	INNER JOIN 
	[ReportServer].[dbo].[Catalog] c ON e.ReportID = c.ItemID
WHERE
	e.[ReportID] = @ReportParameter2

union all
	
SELECT TOP 1
	c.[Name] AS [ReportName]
	, e.[ReportID] AS [ReportID]
	, e.[TimeStart] AS [TimeStamp]
	, e.[Parameters] AS [Parameters]
	, e.[ByteCount] AS [Size]
	, e.[TimeDataRetrieval] AS [Data Retrieval]
	, e.[TimeProcessing] AS [Process Time]
	, e.[TimeRendering] AS [Render Time]
	, DATEDIFF(MILLISECOND, e.TimeStart, e.TimeEnd) [Total Time]
	,RANK() OVER (ORDER BY e.[TimeStart] DESC) [Rank]
FROM            
	[ReportServer].[dbo].[ExecutionLogStorage] e
	INNER JOIN 
	[ReportServer].[dbo].[Catalog] c ON e.ReportID = c.ItemID
WHERE
	e.[ReportID] = @ReportParameter3

union all
	
SELECT TOP 1
	c.[Name] AS [ReportName]
	, e.[ReportID] AS [ReportID]
	, e.[TimeStart] AS [TimeStamp]
	, e.[Parameters] AS [Parameters]
	, e.[ByteCount] AS [Size]
	, e.[TimeDataRetrieval] AS [Data Retrieval]
	, e.[TimeProcessing] AS [Process Time]
	, e.[TimeRendering] AS [Render Time]
	, DATEDIFF(MILLISECOND, e.TimeStart, e.TimeEnd) [Total Time]
	,RANK() OVER (ORDER BY e.[TimeStart] DESC) [Rank]
FROM            
	[ReportServer].[dbo].[ExecutionLogStorage] e
	INNER JOIN 
	[ReportServer].[dbo].[Catalog] c ON e.ReportID = c.ItemID
WHERE
	e.[ReportID] = @ReportParameter4		
) AS t1
ORDER BY
	t1.[TimeStamp] ASC
  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):
copyraw 
SELECT        Name, ItemID
FROM            Catalog
WHERE        Type = 2
ORDER BY Name
  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:
copyraw 
=IIF(
	Fields!Data_Retrieval.Value=MAX(Fields!Data_Retrieval.Value, "Report1"), 
	"Red", 
	IIF(
		Fields!Data_Retrieval.Value=MIN(Fields!Data_Retrieval.Value, "Report1"), 
		"Green", 
		IIF(
			Fields!Data_Retrieval.Value<AVG(Fields!Data_Retrieval.Value, "Report1"), 
			"Yellow", 
			"Orange"
		)
	)
)
  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:
copyraw 
select * from
(
SELECT TOP 2
	c.[Name] AS [ReportName]
	, e.[ReportID] AS [ReportID]
	, e.[TimeStart] AS [TimeStamp]
	, e.[Parameters] AS [Parameters]
	, e.[ByteCount] AS [Size]
	, e.[TimeDataRetrieval] AS [Data Retrieval]
	, e.[TimeProcessing] AS [Process Time]
	, e.[TimeRendering] AS [Render Time]
	, DATEDIFF(MILLISECOND, e.TimeStart, e.TimeEnd) [Total Time]
	,RANK() OVER (ORDER BY e.[TimeStart] DESC) [Rank]
FROM            
	[ReportServer].[dbo].[ExecutionLogStorage] e
	INNER JOIN 
	[ReportServer].[dbo].[Catalog] c ON e.ReportID = c.ItemID
WHERE
	e.[ReportID] = @ReportParameter1

union all
	
SELECT TOP 2
	c.[Name] AS [ReportName]
	, e.[ReportID] AS [ReportID]
	, e.[TimeStart] AS [TimeStamp]
	, e.[Parameters] AS [Parameters]
	, e.[ByteCount] AS [Size]
	, e.[TimeDataRetrieval] AS [Data Retrieval]
	, e.[TimeProcessing] AS [Process Time]
	, e.[TimeRendering] AS [Render Time]
	, DATEDIFF(MILLISECOND, e.TimeStart, e.TimeEnd) [Total Time]
	,RANK() OVER (ORDER BY e.[TimeStart] DESC) [Rank]
FROM            
	[ReportServer].[dbo].[ExecutionLogStorage] e
	INNER JOIN 
	[ReportServer].[dbo].[Catalog] c ON e.ReportID = c.ItemID
WHERE
	e.[ReportID] = @ReportParameter2

union all
	
SELECT TOP 2
	c.[Name] AS [ReportName]
	, e.[ReportID] AS [ReportID]
	, e.[TimeStart] AS [TimeStamp]
	, e.[Parameters] AS [Parameters]
	, e.[ByteCount] AS [Size]
	, e.[TimeDataRetrieval] AS [Data Retrieval]
	, e.[TimeProcessing] AS [Process Time]
	, e.[TimeRendering] AS [Render Time]
	, DATEDIFF(MILLISECOND, e.TimeStart, e.TimeEnd) [Total Time]
	,RANK() OVER (ORDER BY e.[TimeStart] DESC) [Rank]
FROM            
	[ReportServer].[dbo].[ExecutionLogStorage] e
	INNER JOIN 
	[ReportServer].[dbo].[Catalog] c ON e.ReportID = c.ItemID
WHERE
	e.[ReportID] = @ReportParameter3

union all
	
SELECT TOP 2
	c.[Name] AS [ReportName]
	, e.[ReportID] AS [ReportID]
	, e.[TimeStart] AS [TimeStamp]
	, e.[Parameters] AS [Parameters]
	, e.[ByteCount] AS [Size]
	, e.[TimeDataRetrieval] AS [Data Retrieval]
	, e.[TimeProcessing] AS [Process Time]
	, e.[TimeRendering] AS [Render Time]
	, DATEDIFF(MILLISECOND, e.TimeStart, e.TimeEnd) [Total Time]
	,RANK() OVER (ORDER BY e.[TimeStart] DESC) [Rank]
FROM            
	[ReportServer].[dbo].[ExecutionLogStorage] e
	INNER JOIN 
	[ReportServer].[dbo].[Catalog] c ON e.ReportID = c.ItemID
WHERE
	e.[ReportID] = @ReportParameter4		
) AS t1
WHERE
	t1.[Rank]=2
ORDER BY
	t1.[TimeStamp] ASC
  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 
Category: SQL Server Reporting Services :: Article: 404