HomeASP.NET Designing and Developing Reports in ASP.NE...
Designing and Developing Reports in ASP.NET 2.0
This is the first article in a series focusing on designing/presenting native reports using ASP.NET 2.0. Throughout this series, I will focus on working with the “ReportViewer” control in local mode to develop web-based applications.
The entire source code for this article is available in the form of a downloadable zip file. The solution was developed using Microsoft Visual Studio 2005 Professional Edition on Microsoft Windows Server 2003 Enterprise Edition together with Microsoft SQL Server 2005 Developer Edition. I didn't really test the solution with any other/previous editions.
Solutions for developing and designing reports in ASP.NET 2.0
We have several solutions for designing and developing reports in ASP.NET 2.0. The most robust reporting solutions recommended by Microsoft are the following:
SQL Server 2005 Reporting Services (Server Mode)
Embedded Reporting (Local Mode) using Visual Studio 2005/Visual Web Developer
SQL Server 2005 Express Edition with Advanced Services
Crystal Reports
The first three are the solutions from Microsoft; Crystal Reports is from "Business Objects" (http://www.businessobjects.com/). To deploy Crystal Reports, based on the requirements, you may need to have a separate licensing scheme.
SQL Server 2005 Reporting Services comes free, if you have at least one SQL Server 2005 license. Embedded Reporting (local mode) is part of your Visual Studio 2005 (or Visual Web Developer) and local to every application you develop. It does not depend on any other server/service/tool.
"SQL Server 2005 Express Edition with Advanced Services" is a bit different from all of the others listed above. First of all, SQL Server 2005 Express Edition is a free database engine offered by Microsoft to embed as part of our application. It is basically a lightweight database engine for SQL Server 2005. Later came "SQL Server 2005 Express Edition with Advanced Services," which includes a database engine (a lightweight version of the SQL Server 2005 database engine), Management Studio Express (a lightweight version of SQL Server Management Studio), Reporting Services (a free lightweight version of SQL Server 2005 Reporting Services) and support for full text search.
Apart from the above solutions, we also have numerous third-party reporting solutions which can be directly integrated into ASP.NET 2.0/Visual Studio environment. The following area few of the most used third-party reporting solutions:
For simple reporting (especially reports based on columns/rows/grouping), we can still depend on advanced ASP.NET 2.0 server controls like GridView, DataList, Repeater, and so on. But they lack robustness and built-in support for exporting to other formats like Excel, PDF, Charts, and so forth.
Our discussion in this series will be limited to "Embedded Reporting" (local mode). If you have Visual Studio 2005 installed on your system, you are ready to start. But if you have Visual Web Developer, you may need to download an additional add-in (free) available from Microsoft. You can also download sample SQL Server 2005 databases (provided by Microsoft).
To design a report (locally) and embed it within our application, we first need to start with a strongly-typed data set. This data set will act as a data source for the report we are going to develop. At run-time, the data set gets automatically populated from the database (based on the table adapter) and the same data is served to the report, which finally is rendered using the "ReportViewer" control (with a mediating "ObjectDataSource").
Let us start with a step-by-step approach.
Open your Visual Studio 2005 IDE.
Go to File -> New Web Site.
In the "New Web Site" dialog box, select "ASP.NET web site" as the template, "File System" as the location, provide your own name (or SampleReport1) for your web site and finally click on "OK."
Right click on the solution and go for "Add New Item."
Within the "Add new item" dialog box, select "dataset" as the template and provide "SalesData.xsd" as the file name to create the data set. Finally, click on "add."
It prompts you to place the dataset-related code in the "app_code" folder, to which you simply respond by clicking on "OK."
Once it creates the dataset, it starts the "TableAdapter Configuration Wizard." You can select any existing connection or create a new connection by hitting the "New Connection" button.
Once you have selected the connection, click on "Next." At this level, it prompts for the connection string; simply press "Next" (to store the connection string in the web.config).
The next section asks for the command type, to which you select "Use SQL statements" and click "Next."
If you wanted to execute any stored procedures to generate the report, you need to make the appropriate selection.
Next, you can type your own SELECT statement to retrieve the data related to the report. You can also use the "Query Builder" button if you need to design a query in a GUI environment.
Click "Next," make sure that you select both check boxes, then hit "Next" again.
At this point, it gives you the results of your data set creation; check them and finally click "Finish."
Once you create the strongly-typed data set (as given in the previous section), your data set designer should look something like the following:
Now it is time to add a new embeddable report to our application. Go through the following steps.
Using the Solution Explorer, right click on the project and go for "Add New Item."
Within the "Add New Item" dialog box, select "Report" as the template, provide a name for your report (or SalesReport in this case) and hit "Add."
At this moment, the name of the report file will end with the extension "rdlc," which stands for "report definition language for client" (or for local mode). If you deal with SQL Server 2005 Reporting Services, it will have the extension "rdl" only, i.e. server mode.
At this moment, it shows you the report designer, where you design/develop the reports in very much the same fashion as you would for Crystal Reports. From the Toolbox, drag a table and drop it onto the layout. You can observe that it shows an Excel-like spread sheet with only three rows. The first row is for the header, the second is for detail (or records) and the third is for the footer.
At this point, you need to drag the columns (of the data set) from "Website Data Sources" onto each cell in the second row of the Excel-type grid. If "Website Data Sources" is not visible, you can make it visible by going to Data -> Show Data Sources
Once you drag all the columns, your screen should look something like the following:
You can add as large a number of columns to the right as the program allows by right-clicking and selecting "Insert Column to the Right."
Up to now, we created the data set and designed the report. Now it is time to view the report on a web page. Let us proceed with the following steps one by one to view the report.
Double click on "default.aspx" to open it.
Drag and drop the "ReportViewer" control from the toolbox.
From the smart tag of the "ReportViewer" control, select "SalesReport.rdlc" from the drop down.
At this moment, you can observe that the designer automatically creates an "ObjectDataSource" to bind.
You can go to the properties window of the ReportViewer control and play with different settings.
Now, you can press F5 to execute the solution. You will be prompted for the "Web.config" modification for debugging, to which you respond by clicking on "OK."
Until now you were able to develop reports without really writing a single line of code. When you want to attach different data sources based on specific criteria, you may need to add a few lines of code to your web page.
For this demonstration, I added one more "ReportViewer" control (ReportViewer1) and didn't set any properties at design time. I want to attach a report using a few lines of code. The following is the code used to achieve this:
ImportsMicrosoft.Reporting.WebForms
PartialClass _Default
Inherits System.Web.UI.Page
ProtectedSub Page_Load(ByVal sender AsObject, ByVal e As System.EventArgs) HandlesMe.Load
The most important issue to remember from the above code is that the name of the report, data source, must match with the syntax of "<datasetname>_<datatablename>" (SalesData_vSalesPerson).
The above is a very simple code sample used to bind a report to an ObjectDataSource. We can even bind the reports to SELECT statements dynamically (provided the data set and reports are already designed based on the columns retrieved by SQL SELECT statement). I shall cover these aspects in upcoming articles.
Any feedback, problems, suggestions, bugs, errors, improvements etc., are highly appreciated at http://jagchat.spaces.live.com.
ASP.NET 
Implement Gzip and Deflate Compression in ASP.Net
