Saturday, October 1, 2011

Crystal Report in ASP.NET

Reporting plays an important role in many information systems. Crystal report supports ASP.NET to design a report and render in ASP.NET page with records.
Introduction:

The purpose of this tutorial is to explore how to create crystal report in ASP.NET applications. Crystal report supports web Application reporting. Therefore ASP.NET has a feature to develop reporting with crystal reports.

Prerequisites:

    1. SQL Server 2005
    2. Visual studio 2005/2008
    3. Crystal report

Before starting to design a report, you need a database which is going to be used by the report. Here I have designed a small database for this demonstration.



The created database name is “HouseRental” with “HouseOwnerInfo” table. There are eight columns in the “HouseOwnerInfo.

Let us design a report for this table, get owners summary report which will be used to pass date parameter to process the report.

Note: The database design is only for demonstration purpose.

Let us start designing the report. For that, there are two standard ways. The first way is we can use the crystal report and second one is we can use the Visual studio.

First open Visual studio 2008 or 2005 and create new web project called “CrystalReportInASPNET”. Then right click the project and select Add New Item from the menu.

Select Crystal Report from the templates list window. When you select crystal report, give a meaningful name to the report. Here I have given “SampleRpt.rpt”. Then click Add button.

After you click the Add button, you will get a welcome window as shown by the following figure.        


Keep the Standard report format and click OK button. Then you need to create the database connection to access tables within the database. Once you click OK button, system gives a window to select the connection type as shown below.



This demonstration report is to work with SQL Server 2005 database. So you need to select SQL native client as provider (As above figure shows).

Then click Next button, to continue report creation process. Then you need to assign details of SQL Server and user credential to access the database from the report. Then you need to expand current connection tag, select your database connection then select the relevant table as shown by the following figure.


Then click Next button to continue. There you need to select fields from selected tables to design report as shown by the following figure.



Then click Next button. Now you have done necessary effort and finished the final step. As a result, you will get report like this.



Now you need to add crystal report viewer on the ASP.NET Webpage.

    Let us see, how we can add the report viewer on the page, just look at these html tags.

01    <body>
02   <form id="form1" runat="server" style="background:lightgreen;">
03<table cellpadding="0" cellspacing="0" width="100%">
04    <tr>
05   <td>
06        <CR:CrystalReportViewer ID="rptviewer" DisplayGroupTree="False"
07    runat="server" AutoDataBind="true" />
08   </td>
09    </tr>
10                </table>
11</form>
12</body>


Create a table in form object. Then add a HTML row and a column.
Then place the crystalReportViewer to the column as shown above HTML tag.
If we compile this application, it will give a compiler error “CR doesn’t exist”.

Therefore, you need  to register crystal report namespace on the top of the page as shown below.

1<%@ Register Assembly="CrystalDecisions.Web, Version=10.5.3700.0, Culture=neutral, PublicKeyToken=692fbea5521e1304" Namespace="CrystalDecisions.Web" TagPrefix="CR" %>


Note: when you double click on toolbox in the crystalreportviewer Visual studio will do all registration and etc.

Now write few lines to display report on the viewer.
1protected override void OnPreRender(EventArgs e)
2{
3        // attached our report to viewer and set database login.
4         ReportDocument report = new ReportDocument();
5         report.Load(Server.MapPath("SampleRpt.rpt"));
6         report.SetDatabaseLogon("username", "pwd", @"server", "database");
7         rptviewer.ReportSource = report;
8 }


Here we have to write code under the OnPreRender event,I hope you know about ASP.Net event life cycle. Please visit the following links for more information.

1. http://msdn.microsoft.com/en-us/library/ms178472.aspx
2. http://msdn.microsoft.com/en-us/library/ms178473.aspx

The reason for using “PreRender” event is this is the most suitable event for value setting in asp.net, Otherwise we can set in “PageLoad” event too. Here I did not use a button to load report on the viewer, therefore I have used “PreRender” event.

On my code just create a report document object and load report from the path. Here we used the Server. MapPath() method to get absolute path from the report file name.
  
Then build the web application and run, you will get result like follow.


  
Result seems cool right!

Note:

When you are trying to deploy in your live server, you may face one problem. That is database login fail to report. To ignore that, best approach is creating dataset schema to your tables, then attach the dataset to report. I hope to discuss this scenario on my next article. Keep your eyes on the http://aspnetvisual.blogspot.com/

Conclusion:
  
This article discussed about creates report with ASP.NET, and how to setup the CrystalReportViewer on the ASP.NET page.