Lesson 7: Creating a Tabular Report
This topic introduces how to create a Tabular Report.
A report about performance of products is needed, in which the product ID is less than or equals to 10. The report includes annual sales of every product in every region, their unit price, the quantities that are sold, and the total of their annual sales of every product. The report should contain a crosstab, which is to represent annual sales of ten products in every region, a chart object, which is to represent quantities and total annual sales of these specified products, and a banded object, which is to represent the unit price and quantities.
Here is a sketch of the report that you can refer to:
In this lesson, we will create a tabular report and insert crosstab object, chart object and banded object respectively into different tabular cells, so that components in the report can be arranged easily with the tabular layout. Meanwhile, all the data resources we need for creating the report have been predefined in the JinfonetGourmetJava.cat catalog file, so in this lesson, we do not need to create them ourselves.
Follow the tasks below to finish creating the report:
- Task 1: Create the Tabular Report
- Task 2: Add Components to the Tabular Report
- Task 3: Format the Report
- In Logi JReport Designer select File > New > Page Report.
- In the Select Component for Page Report dialog, select Tabular and select OK.
Be sure that JinfonetGourmetJava.cat is specified as the current catalog because it is the catalog we use in this track. For information about specifying this catalog, see Task 1, Step 2 and 3 of Lesson 1.
- In the Tabular Wizard, keep the default settings and select Finish to create a 2*2 tabular.
- A message dialog appears prompting you to drag and drop data fields and components to the blank report. Select OK in the message dialog to close it.
Now a blank report with a 2*2 tabular is generated. In the Report Inspector, they are respectively named TabularCell and TabularCell1 (the two cells in the first row of the tabular); TabularCell2 and TabularCell3 (the two cells in the second row of the tabular). We can then insert different components into the cells, so that they can be easily aligned just by adjusting the tabular cells.
The tabular fills the whole page panel, we can resize it by just dragging its cell borders. In this lesson, in order to make screen captures, we zoom out the tabular first.
Before taking this task, make sure you have enabled the Insert field name label with field option in the Options dialog 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.
In this task, we will add three components - a crosstab, a chart and a banded object respectively to TabularCell, TabularCell2 and TabularCell3 and make them share the same dataset so as to improve performance.
- Select the two cells in the first row of the tabular (TabularCell and TabularCell1) by holding the Ctrl key on the keyboard, right-click and then select Merge from the shortcut menu.
- Select the merged tabular cell and select Insert > Crosstab. The Create Crosstab wizard appears.
- In the Data screen of the wizard, select the query ProductPerformance from the Queries node of Data Source 1, then select Next.
- In the Display screen, add the Products_Product ID DBField to the Columns box by selecting it and selecting , add the Region_AbbreviationName formula to the Rows box by selecting it and selecting , add the Total formula to the Summaries box by selecting it and selecting , and then double-click in the Aggregate text box and select Sum from the drop-down list as the aggregate function of the summary.
- Select Style to switch to the screen and apply the Classic style to the crosstab, then select Finish to close the Create Crosstab wizard and place the crosstab in the first tabular cell row.
Next, we will insert the chart object to TabularCell2:
- Select the left cell in the second row of the tabular (TabularCell2) and select Insert > Chart. The Create Chart wizard appears.
- In the Data screen of the wizard, select the More Options button, then check the Existing Dataset radio button and select the dataset ProductPerformance. Select Next.
- In the Type screen, select the Bubble in the Chart Type box and select Next.
- In the Display screen, select the Y Axis node in the Show Values box, drag and drop the summary Count_QuantitybyProductID under it, then select the Radius node and add Sum_ProductSalesbyProductID under it. The summaries are grouped by the DBField ProductID_FK1 so the field is added to the Category box automatically. Leave the Series box empty.
- Select Layout to switch to the screen, select Title in the Options box, then specify Category (X) Axis Title as Product ID, and Value (Y) Axis Title as Quantity. Select Next.
- In the Style screen, select Classic from the Style list. Select Finish to close the Create Chart wizard and drop the chart in TabularCell2.
Next, we will insert the banded object to TabularCell3:
- Select TabularCell3 (the right cell in the second row of the tabular), and then select Insert > Banded Object. The Create Banded Object wizard appears.
- In the Data screen of the wizard, select the More Options button, then check the Existing Dataset radio button and select the dataset ProductPerformance.
- Select Group to switch to the screen, select the DBField ProductID_FK1 and select to add it as the group by field.
- Go to the Style screen and select the Classic style for the banded object. Select Finish to close the Create Banded Object wizard and then select in TabularCell3 to place the banded object in it. The panels in the banded object are identified on the left side by their abbreviations: a banded header panel (BH), a banded page header panel (BPH), a group header panel (GH), a detail panel (DT), a group footer panel (GF), a banded page footer panel (BPF) and a banded footer panel (BF).
Now, the three objects look as follows in the tabular:
- Drag from the Basic group in the Components panel to the BPH panel in the banded object, above the group by field in the GH panel to label the field, then edit the label text to Product ID.
Drag the DBFields Unit Price and the summary Count_QuantitybyProduct ID from the Data panel and drop them in the GH panel.
- Adjust the added field and summary, move their name labels to the BPH panel, and edit the name label Count_QuantitybyProduct ID to Quantity.
- Right-click in the BH panel and select Hide from the shortcut menu to hide it from view.
Repeat this to hide all panels
that do not hold data.
Now the report shows as follows:
Next, we need a report title for the tabular report to make the report look professional.
- Right-click the tabular cell that holds the crosstab, and select Insert Row Above from the shortcut menu, then resize the row to a suitable height.
- Double-click the newly-created tabular row, and enter the text Product Performance by Product ID as the report title.
As required, the report only needs to show the products performance from the Product ID 1 to 10, so next we will use the Dataset Filter feature to limit data selected from the dataset. Since all the components in the report share the same dataset, the filter will be applied to all of them.
- Select the Dataset Filter button on the toolbar of the Data panel.
- In the Dataset Filter dialog, select the Add Condition button to add a filter line, select PRODUCTID_FK1 from the field drop-down list, <= from the operator drop-down list, and enter 10 in the value text field to specify the filter condition as PRODUCTID_FK1 <= 10, then select OK.
To improve the report appearance, we need to do some adjustments to the report, as well as the components in it:
- Resize the tabular cells according to the components' sizes in every cell, so that they will not be truncated by the tabular and can display well.
Next, we will further format to improve the appearances of the components. First, we will format the report title and the crosstab.
- Select the text Product Performance by Product ID, right-click on it and select Font from the shortcut menu.
- In the Font tab of the Format Text dialog, set Font to Arial, Font Size to 18, Font Color to Red and Bold to true, then select OK.
The title appears as follows:
- Select the four DBFields in the summary area of the crosstab, then in the Report Inspector, specify the Height property to 0.25, Width to 0.59, and Format to #,###.#.
Next, let's format the chart.
- Double-click the label Quantity in the chart, then in Format Label dialog, switch to the Font tab, set Font Size to 10pt and Font Color to 808080. Select OK to accept the settings.
- Use the same way to format the label Product ID in the chart.
- Right-click the chart and then select Hide Legend on the shortcut menu to hide the legend.
Then, we will format the banded object.
- Select the Product ID name label in the BPH panel and its corresponding field in the GH panel by holding the Ctrl key on the keyboard, then select Format > Right to make them aligned the same as other labels and fields in the banded object.
- Select the three DBFields in the GH panel by holding the Ctrl key on the keyboard, then in the Report Inspector set their Foreground property to Black and Bold to false.
- Select the Unit Price DBField and then specify its Format property to $#,###.00 in the Report Inspector.
After doing the adjustments, the report appears somewhat like follows in design view:
According to the sketch, a line needs to be added below the report title. We can do this just by setting tabular cell properties.
- Select the tabular cell holding the report title, then in the Report Inspector, specify its Top Line, Left Line and Right Line properties to none, Border Color to Gray and Border Thickness to 0.01.
When viewing the report, the crosstab object displays much closer to the line we just customized and the other two components look too clumsy. We need to do some adjustments, which can be done easily by using the function of a tabular.
- Right-click the tabular cell holding the crosstab, and then select Insert Row Above from the shortcut menu. Right-click the cell again and select Insert Row Below.
Two rows are now added above and below the crosstab.
- Resize the tabular rows to adjust the distances between the crosstab and the other components.
- On the report tab bar, right-click the report tab and select Rename to rename it to ProductPerformance.
- Select File > Save to save the report as ProductPerformancebyProductID.cls.
- Select the View tab, and the report appears as follows, depending on what formatting you have done to the components.
Note: If the report does not look correct, you can compare it to the final version of the report provided by Logi JReport. To do so, you will need to save and close this catalog and then open the JinfonetGourmetJava.cat catalog file located at