Developing Reports for ASP.NET Web applications Using SQL Server 2008 Reporting Services

by Tapas Pal

Learn how the SQL server reporting service allows ASP.NET developers to design and deploy interactive data driven reports in different formats like Excel, PDF, CSV, XML, TIFF and HTML.


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.

Figure 1

Records of the tblproduct table I have used in the report are shown below in Figure 2.

Figure 2

And records of tblMonthlySales table are listed below in Figure 3.

Figure 3

Figure 4 depicts the entity relationship diagram (database diagram) of sales database.

Figure 4

Develop RDL Report

After the sales database creation, the next step was to create one sales report using Microsoft SQL Server 2008 Report Builder Studio. The 1st dataset I created in this report with the following SQL query to show the entire list of sales representatives.

select salesmanname,country,city,productname,managername
from tblSales left outer join tblProduct on tblSales.ProductSales = tblProduct.productid

Figure 5 shows properties of the sales representative list dataset.

Figure 5

I have assigned this dataset to the data source property of a table control (dragged and dropped in the sales report) to display sales representative's details. Figure 6 shows the sales representatives list when I executed the sales report in report builder.

Figure 6

Next, to analyze sales representative-wise monthly sales data (target and actual sales, I have created another dataset with following SQL query.

select [salesmanname],[YEAR], [MONTH], salestarget,actualsales
from tblMonthlySales left outer join tblSales on tblMonthlySales.SalesManid = tblSales.SalesManId

Using the Insert Chart option I have added a columnar chart control in the sales report and assigned the newly created dataset to the data source property of the chart control to display sales representative-wise monthly sales data. Figure 7 shows monthly sales data.

Figure 7

Now to show sales manager and city-wise sales charts I've used following 2 SQL queries.

-- City wise sales report 
select city,SUM(salestarget) [Total Target], SUM(actualsales) [Total Sales] from tblMonthlySales
left outer join tblSales on tblMonthlySales.SalesManid = tblSales.SalesManId
group by city

-- sales manager wise sales report

select ManagerName,SUM(salestarget) [Total Target], SUM(actualsales) [Total Sales] from tblMonthlySales
left outer join tblSales on tblMonthlySales.SalesManid = tblSales.SalesManId
group by ManagerName

Figure 8 depicts the sales manager and city sales charts.

Figure 8

Develop ASP.NET Web Application Report Viewer

An RDL report is a server side processing file. To use this RDL report in a local ASP.NET web application, I have converted that to RDLC extension from Report Builder Studio using the 'Save As' option. After that I created an ASP.NET web application project using Microsoft Visual Studio 2008 and .NET framework 3.5 and added the converted SalesReport.rdlc file to the newly created project. I also modified the data source property of the SalesReport.rdlc file.

Finally I have added a web form named SalesReportViewer.aspx where I have shown the report output. To render the SalesReport.rdlc report I have also added a report viewer control to the SalesReportViewer.aspx web form and selected SalesReport.rdlc in the ReportViewer tasks smart tags panel. Using "Choose Data Sources" I have selected the required data sources for this report as shown in Figure 9.

Figure 9

Once all the configurations are complete I pressed F5 to compile and execute my report viewer application.


RDL reports can be integrated with ASP.NET web applications if the SSRS reporting server exists. RDL report files can only be accessible by ASP.NET web application using the web service.

This article was originally published on Thursday Jul 29th 2010
Mobile Site | Full Site