Category Archives: SSRS Snippets

How to Display Report Execution Time in SQL Server Reporting Services (SSRS)

Often in Reporting Applications, faster rendering of reports is very essential. Especially in case of Operational Reporting Systems with large number of concurrent users, Reporting Rendering SLA is defined during the requirement phase of the project and is a very critical component of the Requirements / Reporting Application.
 
In these kind of solutions its good to display the total time a report takes for rendering from the moment the View Report button is clicked in the report manager. This feature can be used as a means of communicating the exact report rendering time to the users and also to identify the reports / scenarios which are exceeding / missing the rendering SLA.
 
Now let us see how to display the execution time in an SSRS report.
 
Since SSRS uses functions based on .Net framework we can use the following expression to get the report rendering time in seconds:
 
System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds
 
Similary to display the time in terms of minutes and hours we can just replace Seconds in the above expression with Minutes and Hours respectively.
 
Though execution time in terms of Hours is rarely/never used.
 
To make the display more user friendly or more presentable we can use the following expression:
 
=”Execution Time: ” +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours) + ” hour(s)” + ” , ” +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes) + ” minute(s)” + “, ” +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds) + ” second(s)”
 
Here is a sample report displaying the execution time in terms of Hours, Minutes and Seconds as shown below.
 
 
As shown in the above screenshot the report takes 1 Min 3 Seconds or 63 Seconds to render.
 
Now let us verify how accurate this number is by querying the SSRS report tables which hold the history/log of a report execution.
 
I ran the following query in the ReportServer database:
 
SELECT TOP 1
ReportID
, TimeStart
, TimeEnd
, TimeDataRetrieval
, TimeProcessing
, TimeRendering
, TimeDataRetrieval + TimeProcessing + TimeRendering AS TotalRenderingTime
FROM dbo.ExecutionLog
WHERE ReportID = ’90ABF38F-E280-4B75-B807-343AFB5FE696′
ORDER BY TimeStart DESC

In the above query, replace ’90ABF38F-E280-4B75-B807-343AFB5FE696′ with your Report ID.
And here are the results of the above query:

From the above screenshot we can see that the total time is 63172 milliseconds or ~63 Seconds or 1 Min & 3 Seconds, which is same as the Execution Time displayed in the previous screenshot.

Please let me know your comments / opinions about this article by leaving a comment below.

Note: This demonstration is tested in SSRS 2008. The implementation should be almost the same for SSRS 2005 as well.

Advertisements

Formatting Minutes as Hours and Minutes in SQL Server Reporting Services (SSRS)

Today I was working on an SSRS report for one of our clients and came across a specific formatting requirement around formatting Minutes as HH:MM. Browsed a little bit but could not get it working. Then sat for a while & tried few tricks by playing around with the expressions and finally got it working 🙂
 
Here is how I solved the problem.
 
The data available in the database is numeric and represents Total Minutes. When I initially wrote some expressions to derive Hours and Minutes from the Total Minutes I was able to get the data something like this:
 
10 Minutes -> 0:10
65 Minutes -> 1:5 etc.
 
However the need was to display the data something like below:
 
10 Minutes -> 00:10
65 Minutes -> 01:05 etc.
 
Now to get this formatting, first split the Minutes into Hours & Remaining Minutes as follows:
 
Hours part of TotalMinutes = Floor(Fields!Minutes.Value/60)
Remaining Minutes part of TotalMinutes = Fields!Minutes.Value Mod 60
 
Now to display the data in the intended format put the following expression in the textbox of the detailed row of the Table/Matrix of SSRS:
 
=Format(Floor(Fields!Minutes.Value/60),”00″) + “:” + Format((Fields!Minutes.Value Mod 60),”00″)
 
Below is a sample report with formatted minutes.
 
 
Hope you will find this useful. If yes feel free to leave a comment below.
 
Note: This article/demonstration is based on SSRS 2008. The options should be almost the same in SSRS 2005 as well.

How to use functions in SQL Server Reporting Services (SSRS)

 
SQL Server Reporting Services (SSRS) is a great BI tool offering lot of powerful features. SSRS 2008 has lot more features and is much more powerful than SSRS 2005. Irrespective of whether its SSRS 2005 or SSRS 2008, there are many features common between the two versions. Functions are one such powerful options/capabilities in SSRS.
 
In this article I will present detailed steps for the use of Functions in SSRS.
 
Often in reporting/BI projects using SSRS we use a lot of calculations/expressions in many fields in the reports. There are scenarios where in same formula/calculations is used across many fields in the report. Functions come in handy especially in this kind of scenarios.
 
Let us take a look at this sample report with simple calculations for finding Sum, Difference, Product & Percentage.
 
Create an SSRS report with the following query in the dataset:
 
SELECT 1 AS ColumnA, 2 AS ColumnB
UNION ALL
SELECT 3 AS ColumnA, 4 AS ColumnB
UNION ALL
SELECT 5 AS ColumnA, 6 AS ColumnB
UNION ALL
SELECT 7 AS ColumnA, 8 AS ColumnB
UNION ALL
SELECT 9 AS ColumnA, 10 AS ColumnB
UNION ALL
SELECT 11 AS ColumnA, 12 AS ColumnB
UNION ALL
SELECT 13 AS ColumnA, 14 AS ColumnB
UNION ALL
SELECT 15 AS ColumnA, 16 AS ColumnB
UNION ALL
SELECT 17 AS ColumnA, 18 AS ColumnB
UNION ALL
SELECT 19 AS ColumnA, 20 AS ColumnB
 
Now lets drop a Table control on to the report designer with the following six fields as shown in the below screenshot.
  • Column A
  • Column B
  • Sum of A & B
  • Difference of A & B
  • A Multiplied by B
  • A as a % of B
 
Now go to “Report -> Report Properties“. Report Properties dialog box will open and in this window click on “Code” in the left pane. Enter the following code with four different functions for calculating Addition, Difference, Product & Percentage in the Code window as shown in the below screenshot.
 
‘ Function for Addition
Function GetSum(ByVal A AS Integer, ByVal B AS Integer) As Integer
Dim VarSum AS Integer
VarSum = A + B
Return VarSum
End Function
 
‘ Function for Difference
Function GetDiff(ByVal A AS Integer, ByVal B AS Integer) As Integer
Dim VarDiff AS Integer
IF (A>=B) THEN
VarDiff = A – B
ELSE
VarDiff = B – A
END IF
Return VarDiff
End Function
 
‘ Function for Multiplication/Product
Function GetProduct(ByVal A AS Integer, ByVal B AS Integer) As Integer
Dim VarProduct AS Integer
VarProduct = A * B
Return VarProduct
End Function
 
‘ Function for Percentage
Function GetPercent(ByVal A AS Integer, ByVal B AS Integer) As Integer
Dim VarPercent AS Integer
VarPercent = (A/B)*100
Return VarPercent
End Function
 
 
Enter the following expressions in the detailed row of the report for the six fields as shown in the below screenshot.
 
 
Expr1: “=Fields!ColumnA.Value”
Expr2: “=Fields!ColumnB.Value”
Expr3: “=Code.GetSum(Fields!ColumnA.Value,Fields!ColumnB.Value)”
Expr4: “=Code.GetDiff(Fields!ColumnA.Value,Fields!ColumnB.Value)”
Expr5: “=Code.GetProduct(Fields!ColumnA.Value,Fields!ColumnB.Value)”
Expr6: “=CStr(Code.GetPercent(Fields!ColumnA.Value,Fields!ColumnB.Value)) + ” %””
 

Now go to the Preview tab and check out the results as shown in the below screenshot.

 
And the results are amazing. Having functions for scenarios where the same calculation is used in many places in a report is really helpful and makes the code more modular & neat.
 
Note: This has been tested & presented as per SSRS 2008.
 
If you find this post helpful then feel free to leave a comment below 🙂