Report generation is a very handy feature for any website. Reports help the users to analyze the data more efficiently and give them different views of the information. There are many ways to create a report which includes SQL SERVER Reporting Services, third party reporting tools etc. But most of these tools come with a price and as a developer we will be interested in good free products. So, if you have Visual Studio.NET 2005 installed you can also enjoy the feature of Crystal Reports. In this article I will explain that how you can generate Crystal Reports and display it in the ASP.NET web application.

Introduction:

Report generation is a very handy feature for any website. Reports help the users to analyze the data more efficiently and give them different views of the information. There are many ways to create a report which includes SQL SERVER Reporting Services, third party reporting tools etc. But most of these tools come with a price and as a developer we will be interested in good free products. So, if you have Visual Studio.NET 2005 installed you can also enjoy the feature of Crystal Reports. In this article I will explain that how you can generate Crystal Reports and display it in the ASP.NET web application.

Database Design:

We will be creating a simple database which will consist of three tables, Users, Exams and UserExams. The scenario is simple, a student comes to take the exam and his highest score is printed in the report along with the graph which shows the student’s progress.

 

Take a look at the screen shot below which shows the relationship between the two tables.

 

 

 

There is a simple query embedded in the C# code which is used to get the highest score in a particular exam type. This means that if the student has given Exam 1 5 times then the query will pick up the highest score.

 

You can take a look at the query below:

 

@"SELECT ue.ExamID,MAX(ue.Score) AS Score,u.FirstName,u.LastName,e.Title FROM UserExams ue                             JOIN Users u ON u.UserID = ue.UserID                             JOIN Exams e ON e.ExamID = ue.ExamID                             WHERE u.UserID = @UserID                              GROUP BY ue.ExamID,u.FirstName,u.LastName,e.Title";

 

As, you can see that the above query that the report is dependent on the UserID. Let’s start by populating the DropDownList with the names of the students. Once, the list is populated we can select a particular student and send the ID to the Crystal Report.

 

Populating the DropDownList With Students:

Take a look at the code below which is used to accomplish the task of populating the DropDownList.

 

private void BindData()     {         string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;           SqlConnection myConnection = new SqlConnection(connectionString);         SqlDataAdapter ad = new SqlDataAdapter("SELECT UserID, FirstName + LastName AS [Name] FROM Users", myConnection);         DataSet ds = new DataSet();         ad.Fill(ds);           ddlStudents.DataSource = ds;         ddlStudents.DataTextField = "Name";         ddlStudents.DataValueField = "UserID";         ddlStudents.DataBind();       }

 

Now, when you run the page you will see that the DropDownList is populated.

 

 

 

Creating the Typed DataSet:

There are various ways to send the parameters from the ASP.NET application to the Crystal Report. But the most flexible approach is when instead of sending the parameters to the Crystal Report you bind the report to a custom made collection. In this case the custom collection will be a typed dataset. Simply, add a typed dataset to the project and create the fields displayed in the image below:

 

 

 

Creating Crystal Reports Videos   GridViewGuy Videos website has couple of videos that shows that how you can create crystal reports. These videos are 5-10 minutes long and each video start the implementation from the scratch hence allowing the user to learn from start to finish. You can view the videos at www.videos.gridviewguy.com.

Creating the Report:

Creating the report is also fairly simple and the whole process is done by using the wizard. When you add a new Crystal Report to the project the wizard will kick in and you will see the following screens:

 

Click OK button at the bottom.

 

 

Click Next >.

 

 

Click Next >.

 

 

Click Next >

 

 

Click Next >

 

After you press the Finish button you will see the template for your report.

 

 

You can also add the graph control from the Crystal Reports menu and add a graph on the report. Graph helps to better visualize the data and is one of the most important feature in any form of report.

 

After adding the graph you can right click on the graph (chart) and select the “Chart Expert”. This will allow you to adjust the placement and the data displayed on the chart.

 

 

 

Populating the Report Dynamically:

Since, we are not using the CrystalReportSource control hence we will be dynamically populating the report with the help of the typed dataset called UserDataSet which we created earlier.

 

protected void Btn_DisplayClick(object sender, EventArgs e)     {         string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;           int userID = Convert.ToInt32(ddlStudents.SelectedValue);                 string selectQuery = @"SELECT ue.ExamID,MAX(ue.Score) AS Score,u.FirstName,u.LastName,e.Title FROM UserExams ue                             JOIN Users u ON u.UserID = ue.UserID                             JOIN Exams e ON e.ExamID = ue.ExamID                             WHERE u.UserID = @UserID                              GROUP BY ue.ExamID,u.FirstName,u.LastName,e.Title";           SqlConnection myConnection = new SqlConnection(connectionString);         SqlCommand myCommand = new SqlCommand(selectQuery, myConnection);         myCommand.Parameters.AddWithValue("@UserID", userID);           SqlDataAdapter ad = new SqlDataAdapter(myCommand);           UserDataSet user = new UserDataSet();         ad.Fill(user,"Users");                                        report.FileName = Server.MapPath("ExamReport.rpt");           report.SetDataSource(user);           CrystalReportViewer1.ReportSource = report;           }

 

 

The above code will be fired when the user clicks the display report button. You will need to add the following namespaces to work with this example.

using System.Data.SqlClient; using CrystalDecisions.CrystalReports.Engine; using CrystalDecisions.Shared;

 

And you will also need to declare the ReportDocument object as the class object.

 

private ReportDocument report = new ReportDocument();

 

 

You must have the CrystalReportViewer control in order to view the report. Take a look at the HTML code below which declares the CrystalReportViewer control.

 

<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />

 

Now, when you run the page you will see a very neat report displayed on the web page.

 

 

If you select a different name from the list then you will see a different report.

 

 

Disposing the ReportDocument Object When Done:

There are a limited number of reports that you can create in a day. That number is stored in the registry settings and can be increased or decreased by the administrator. Instead of dealing with the registry settings you should always dispose the ReportDocument object when you are not using it. This can be done in the Page_UnLoad event.

 

protected override void  OnUnload(EventArgs e)     {       base.OnUnload(e);         // close the report connection      this.Unload += new EventHandler(Report_Default_Unload);       }       void Report_Default_Unload(object sender, EventArgs e)     {         // close the report connection         report.Close();         report.Dispose();             }

 

 

This will ensure that you are not wasting the resources by keeping the ReportDocument object alive even though it is not being used by the application.

 

Conclusion:

The integration of Crystal Reports with ASP.NET application allows the developer to view the data in a professional manner with less effort. In later article I will demonstrate some of the advanced features of the Crystal Reports.