Performance Report - Background colors based on dataset values

: Joel Lipman; SQL Server Reporting Services; 02 December 2011; Hits: 14600

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:

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

copy raw

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

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

Dataset for all reports (used for the dropdown parameters):

copy raw

SELECT        Name, ItemID
FROM            Catalog
WHERE        Type = 2
ORDER BY Name

SELECT Name, ItemID
FROM Catalog
WHERE Type = 2
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:

Insert a "Matrix" into the body of your report.
Drag "ReportName" into the field that says "Columns".
Right-click on the grey left margin and select "Insert Row" then "Outside Group - Below".
Delete the row with the left parenthesis
Click on the dropdown of "Data" and select "TimeStamp".
Click in the left of that column and type the word "TimeStamp".
Right-click on the left grey margin of this and select "Insert Row" then "Below".
Repeat with all values you want to compare.
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:

copy raw

=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"
		)
	)
)

=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"
)
)
)

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:

copy raw

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