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