Creating Crystal Reports Using Typed DataSet
By AzamSharp
Views: 6099

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);