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

Introduction

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)

Why SSRS?

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



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

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.

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

Figure 5 shows properties of the sales representative list dataset.

[database5.jpg]
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.

[database6.jpg]
Figure 6

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

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

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.

[database7.jpg]
Figure 7

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

<Code>
-- 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
</Code>

Figure 8 depicts the sales manager and city sales charts.

[database8.jpg]
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.

[database9.jpg]
Figure 9

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

Conclusion

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.





Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: March 19, 2015 @ 1:00 p.m. ET / 10:00 a.m. PT The 2015 Enterprise Mobile Application Survey asked 250 mobility professionals what their biggest mobile challenges are, how many employees they are equipping with mobile apps, and their methods for driving value with mobility. Join Dan Woods, Editor and CTO of CITO Research, and Alan Murray, SVP of Products at Apperian, as they break down the results of this survey and discuss how enterprises are using mobile application management and private …

  • Live Event Date: March 18, 2015 @ 2:00 p.m. ET / 11:00 a.m. PT Content is king, and businesses of all kinds want to leverage content to demonstrate their business benefits to customers, prospects, and partners alike. With new media options – 4K, 3D, HTML, and new devices – all becoming a part of your communications strategy, what tools are there that can speed time to value while reducing learning curve and duplication of efforts? Check out this upcoming eSeminar to learn how working with Adobe …

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date