Click here to Skip to main content
15,860,943 members
Articles / Web Development / ASP.NET

Step by Step Creation of Crystal Report using its Features Group, Graph, Cross-Tab and Sub Report

Rate me:
Please Sign up or sign in to vote.
4.85/5 (112 votes)
20 Jan 2011CPOL9 min read 701.9K   23.4K   209   93
This article will help beginners to work with Crystal reports features easily.

Introduction

This article will help you to create reports using Crystal Reports. Here I am giving a step by step idea to create reports in an easy way. Here I have covered Crystal reports features such as Grouping, Graph, Cross-Tap, Sub report, etc.

I am going to explain the below 5 features:

  1. Simple Crystal Report
  2. Group in Crystal Report
  3. Graph in Crystal Report
  4. Cross-Tab in Crystal Report
  5. Sub report in Crystal Report

Using the Code

Let’s start by creating a new ASP.NET Web Site. Figure 1 shows the initial web site creation screen.

fig1.PNG

Figure 1

By clicking OK button, you can see the project created in solution explorer. Now let’s start with Crystal Report.

Before that, I am going to show my table structure and data in table. So this will help you to understand the steps easily. Figure 2 will show the table structure and Figure 3 will show the data in table.

fig2.PNG

Figure 2

fig3.PNG

Figure 3

But in the attached project, I am getting data from the XML file. I have saved all the data from table to XML. So you can easily use the attached project. Next Dataset (xsd file) creation.

There are so many ways to pass the data to Crystal Report. Here I am using Dataset (xsd file) to bind Crystal Report. In my point of view, this is the best way to bind. So, next step is Dataset (xsd file) creation.

Go to Solution Explorer -> Right click on the Project -> Add New Item. Figure 4 shows the Dataset creation.

fig4.PNG

Figure 4

Once you click on Add button, it will ask one confirmation message. Just click yes. Now you can see the dsTestCrystal.xsd dataset file opened in Visual Studio. Here you need to create one Data Table with all the columns names you need to shown in Crystal Report. To Create Data Table, see the below Figure 5.

fig5.PNG

Figure 5

Now you can see Data Table created in the Dataset. Next, we need to add the columns names need to shown in Crystal Report. Please note that this Data Table column names and data types should be the same as your table in database. If you are getting any data type property mismatching error, just think that this is the problem. To Create Column names, see the below Figure 6.

fig6.PNG

Figure 6

Add all the columns one by one and set the correct data type. Here by default, data type is string. If any columns have different data type, we need to change manually. In this project, I am using ‘Marks’ column data type as int. So to change the data type, see the below Figure 7.

fig7.PNG

Figure 7

Dataset (dsTestCrystal.xsd) creation has been done. Now we need to design Crystal Report.

1. Simple Crystal Report

Go to Solution Explorer -> Right click on the Project -> Add New Item. Figure 8 shows the Dataset creation.

fig8.PNG

Figure 8

On click of Add button, you will get Crystal Report Gallery window. In that, select Using the Report Wizard check box and Click OK button. Now you will get the below Figure 9.

fig9.PNG

Figure 9

Now expand the ADO.NET Datasets folder. There you can see the Dataset (dsTestCrystal) we created. Under dsTestCrystal, you can see the Data Table we created. So select this Data Table and click the arrow button to move to the Selected Tables list box as shown in the below Figure 10.

fig90.PNG

Figure 10

Just click the Finish button. Now you can see the Crystal Report opened in Visual Studio. In your left side, you can see Field Explorer. In case you cannot see it there, just go to menu Crystal Reports -> Field Explorer. Field Explorer View is shown in below Figure 11.

fig91.PNG

Figure 11

Now you can drag and drop the columns from Field Explorer to Details Section in Crystal Report. You can see the final view of the Crystal Report in the next Figure 12.

fig92.PNG

Figure 12

Crystal Report design also done. Now we need to pass the data to the Dataset (dsTestCrystal) and we need to set this Dataset to Crystal Report.

Now just create one aspx page. Add the below code in .aspx page.

ASP.NET
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server"
DisplayGroupTree="False"  />   

Then write the below code in .aspx.vb page to generate Crystal report.

VB.NET
Dim rptDoc As New ReportDocument
Dim ds As New dsTestCrystal
Dim sqlCon As SqlConnection
Dim dt As New DataTable
dt.TableName = "Crystal Report Example"
sqlCon = New SqlConnection("server='servername';
Initial Catalog='databasename';user id='userid';password='password'")
Dim da As New SqlDataAdapter("select Stud_Name, Class, 
Subject, Marks from stud_details", sqlCon)
da.Fill(dt)
ds.Tables(0).Merge(dt)
rptDoc.Load(Server.MapPath("SimpleCrystal.rpt"))
rptDoc.SetDataSource(ds)
CrystalReportViewer1.ReportSource = rptDoc 

Now run your page and the data generated in Crystal Report.

2. Crystal Report using Grouping

Here I am going to explain only the Crystal Report design. Remaining things you can refer from the previous one.

I am going to use the Group like Class -> Name -> Subect & Marks. Here Class and Name is Group. Subject and Marks will come in detail section. To create Group, see the below Figure 13 & 14.

fig93.PNG

Figure 13

fig94.PNG

Figure 14

Class is the main group. So first we need to move Class field to Group By list box by clicking the arrow button. Then we need to move the Stud_Name field. See the below Figure 15.

fig95.PNG

Figure 15

Now click OK button and see the difference in Crystal Report. There you can see two Header & Footer added and two fields Group #1 Name, Group #2 Name. Group #1 Name is Class and Group #2 Name is Stud_name. See the below Figure 16.

fig96.PNG

Figure 16

Now you can drag and drop the columns from Field Explorer to Details Section in Crystal Report. You can see the final view of the Crystal Report in the next Figure 17.

fig97.PNG

Figure 17

Now I am going to add sum of marks for the student. In Stud_Name GroupFooterSection, we need to add this sum. See the below Figure 18.

fig98.PNG

Figure 18

On click of Summary you will get the below Figure 19.

fig99.PNG

Figure 19

We need the sum of Marks column. So select Marks field from first combo box. Then select Sum from the second combo box. Finally, we need sum of marks for each student. So we need to select Stud_Name group from third combo box. Then click OK button. Now you can see the sum field added in Stus_Name footer section. See the Final view of Crystal Report in the below Figure 20.

fig100.PNG

Figure 20

3. Chart in Crystal Report

Here also, I am going to explain only the Chart design. Remaining steps are the same as the first one.

I am going to show the students total marks student wise. X-axis is Student names and Y-axis is Total Marks. Lets start the creation of chart. See the below Figure 21.

fig101.PNG

Figure 21

Once you click the Chart, you will get the below Figure 22. Here you can see number of chart formats. So you can select any one from here. I am selecting Bar from the options.

fig102.PNG

Figure 22

Now move to the second tab which is Data. See the below Figure 23.

fig103.PNG

Figure 23

As I said earlier, Stud_Name we are using in X-axis. So we need to move Stud_Name to first (on change of) list box. Then move Marks to the second list box (Show value(s)). By default, Sum will display there. If you want to change Sum to some other, you can change by clicking the Set Summary Operation button. Then click Ok button. You can see the chart created in Report Header section. See the Final view of Crystal Report in the below Figure 24.

fig104.PNG

Figure 24

Now Run the web site and see the chart. That’s all. It’s done.

4. Cross-Tab in Crystal Report

Here also, I am going to explain only the report design. Remaining steps are the same as the first one.

First, you need to know the reason why we need to use Cross-Tab. If you want to display the subject column wise, we need to use cross-Tab. Because subject can vary for different class, we cannot design report with fixed columns. So in this scenario, we can go for Cross-Tab. See the Cross-Tab report creation in below Figure 25.

Note: We can place Cross-Tab only in Report Header/Footer.

fig105.PNG

Figure 25

Once you click the Cross-tab, you can see the rectangle mouse pointer. So you need to place that only in Report Header/Footer. Once you place that, you will get the below Figure 26.

fig106.PNG

Figure 26

As I said in the earlier, we are going to put subject in column wise. So move Subject field to Columns listbox. Then before showing the marks of each subject, we need to show the Student name. So Move Stud_Name to Rows listbox. Finally move Marks to Summarized Fields listbox. Then click Ok button. You can see the report created in Report Header/Footer section. See the Final view of Cross-Tab Report in the below Figure 27.

fig107.PNG

Figure 27

Now Run the web site and see the report. That’s all. It’s done.

5. Sub Report in Crystal Report

Here also, I am going to explain only the report design. Remaining steps are the same as the first one.

Here, I am going to show the each students' marks in graph by using sub report. So, to use sub report, first I am going to create student group header. See the below Figure 28.

fig108.PNG

Figure 28

Then in group header section, we need to create sub report. To do this, follow the below Figure 29.

fig109.PNG

Figure 29

Then place the mouse in the group header section. Once you place the mouse cursor, you will get one popup window. See the below Figure 30.

fig110.PNG

Figure 30

Here check the third check box to create new sub report. Then type the sub report name. Then click the Report Wizard button to select data set. Select the dataset like the below Figure 31.

fig90.PNG

Figure 31

Then click the Finish button and click OK button in sub report window. Now follow the below Figure 32 to edit the sub report.

fig111.PNG

Figure 32

Now you will get the below Figure 33. Here you can create Graph by using 3rd section (Graph Creation). Here use Subject as X-axis and Marks as Y-axis. See the below Figure 33.

fig112.PNG

Figure 33

Now Run the web site and see the report. That’s all. It’s done.

I hope that this article will help you.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralRe: My vote of 5 Pin
Toniyo Jackson3-Mar-11 2:35
Toniyo Jackson3-Mar-11 2:35 
GeneralA good article Pin
caseyj110-Feb-11 17:19
caseyj110-Feb-11 17:19 
GeneralRe: A good article Pin
Toniyo Jackson15-Feb-11 0:24
Toniyo Jackson15-Feb-11 0:24 
GeneralMy vote of 5 Pin
Abhinav S9-Feb-11 3:05
Abhinav S9-Feb-11 3:05 
GeneralRe: My vote of 5 Pin
Toniyo Jackson15-Feb-11 0:23
Toniyo Jackson15-Feb-11 0:23 
GeneralMy Vote of 5 Pin
RaviRanjanKr8-Feb-11 18:58
professionalRaviRanjanKr8-Feb-11 18:58 
GeneralRe: My Vote of 5 Pin
Toniyo Jackson15-Feb-11 0:22
Toniyo Jackson15-Feb-11 0:22 
GeneralMy vote of 5 Pin
Richard Jones27-Jan-11 4:41
Richard Jones27-Jan-11 4:41 
GeneralRe: My vote of 5 Pin
Toniyo Jackson16-Mar-11 3:28
Toniyo Jackson16-Mar-11 3:28 
GeneralMy vote of 5 Pin
Ajeesh M J16-Jan-11 23:30
Ajeesh M J16-Jan-11 23:30 
GeneralRe: My vote of 5 Pin
Toniyo Jackson17-Jan-11 1:19
Toniyo Jackson17-Jan-11 1:19 
QuestionCrossTab report Pin
Aiood197216-Jan-11 20:49
Aiood197216-Jan-11 20:49 
GeneralVery Good Pin
farouk gaber16-Jan-11 7:26
farouk gaber16-Jan-11 7:26 
GeneralRe: Very Good Pin
Toniyo Jackson16-Mar-11 3:28
Toniyo Jackson16-Mar-11 3:28 
GeneralGood Pin
lingeswararaj.s13-Jan-11 17:41
lingeswararaj.s13-Jan-11 17:41 
Generalreally good one with step by step explanation Pin
KalyaniPatra10-Jan-11 19:32
KalyaniPatra10-Jan-11 19:32 
GeneralRe: really good one with step by step explanation Pin
Toniyo Jackson10-Jan-11 23:39
Toniyo Jackson10-Jan-11 23:39 
GeneralMy vote of 5 Pin
Anupama Roy8-Jan-11 17:47
Anupama Roy8-Jan-11 17:47 
GeneralRe: My vote of 5 Pin
Toniyo Jackson10-Jan-11 23:40
Toniyo Jackson10-Jan-11 23:40 
Generalvery well explained - have 5 Pin
Pranay Rana7-Jan-11 0:07
professionalPranay Rana7-Jan-11 0:07 
GeneralRe: very well explained - have 5 Pin
Toniyo Jackson10-Jan-11 23:40
Toniyo Jackson10-Jan-11 23:40 
GeneralMy vote of 5 Pin
Аslam Iqbal6-Jan-11 6:41
professionalАslam Iqbal6-Jan-11 6:41 
GeneralRe: My vote of 5 Pin
Toniyo Jackson10-Jan-11 23:40
Toniyo Jackson10-Jan-11 23:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.