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: December 11, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT Market pressures to move more quickly and develop innovative applications are forcing organizations to rethink how they develop and release applications. The combination of public clouds and physical back-end infrastructures are a means to get applications out faster. However, these hybrid solutions complicate DevOps adoption, with application delivery pipelines that span across complex hybrid cloud and non-cloud environments. Check out this …

  • CentreCorp is a fully integrated and diversified property management and real estate service company, specializing in the "shopping center" segment, and is one of the premier retail service providers in North America. Company executives travel a great deal, carrying a number of traveling laptops with critical current business data, and no easy way to back up to the network outside the office. Read this case study to learn how CentreCorp implemented a suite of business continuity services that included …

Most Popular Programming Stories

More for Developers

RSS Feeds