Previous Topic  Next Topic

Lesson 8: Creating a Report That Contains a Subreport

Here we need to create a report that contains a subreport to show the order detail information, the related customers and employees information.

This lesson shows a report combined with another report. When adding report A into report B, the report A is referred to as a subreport, while report B is considered as a primary report. We will first create a page report that contains two report tabs - one is about orders detail information, and the other is about a tabular report with two charts of different types.

In this lesson, we take the first report tab in the page report as the primary report, and the second one as the subreport. So that we can insert the second report tab to the first one to show the sales total by orders in different order ID groups.

Follow the tasks below to finish creating the report:

• Task 1: Create a Page Report with Two Report Tabs
• Task 2: Insert a Subreport to the Report

Task 1: Create a Page Report with Two Report Tabs

Before taking this task, make sure you have enabled the Insert field name label with field option in the Options dialog box as described at the last step of Lesson 3. Otherwise, the name labels will not be inserted together with the fields when you add fields to the report.

1. In Designer, select File > New > Page Report.
2. In the Select Component for Page Report dialog box, select Banded and select OK.

3. Be sure that JinfonetGourmetJava.cat is specified as the current catalog because this is the catalog we use in this track. For information about specifying this catalog, see Task 1, Step 2 and 3 of Lesson 1.

4. In the Data screen of the Banded Wizard dialog box, expand the Imported SQLs node in Data Source 1 and select OrdersReport.

   

5. Select Style to switch to the screen and select the Simple style for the report, then select Finish.

   A report with a blank standard banded object is created as follows. The panels in the banded object are identified on the left by their abbreviations: a banded header panel (BH), a banded page header panel (BPH), a detail panel (DT), a banded page footer panel (BPF), and a banded footer panel (BF).

   

If you would like to write your own SQL statement, you can edit it using the Imported SQL Editor dialog box or import the SQL statement from a file. SQLs can work like queries in Designer.

In this lesson, we need to group data by Order IDs, so we will add a group to the banded object.

5. Right-click the banded object and then select Banded Wizard from the shortcut menu.
6. In the Group screen of the Banded Wizard dialog box, add the DBField Orders_OrderID as the group-by field by selecting it and selecting , then select Finish.

   

7. Resize the GH panel of the banded object, drag the fields Customer_Name, Orders_OrderDate, Contact_LastName, and Orders_PaymentReceived from the Data panel and drop them into it.
8. For the Customer_Name, Orders_OrderDate, Contact_LastName, and Orders_PaymentReceived name labels, set their Auto Map Field Name property to false in the Text Format category of the Report Inspector. Then double-click and edit the name labels to Customer Name, Order Date, Employee Last Name, and Payment Received one by one.
9. Align the newly-added objects and resize them in the GH panel as follows:

   

10. Resize the group-by field in the GH panel, select Insert > Label to add a label ahead of it and edit its text to "Order ID:".

    

11. Select the Order Date DBField and set its Format property to M/d/yyyy in the Report Inspector.

12. Select the BPH panel, select Insert > Label to add a label in it and edit its text to Order Details. The label will then be shown in every page as the report title.
13. Hide the BH, DT, GF, BPF, and BF panels one by one by right-clicking the panel and selecting Hide from the shortcut menu.
14. Format labels and DBFields in the report to improve their appearance. Here, we will not describe the formatting process in detail, you can refer to Task 3 of Lesson 1 for assistance.

    Then the first report tab in the report is created to show the order detail information as follows in design mode:

    

    And the report looks as follows in view mode:

    

15. On the report tab bar, right-click the report tab and select Rename to rename it to OrderDetailInformation.

    

Next, we will create the second report tab in the report.

16. Select File > New > Page Report Tab.
17. In the Select Component for Page Report Tab dialog box, select Tabular from the layout box, then select OK.
18. In the Tabular Wizard dialog box, keep the default settings and select Finish. A report tab with a blank 2*2 tabular is created.
19. Merge the tabular cells into two rows by selecting two cells in a row while holding the Ctrl key on the keyboard, right-clicking on them and selecting Merge from the shortcut menu, then adjust them in order to place the bar chart and pie chart vertically in the report tab.

Next, we will insert the two charts into the tabular cells and have them share the same dataset as the OrdersDetailInformation report tab.

20. Select the first tabular cell and select Insert > Chart on the menu bar. Designer displays the Create Chart dialog box.
21. In the Data screen, select the More Options button, select the Existing Dataset radio button, then select the dataset OrdersReport. Select Next.
22. In the Type screen, keep the default chart type Clustered Bar 2-D and select Next.
23. In the Display screen, select the summary Sum_SQLProductSalesbyOrderID and select beside the Show Values box, Orders_OrderID is then added automatically to the Category box.
24. Select Style the switch to the screen and select Basic from the Style list. Select Finish to create the chart.
25. When a box attached to your mouse pointer, select the tabular cell to place the chart there.

    

26. Select the second tabular cell and insert a clustered pie chart in it which uses the same dataset and displays the same data in the same style with the bar chart.

27. Double-click any bar of the chart. In the Formar Bar dialog box, switch to the Fill tab and select the Vary Colors by Color List check box to make each bar filled with a different color schema, then select OK to apply the setting and close the dialog box. The chart shows as follows:



28. Right-click the bar chart and select Add Label from the shortcut menu. A label is then added to the chart.

    

29. Move the label to the top of the chart, then double-click it to display the Format Label dialog box.
30. In the General tab, change the label text to Current Orders for This Customer.
31. Switch to the Font tab, change Font Size to 12pt, Font Color to 808080 and select OK.

    

32. Resize the label to make sure the text is displayed completely.
33. Add a label to the pie chart and format it using the same way and edit its text to Current Orders for This Employee.
34. In the Report Inspector, select the Chart Object and Chart Object 1 nodes from the report structure tree by holding the Ctrl key on the keyboard, then change the Border Type properties to none.

    

35. Select the Chart Legend Object and Chart Legend Object 1 nodes and change the Border Type property to None as well.
36. Further format the two charts to improve their appearance if desired. For more information about formatting chart, see Task 2 of Lesson 4.

    Now the second report tab in the report is created to show the sales total by orders.

    

37. On the report tab bar, right-click the report tab containing the charts to rename it to Sub_OrderDetails.
38. Select File > Save to save the report as OrdersReport.cls.

Task 2: Insert a Subreport to the Report

After creating the report, we can insert the subreport to the primary report by specifying relationships between them. In this lesson, we can specify different relationships between the primary report (the first one) and the two chart components in the second report tab by setting up different links between them. Then Designer will build a subreport for every order with the conditions, so that it is convenient to see the corresponding customer and employee information of every order.

We want this subreport to be at the end of the existing reports, thus we need to add a second group header and add the subreport to the new header. 

1. Select OrderDetailInformation on the report tab bar to switch to the report tab.

   

2. Right-click in the GH panel of the banded object and choose Insert Panel After from the shortcut menu.
3. Select the newly-added panel and select Insert > Subreport.
4. When Designer attaches a box to the mouse pointer, select the newly-added panel to place the subreport into it. Designer displays the Subreport dialog box.
5. Select the Browse button, select OrdersReport.cls and then select Open.
6. Select Sub_OrderDetails from the Report Tab drop-down list.
7. Select the Add button beside the Component in Report Tab box in the Field tab, choose Chart Object in the Choose Component dialog box, then select OK.

   

8. Build the relationship between the primary report and the selected chart object on the Orders_CustomerID field by selecting the field and selecting .
   

   

8. Select the Add button again, this time choose Chart Object 1 in the Choose Component dialog box, then select OK.
9. Build the relationship between the primary report and the second chart object on the Orders_EmployeeID field.
10. Select OK in the Subreport dialog box and the report tab will be inserted in the OrderDetails report tab as follows:

    

Next, we will filter the dataset used by report tabs in the report so as to enhance the performance when running the report.

12. Select Report > Manage Datasets to display the Manage Datasets dialog box.

    The Manage Datasets dialog box lists all the dataset used in the current report. In our report, all the components share the same dataset, so here only one dataset is shown.

13. Select the Filter tab, select the Add Condition button to add a filter line, select ORDERS_ORDERID from the field drop-down list, <= from the operator drop-down list, and type 3050 in the value text box to specify the filter condition as ORDERS_ORDERID <= 3050. Select OK to apply the filter.

    

14. Select File > Save to save the report.
15. Select the View tab, Designer displays the report tab that contains a subreport as follows: the primary report shows the orders detail information grouped by order ID, and the subreport shows the orders information by different customers and different employees respectively in the two charts.

    

    If the report does not look correct, you can compare it to the final version of the report included in Designer. To do this, save and close your current catalog, and then open the JinfonetGourmetJava.cat catalog file located at <install_root>\Demo\Reports\TutorialReports.

As you learned in this lesson, it is very convenient to build up a library of reports that act as components which you can add to various primary reports. Properly designed and used, subreports save a lot of time and effort in rapidly adapting your applications to changing user requirements and needs.

Previous Topic  Next Topic