Crystal Report was the most recommended reporting tool used with ASP.NET web application before SQL server reporting service (SSRS) was first introduced in 2004 as an add-on to SQL server 2000 by Microsoft. An enhanced version of this SQL server reporting service was released with SQL server 2005 and the latest version of SSRS is launched with SQL Server 2008. A SSRS report is an XML file with a .RDL extension (Report Definition Language). After installation of the business intelligent development studio (BIDS) tool , developers can create a RDL report file using Microsoft Visual Studio editor. SSRS also provides a web service (server) interface for custom reporting applications. A .RDL files needs to be uploaded to that server before it can be accessed by any custom application. This server environment can be easily administrated by web interface (any web browser, recommended Internet Explorer)
You don’t need to purchase a licensed copy of any third party reporting tool like Crystal Report. SSRS can be used to develop and deliver interactive and printed reports. An RDL report can be exported in a variety of formats like Excel, PDF, CSV, XML, TIFF and HTML. Using SQL server 2008 SSRS developers can also export RDL reports in Microsoft Word (DOC) format. Most important, SSRS can be very easily integrated with ASP.NET web applications. Using the report viewer control ASP.NET developers can easily embed RDL reports in web forms. The ASP.NET report viewer control can processes RDL reports in two different ways (a) server processing, where the report is rendered by and obtained from the report server(web service); and (b) local processing, where the report viewer control renders the RDL file itself added to the solution. In this article I will demonstrate to you how to create an RDL report and integrate that with local ASP.NET web application.
Setup Development Environment
For creating RDL sample reports, I have used Microsoft SQL Server 2008 Report Builder Studio version 2.0. It’s a standalone editor having all the SSRS features supported by SQL Server 2008 and can be downloaded here from MSDN. SQL server 2008 report builder studio have following features.
- It has a user friendly RDL report development environment.
- Data visualizations include new controls like charts and gauges.
- Export to Microsoft Office Word format functionality is added with SQL Server 2008 SSRS.
- Wizard based table, matrix and chart creation functionality.
- .NET Developers can edit and open RDL reports already deployed in report server (web service).
For developing ASP.NET application I have used Microsoft Visual Studio 2008 with .NET framework 3.5. For the sample data required for SSRS report I have created a sales database using SQL Server 2008 (Express Edition). The sample sales database contains 3 tables.
TblSales table contains sales representative information,
tblproduct table have the products list; these products are marketed by sales representatives and
tblMonthlySales table keeps annual and monthly sales data (actual and target sales). Figure 1 shows records of the TblSales table.
Records of the tblproduct table I have used in the report are shown below in Figure 2.
And records of
tblMonthlySales table are listed below in Figure 3.
Figure 4 depicts the entity relationship diagram (database diagram) of sales database.