Creating Sub-Reports Using Crystal Reports
page 2 of 4
by Mohammad Azam
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 45624/ 49

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."


View Entire Article

User Comments

Title: 2012 NFL jerseys   
Name: NIKE NFL jerseys
Date: 2012-05-20 11:39:12 PM
Comment:
[/pre]Cheap NFL,NBA,MLB,NHL
[url=http://www.jersey2shop.com/]Jerseys From China[/url]
[url=http://www.jersey2shop.com/]2012 nike nfl Jerseys[/url]
[url=http://www.jersey2shop.com/]cheap China Jerseys[/url]
[url=http://www.jersey2shop.com/]Sports Jerseys China[/url]
[url=http://www.jersey2shop.com/NFL-Jerseys-c68/]NFL Jerseys China[/url]
[url=http://www.jersey2shop.com/NBA-Jerseys-c77/]NBA Jerseys China[/url]
NHL Jerseys China
[url=http://www.jersey2shop.com/MLB-Jerseys-c94/]MLB Jerseys China[/url]NFL jerseys For Sale online.All Our Jerseys Are Sewn On and Directly From Chinese Jerseys Factory
[/pre]
[pre]We Are Professional China jerseys Wholesaler
[url=http://www.cheapjersey2store.com/]Wholesale cheap jerseys[/url]Cheap mlb jerseys
[url= http://www.cheapjersey2store.com/]2012 mlb all atar jerseys[/url]
[url= http://www.cheapjersey2store.com/ [/url]Cheap China Wholesael[/url]
[url= http://www.cheapjersey2store.com/]Wholesale jerseys From China[/url]
[url=http://www.cheapjersey2store.com/]2012 nike nfl Jerseys[/url]Free Shipping,Cheap Price,7 Days Deliver
[/pre]
[/pre]
We are professional jerseys manufacturer from china,wholesal
sports [url= http://www.cheapjersey2store.com/]Jerseys From China[/url]
[url=http://www.cheapjersey2store.com/NFL-Jerseys-c68]NFL jerseys China[/url]
[url=http://www.cheapjersey2store.com/NHL-Jerseys-c96/]NHL Jerseys China[/url]
[url=http://www.cheapjersey2store.com/NBA-Jerseys-c77/]NBA Jerseys China[/url]
[url=http://www.cheapjersey2store.com/MLB-Jerseys-c94/]MLB Jerseys China[/url]
[url= http://www.cheapjersey2store.com/]China Jerseys[/url],Free Shipping
[/pre]
[/pre]
We are professional jerseys manufacturer from china,wholesal
sports [url= http://www.jerseycaptain.com/]cheap jerseys sale online [/url]
[url= http://www.jerseycaptain.com/]2012 nike nfl Jerseys[/url]
[url=http://www.jerseycaptain.com/NFL-Jerseys-c68]cheap NFL jerseys China[/url]
[url=http://www.jerseycaptain.com/NHL-Jerseys-c96/]NHL Jerseys C
Title: Picture not displayed   
Name: pppp
Date: 2012-03-15 9:07:37 AM
Comment:
please make the pictures to be opened in any environment...
Title: subreports in crystal report?   
Name: Rajan
Date: 2012-02-23 7:26:21 AM
Comment:
I have created a Report and i want to add one to one subreport ,and in subreport i want another subreport
upto what extend i can keep calling the sub reports in reports

Please suggest some idea and if possible provide some articles..

Thanks in advance
Title: Nice one   
Name: H.Bilal Tariq
Date: 2011-02-11 1:34:09 AM
Comment:
This article is very useful like your other one. May you please explain a bit more that if we use dataset as data source then how can we handle subreport in CS file?

Please respond me on aspsage@yahoo.com
Title: VERY GOOD   
Name: BHUP
Date: 2010-09-28 5:55:01 AM
Comment:
Nice work
Title: Very Nice   
Name: keerthi
Date: 2010-09-02 7:52:38 AM
Comment:
Hi Mr Mohammad Azam,I am keerthi, i read ur article, its very help full to me.... because i am a beginer for crystal reports..............
Title: Mr.   
Name: Raghavendra
Date: 2010-08-19 5:36:23 AM
Comment:
Hi, this is what i am following to generate the reports and subreport. but still i have a problem with ub report, i am using ondemand subreport but when i click on the field sub report is not generated, can someone help me on this. how to make it work
Title: Great Work   
Name: Atif Shah
Date: 2010-07-31 10:10:51 AM
Comment:
Thanks a lot for such a nice post.
Title: Thank You   
Name: Lakshmi
Date: 2010-04-19 4:10:31 PM
Comment:
Thanks for detail information
Title: Gr8 work   
Name: Ashish
Date: 2009-12-31 12:53:46 AM
Comment:
You hv done a great work..!!
Title: Very Good   
Name: MG
Date: 2009-12-08 12:02:18 PM
Comment:
Very neatly explained...Very easy to understand the steps and reasons. Thanks!!
Title: Thank you!   
Name: Lisa
Date: 2009-11-23 11:31:19 AM
Comment:
I have been looking for help with this for a while - your steps were so easy to understand. Thanks!!
Title: Impressed   
Name: Salil
Date: 2009-10-27 12:55:04 PM
Comment:
you rock..thanks a lot..
Title: Thanks   
Name: Krishna
Date: 2009-10-05 4:00:37 AM
Comment:
Amazing....Thanks a lot for your help in understanding sub reporting in crystal reports.

Can you let me know how we are supposed to do the same withput using "ReportSource" and by using code in the Script..

My EMail ID is kkumar181286@gmail.com

Thanks a million...
Title: Subreport Printing   
Name: Pankaj Rai
Date: 2009-09-24 2:35:18 AM
Comment:
Hi,
I am creating subreport using dataset .It's working fine but.When I click on print page will went to main report.
What I have to do .Please reply ASAP.


Thanks in Advance
Title: SUBREPORTS   
Name: Steve
Date: 2009-09-01 9:53:30 AM
Comment:
Hi I am creating a wrapper to house 9 sub-reports (because they have to print in order and the sql code is copywritted), problem is that i will be passing parameters to the rpt,from a user-interface and I am having issues figuring out how to code it in crystal. Could you please help??
Title: As usual Great article for guyz like me   
Name: Jay
Date: 2009-06-07 2:29:35 PM
Comment:
One nice thing about Azam is, just follow his doc you will become good developer. i like his comments and screen shots. Thans a lot Azam.
Title: subreport   
Name: Thanasekaran
Date: 2009-05-04 5:45:24 AM
Comment:
Hi
I am creating a sub report and it's working fine
the problem is in run time the curser move to sub report it's show the hand Symbol and if it's click that time main report are hiden
how to control this problem
pls help
Title: Dr   
Name: Desouki
Date: 2009-05-02 6:14:55 AM
Comment:
very useful article, thanks
Title: Crystal SubReport   
Name: madhu
Date: 2009-04-20 5:01:59 AM
Comment:
Sir

I am using crystal 11 and i hv created a main report and want to insert 2-3 sub reports but data is not comming in sub reports, pl help me.
Title: Crystal SubReport   
Name: Ravi
Date: 2009-03-20 9:02:29 PM
Comment:
Sir

I am using crystal 11 and i hv created a main report and want to insert 2-3 sub reports but data is not comming in sub reports, pl help me.
Title: Sub Reportimg   
Name: Prashanth A
Date: 2009-02-03 1:13:55 AM
Comment:
This article is great!!!!!!!!! and gives all the information in a very understandable format thanks for such a great article .
keep up the good work
Title: Subreporting   
Name: Gunjan Singh
Date: 2008-12-31 5:21:35 AM
Comment:
This aricle is very useful sir.
I got good concept from this
Thanks
Title: subreport   
Name: jaipalreddy
Date: 2008-11-18 5:50:12 AM
Comment:
this artical is super......and gives all the information in a very understandable format thanks for such a great article .keep up the good work.
Title: Sub reports article on CR   
Name: prashanti t
Date: 2008-10-21 1:30:26 PM
Comment:
this article is great!!!!!!!!! and gives all the information in a very understandable format thanks for such a great article .keep up the good work.
Title: Sub reports article on CR   
Name: Colin Gray
Date: 2008-08-12 5:50:43 PM
Comment:
Hi Mohammad,

Thanks for the article on sub reports. They're often great to use and can get report developers out of sticky positions. I would be careful about showing subreports for situations that can be address with groupings though... Sub Reports, especially dropped into detailed sections, can have a significant impact on performance. Many developers use them to mash data together where links in the database don't exist or to position the data more with more flexibility. Just thought I would comment.

cheers

Colin Gray
Developer Marketing Manager
Business Objects, an SAP company.
Title: nice information   
Name: anver
Date: 2008-07-25 6:20:26 AM
Comment:
thnx azam to share such avaluable stuff of ctystal report with us.
continue ur sharing and expand knowledge for us in future.
have a nice future

Product Spotlight
Product Spotlight 



Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-29 5:19:49 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search