LogoASPAlliance: Articles, reviews, and samples for .NET Developers
Creating Sub-Reports Using Crystal Reports
by Mohammad Azam
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 64170/ 162


Reports allow the user to view the complex data in a readable format. The data can be stored in a variety of places and can be pulled out by the reporting tool to generate a user friendly report. In this article we will demonstrate how to create sub reports using Crystal Reports.

Database Design

Our database "EasyShopping" consists of two tables: "Categories" and "Products." The Categories table contains all the different categories available to the user. And the Products table contains all the products related to different categories. Check out the database diagram for both the tables in the database.

Figure 1

Understanding the Hierarchical Data Display

Let us first understand what we meant by hierarchical data display. As you can see from the database diagram, the "CategoryID" is a foreign key in the "Products" table. This means that a single category can have multiple products. In other words, it is a One-to-Many relationship. Our task is to display a category name along with its products. This process will be repeated for every category in the database. Here is the diagram that explains the flow of the report.

Figure 2

Now it is time to start designing the report.

Designing the Report

The first task is to add a Crystal Report to your project. Right click on your project and select "Add New Item." Click on the Reporting category and select "Crystal Report." Check out the image below to get a better idea.

Figure 3

We are naming our report as "CategoryReport." Click the "Add" button and you will see the following screen.

Figure 4

Select the "Using the Report Wizard Option" and click "OK." The next screen will ask to configure the data source for the reports. Since our data is contained in the SQL SERVER database, we will create a new connection as shown below.

Figure 5

Double click on the "Make New Connection" and the following screen will pop up.

Figure 6

Select "Microsoft OLE DB Provider for SQL SERVER" as shown above and click the "Next" button. The next screen will allow you to connect to a database. Check out the screen shot below.

Figure 7


The Integrated Security is checked because the SQL SERVER is hosted on the same machine from where the application is running. Click Next to continue to the advanced OLE DB information screen as shown below.

Figure 8

Click finish and you will see figure 3 showing the connection established. Drill down to the "EasyShopping" database and add the "Categories Products" table to the selected tables by clicking the ">" button.

Figure 9

Click "Next>" and you will be taken to the "Link" screen which allows you to link different tables together.

Figure 10

We are not going to link any tables in this example so you can simply press the "Next>" button to move forward. The next screen is the "Field Selection Screen" which allows you to select the fields you want to display in a report. Select the "CategoryName" field from the options on the left and press the ">" button to add it to the selected fields to the right.

Figure 11

Press "Next>" to move to the Grouping Screen. We will not include any group for this demo.

Figure 12

Press "Next>" to move to the "Record Selection" screen. We will not be selecting any records so you can bypass that screen.

Figure 13

Click "Next>" to move to the "Report Style" screen. You can select any style you want, but for this demo we will be using the "Red/Blue Border" style.

Figure 14

Click the "Finish" button to finally end the wizard steps. Click on the "Main Report Preview" button at the bottom of the screen to see the preview of the generated report.

Figure 15

As you can see from the screenshot, the report is displaying the "CategoryName." We are half way done. Now we need to add a sub-report that will display all the products in each category.

Adding Sub-Report

The next task is to add a sub-report to the current report. The sub-report will enable the display for the products of each category. To add a sub-report right click on the current report's "Details" section and select "Insert -> Section" as shown in the screenshot below.

Figure 16

In the "Insert Section" window select "Details" and click the "Insert" button at the top of the screen.

Figure 17

This will add a separate "Details" section as shown below.

Figure 18

Click on the "OK" button to finish adding the section. When you are finished you will see a new section added to the report.

Figure 19



Now, you need to add a sub-report inside the "DetailSection2(Details b)." Right click inside the Details b section and select "Insert -> Sub-Report" as shown in the screenshot below.

Figure 20

When you select the sub-report option it will create a rectangle where the sub report will be displayed. Simply place the sub-report rectangle inside the "Details b" section. This will open an insert Sub-Report window.

Figure 21

Then click on the "Report Wizard" button and select the "Products" table from the right panel. Move the "Products" table to the left panel by using the ">" button.

Figure 22

Click "Next>" to continue. The next screen will allow you to select the "Fields" that you want to display on the report. We are going to select "ProductName" as shown in the screenshot below.

Figure 23

Click "Finish" to end the wizard. The "Finish" button will lead to the "Insert SubReport" window. Select the "Link" tab to display the link screen. The link screen will allow you to set the parameters that will be used to fetch the sub report.

Figure 24

The sub-report accepts a parameter named "CategoryID" from the master report. This will allow us to create the parent-child relationship between the data. When you preview the above report you will see the following output.

Figure 25

The above screenshot shows the hierarchical relationship between the "Category" and its "Products."

Displaying Report on a Webpage

The last step is to display the report on the webpage. This is accomplished by using the CrystalReportViewer and CrystalReportSource control. The implementation is shown below.

Listing 1

<CR:CrystalReportViewer ID="CrystalReportViewer1"  runat="server" 
AutoDataBind="true" ReportSourceID="CrystalReportSource1" />
<CR:CrystalReportSource ID="CrystalReportSource1"  runat="server">
<Report FileName="CategoryReport.rpt">

The CrystalReportSource control fetches the report by using the Report FileName attribute. The report is displayed on the webpage by the CrystalReportView control. Take a look at the final output that is displayed on the webpage.

Figure 26


In this article we learned how to display hierarchical data using Crystal Reports. As you saw, most of the operations associated with building a report involved less coding and more drag, drop things. This benefits the developer by allowing him or her to concentrate on the outer design rather then the inner workings.


[Download Source]

Product Spotlight
Product Spotlight 

©Copyright 1998-2021  |  Page Processed at 2021-03-05 8:12:23 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search