Ad hoc Reporting using SQL Server Reporting Services 2005

Problem Situation

Frequently, we come across situations where the reports display large amount of data. At a given instance, this large amount of data could contain both desired as well as undesired data. In such situations, the users generally request for a functionality where they can choose the data to be displayed in the report. This leads to the need for Ad hoc Reporting whose implementation details are discussed in this document.

Introduction

Microsoft SQL Server Reporting Services is a comprehensive solution for creating, managing, and delivering both traditional, paper-oriented reports and interactive, web-based reports from different types of data sources. This article covers the implementation details of generating an Ad hoc Report using SQL Reporting Services.

Need For Ad hoc Reporting

Ad hoc Reporting is used to describe dynamic report creation by the users. Ad hoc Reports are very beneficial in scenarios where large amount of data is being displayed in the report; An Ad hoc report would contain desired as well as undesired data. In such scenarios, the users would like to have control over the choice of data that is to be displayed in the report. Depending on the user’s choice, the report is dynamically generated to display only the desired data.

Problem Scenario and Requirement

Project Scenario:
The employee details are to be displayed in the report. Around 175 fields of data are associated with every employee. Hence, the user requested for some functionalities, using which he/she could control the display of fields in the report. This is why, the Ad hoc Reporting approach was followed.

Project Requirement:

• The SQL reporting services are used for generating reports.
• The screen developed using .Net 2005 is used as a front-end to enable the users to choose the data to be displayed in the report.

Pre-requisites

• . Net 2005
• SQL server 2005
• SQL reporting services

Implementation Details

Step 1: Screen Design

1. The screen was developed using .Net 2005. The screen consisted of all the 175 fields that were associated with an employee.
2. Every field had a checkbox associated with it. The user had to check the checkboxes against the fields that were to be displayed in the report.
3. The user could also enter values in the textboxes against each field name; the values in the field were used to filter out the records that were displayed in the report. (This is an optional functionality)
4. Once the desired fields are checked, the user clicks View Report on the screen. A string is generated that contains the names of the fields that are to be displayed in the report. This forms the ‘select clause’ of the dynamic query that is used to retrieve the desired records from the database via a stored procedure.
5. If any values are entered against any field names then another string containing the filter criteria is generated. This forms the ‘where clause’ of the dynamic query that is used to retrieve the desired records from the database via a stored procedure

Step 2: Database Design

The ‘select clause’ and the ‘where clause’ (‘where clause’ is optional) are passed as parameters to the stored procedure which will be used by the report to display the desired records.

Step 3: SQL Report Design

1. The Report will make use of a dataset which will call the appropriate stored procedure (the same stored procedure that was referred in the database design).
2. Once the dataset is configured, the next step is to design the Report Layout.
3. Drag and drop all the 175 columns in a table. It means that the table will have 175 columns corresponding to the 175 fields.
4. Use the Column Visibility property, to hide or unhide the column depending on whether the particular column is returned by the stored procedure.
   The syntax is as follows:
   Select a column.
   For e.g. EmployeeName column ->Go to Visibility property -> Go to Hidden property -> Write the following code in the Hidden property = IIf(Fields!EmployeeName.IsMissing,true,false)
5. At runtime the data set will return only those columns depending on the ‘select clause’ generated by the application.
6. Since the Column Visibility property of every column is set, at runtime, only those columns that are returned by the stored procedure will be visible.

Limitations
The limitation of the discussed approach is that the process of report generation slows down when large volumes of data is processed .This is because of the dynamic nature of the report.

References
MSDN Help for SQL Reporting Services http://msdn2.microsoft.com/
Additional information can be found at http://www.devx.com/dbzone/Article/28047

Related Posts

1. DB2 with DotNet and SQL Server Reporting Services
2. Step by Step : SQL Server 2005 Analysis Services (SSAS) Project
3. Sybase with DotNet Application and SQL Server Reporting Services
4. Troubleshooting SSRS Error “The report server is not responding”
5. Introduction to JOINS in SQL Server

Tags: Ad hoc Reporting, SQL Server 2005, SQL Server Reporting Services, SSRS