Error Handling Implementation in SSRS

V-rajbog
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 16208 red flag

In current IT world the concept of reporting plays a major role. Especially the top level management of any organization mostly relies on reports to enhance their business as well as analyse the customer behaviour.
Microsoft Corporation has provided the SQL Server Reporting Services (SSRS) is one of such tools which provide the high end features of reporting. But in this tool making error handling is somewhat complex as it needs lot of coding. So this document has initiated in order to make the error handling mechanism simpler.


 Download source code for Error Handling Implementation in SSRS

Introduction


In current IT world the concept of reporting plays a major role. Especially the top level management of any organization mostly relies on reports to enhance their business as well as analyse the customer behaviour.

Microsoft Corporation has provided the SQL Server Reporting Services (SSRS) is one of such tools which provide the high end features of reporting. But in this tool making error handling is somewhat complex as it needs lot of coding. So this document has initiated in order to make the error handling mechanism simpler.

Objective


This document briefs you to make error handling mechanism in SQL Server Reporting Services (SSRS) when user gets any database related issues while executing the SSRS reports. So if user gets any such issues, our report will show friendly and useful information instead of rich (original) error message on report body section. With this mechanism, we can also save the error details in the data base table for future reference.

Implementation of Error Handling mechanism



Following are the list of steps we need to follow for implementation of error handling mechanism in SSRS reports.

Step 1:

All the data sets of the report should contain one addition input parameter which should pass a unique information for every request (for every click of View Report button) made by the user.

Step 2:

Need to implement Try-Catch blocks for all the procedures that we have used from the SSRS reports through datasets. The catch section of every procedure should have the provision to save the error details into DB objects (data base table), if any error occurred while execution of that procedure.

Step 3:

Add one more additional dataset with the name “GetErrorInfo” which should call the store procedure (GET_ERROR_INFO). This procedure should be accepting a unique value. This is the unique values should be passed to all the data sets for every click of View Report button made by the user. This dataset will return the error information available in the data base table by verifying records with the unique id which has passes as input parameter.

Note: Please refer the Sample Scripts provided in section: Error handling Samples.

Step 4:

Enable the “Use Single Transaction When Processing Queries” option in data source properties.

Note: Step 4 is the most important step in implementation of error handling feature which makes all the query executions through a single transaction.

Step 5:

After successful completion of all the above mentioned steps, insert new table on SSRS report with custom error information which will be shown to the report user if he/she gets any error during execution of the report.

Following is the screen shot of a SSRS report before implementation of error handling: 

Following is the screen shot of the SSRS report after implementation of error handling mechanism:

Error Handling Samples


Following are the SQL objects used for this implementation.

Object Name

Object Type

Description

dbo.EMP

Table

Contains the employees data which will be shown on the report.

dbo.ERROR_LOG

Table

Contains all the error details information occurred through SSRS reports.

dbo.GET_EMP_VALUES

Procedure

This will get the employees data based on the report parameter selection.

dbo.GET_ERROR_INFO

Procedure

This will fetches the error details information by searching the table with input parameter (Unique ID).

 

The RDL File and SQL Script File is attached, you can download.

Pros and Cons


 Advantages:

  1. Look and feel of the report will be very good even if we get any error while execution of the report.
  2. The error detailed information will be logged in our custom data base table for the future reference.
  3. Ease to Customer: he can directly send the Unique Processing ID value to support team which helps them in identify the issue quickly and uniquely.

Disadvantages:

  • Report execution will be a bit slower after implementation of error handling feature as it should have the Option: “Use Single Transaction When Processing Queries” enabled.


Page copy protected against web site content infringement by Copyscape

About the Author

V-rajbog
Full Name: Rajasekhar Boggarapu
Member Level:
Member Status: Member
Member Since: 3/17/2011 3:21:25 AM
Country: India
Rajasekhar Boggarapu
http://www.dotnetfunda.com

Login to vote for this post.

Comments or Responses

Posted by: Nishithraj on: 3/18/2011 | Points: 25
Great
Posted by: Tripati_tutu on: 3/18/2011 | Points: 25
Nice Article.

Login to post response

Comment using Facebook(Author doesn't get notification)