Lesson 1: Creating a standard banded report

A report of monthly sales from 2010 to the present has been requested by the Vice President of Sales at Jinfonet Gourmet Java. In this report, totals for sales need to be broken down by each order and each month, as well as presented for a grand total of all months.

Here's a draft of the report the vice president has given to you:

You immediately recognize the repetitive, columnar data in the center of the report and associated subtotals as a candidate for a standard banded report. This type of report has horizontal "bands" or panels that are geared for either detailed record display or calculations, such as subtotals, that apply to a preceding group or the entire report.

In this lesson, we perform the following tasks:

Task 1: Create the initial report and query to populate it

In this task, the report wizard collects the necessary information and then creates the standard banded report as well as the OrderListbyDate query.

  1. Click Start > All Programs > JReport 13 Update 1 > Designer to start JReport Designer. The JReport Designer window and the Welcome to JReport Designer dialog appear. Close the welcome dialog.
  2. On the JReport Designer menu bar, click File > Catalog Management > Open Catalog to bring out the Open Catalog File dialog.
  1. Browse to select the JinfonetGourmetJava.cat catalog file in <install_root>\Demo\Reports\JinfonetGourmetJava, then click the Open button.
  2. Choose File > New > Page Report on the menu bar, or click the New Page Report button on the Standard toolbar.
  3. In the New Page Report dialog, clear the text in the Report Title text box, select Banded from the layout box, then click OK.

    In all lessons in this track, we will create the report titles in other ways, so in the New Page Report dialog, we need to clear the report title first, otherwise, a default report title will be added.

    A page report is a container which holds multiple report tabs. Report tabs in the same report can share the same parameters and dataset, and generally, have been designed for the same purpose, or related purposes. You can design, maintain and schedule them together. A page report file has a .cls extension.

  4. In the Data screen of the Banded Wizard, select the New radio button.
  5. Expand the Queries node in Data Source 1, click <Add Query...>, enter OrderListbyDate in the Input Query Name dialog and then click OK.

  6. In the Query Editor window, click Query > Add Table on the menu bar.
  7. In the Add Table dialog, select the tables Orders, Orders Detail, and Products and click the Add button to add the tables to the query, then click Done to close the dialog.

  8. Select all the columns in the three tables by selecting the * checkbox, then uncheck the Inventory and Reorder Level columns in the Products table:

By default, the auto join options for queries are enabled (you can find the options on the sub menu of Query > Auto Join in the Query Editor), which means tables will be automatically joined in queries based on the join criteria. So, the three tables are automatically joined because JReport Designer recognizes the matching rows in each table. You can modify the join if desired or customize a different join for set of tables. However, when you add tables to a query, if you exit the Add Table dialog by directly clicking the close button of the dialog instead of clicking the Done button, tables you add will not be automatically joined based on the auto join criteria.

JReport creates the SQL SELECT statement based on the columns, tables, and joins you specify. Click the SQL button to see the SQL SELECT statement if necessary.

  1. Click OK at the bottom of the Query Editor window to create the query.
  2. Click Next button in the Banded Wizard to show the Display screen. The Display screen determines which fields returned by the dataset are visible in the report.
  3. From the Resources box, add the following fields in the Products table to the right-hand box one by one: Product Type Name, Products_Product ID, Product Name and Category, then change the display name of Products_Product ID to Product ID.

  4. Click Next to display the Group screen. The Group screen specifies the grouping criteria to be applied to the selected records.

There are two levels of grouping in this report: first by Order Date and second by Orders_Order ID.

  1. From the Resources box, add the fields Order Date and Orders_Order ID in the Orders table as the group by fields one by one.

    You can choose the sorting sequence of the groups in the Sort column. Specify it in descending order (c,b,a), ascending order (a,b,c) or a special ordering criteria. By default the order is Ascend.

  2. Select For each month from the Special Function drop-down list for the Order Date group.

    By selecting the for each month special function, the records, of which the field values are in the same month, will be grouped together.

  3. Switch to the Style screen and select Simple from the Style list.

    By default, when you create a banded, table, crosstab or chart report via the report wizard, a default style Neutral will be applied to it. However, in this lesson, we want to customize the report style by ourselves, so here we select the Simple style which has very little formatting.

  4. Click Finish to create the report. The Summary, Chart, and Filter screens are skipped.

    The report with banded object is created. The panels in the banded object are identified on the left side by their abbreviations: a BandedHeader (BH) panel, a BandedPageHeader (BPH) panel, a Detail (DT) panel, a BandedPageFooter (BPF) panel, a BandedFooter (BF) panel, and two GroupHeader (GH) and GroupFooter (GF) panels for the two levels of grouping:

  5. Click the View tab to run the report and view it.

Task 2: Add summaries and a print date to the report

As it is described in the first paragraph of this lesson, you need to calculate the totals of sales per order and per month, and then the total of all months. These types of calculations are accomplished by defining a formula that performs the computation and then placing the formula in the group footer panel directly. Summary is a special type formula that uses the built-in Sum function of JReport. A print date is a predefined field that is calculated at runtime by JReport.

To add the summaries to the report, follow these steps:

  1. From the Resource View panel, drag the Total formula in the Formulas node and drop it next to the Category DBField. The label of the formula is then placed in the second GroupHeader panel as follows:

Next, we will create a dynamic summary based on the Total formula, so as to compute total of product sales by order, by month and also to calculate the grand total.

  1. In the Resource View panel, select <Add Summary...>.
  2. In the New Summary dialog, choose Sum from the Aggregate Function drop-down list, select the Total formula from the Resource panel and click to add it to the Summary On field, check the Dynamic Summary radio button and keep its default settings, then click OK.

  3. Enter Sum_ProductSales_DynamicSummary in the Summary Name dialog and click OK to create the summary.

Then when we insert this summary into a group, JReport will do the calculation based on the group. If the summary is inserted into the BandedHeader or BandedFooter panel, it will compute based on the whole banded report.

  1. Drag the summary Sum_ProductSales_DynamicSummary from the Resource View panel to both of the two GroupFooter panels as well as the BandedFooter panel:

  2. Resize the BandedPageHeader panel and then click Insert > Label to add a label in it.
  3. Resize the newly-added label and double-click it to edit its text to Order List by Date.

  4. Click Insert > Special Fields > Print Date and then place the special field in the BandedPageHeader Panel, next to the Order List by Date label.
  5. Insert the Print Time special field in the same way.

  6. Edit the text of the Print Date and Print Time labels to Date and Time, respectively.
  7. Adjust the position of the two groups by fields, add two labels ahead of them for identification and edit their text as Order Date and Order ID as follows:

Task 3: Fine tune the report layout

To make the report easier to read, we need to make some adjustments to the report layout.

  1. Resize the second GroupHeader panel. Select the Product Type Name, Products_Product ID, Product Name and Category name labels in the BandedPageHeader panel and move them to the second GroupHeader panel, so that they can be closer to the actual data they describe.
  2. Resize the Product Type Name, Product ID, Product Name, Category and Total name labels, as well as their corresponding DBFields, manually drag and align them as follows, so that it can be shown more clearly:

  3. Select the DBField Product ID in the Detail panel and its name label, then set their Horizontal Alignment property in the Report Inspector to left so as to improve the report layout.
  4. Select the five labels Product Type Name, Product ID, Product Name, Category and Total, then in the Report Inspector, set their properties Background and Foreground to Lightgray and White.
  5. Select the three summaries in the two GroupFooter panels and the BandedFooter panel and make them right aligned with the Total formula, then edit the summary labels to Total by Order, Total by Month and Grand Total.
  6. Select the Total by Order and Total by Month summaries, then change their Foreground property to Gray in the Report Inspector.
  7. Select the Grand Total summary in the BandedFooter panel, then change the Foreground property to Red.

  8. Select the Order ID group by field in the second GroupHeader panel and change its Foreground property to Red.
  9. Select the Date and Time special fields in the BandedPageHeader panel and the Order Date group by field in the first GroupHeader panel, then change their Foreground property to Gray.
  10. Resize the Order List by Date label, change its Font Size property to 18, and Foreground property to Red.
  11. Resize the Date and Time special fields and change their position in the BandedPageHeader panel to make them look tidy in the report.
  12. Select the two GroupFooter panels and the BandedFooter panel, then set their Background property to Transparent.
  13. Right-click the BandedHeader panel and select Hide from the shortcut menu to hide it from view. Repeat to hide the BandedPageFooter panel in the same way.

    After editing, the report looks somewhat like below in design view:

Next, we will give the report tab a meaningful name and save the report.

  1. On the report tab bar, right-click the report tab and select Rename from the shortcut menu.

  2. In the Input Report Tab Name dialog, enter OrderDetails and click OK.
  3. Click File > Save in the menu bar to save the report as OrderListbyDate.cls.
  4. Click the View tab to run the report and view it. The report should look similar to the following one:

    Note: If the report does not look correct, you can compare it to the final version of the report provided by JReport. To do so, you will need to save and close this catalog and then open the JinfonetGourmetJava.cat catalog file located at <install_root>\Demo\Reports\TutorialReports.

Lesson 1 summary

In this lesson, we created a standard banded report to build a simple yet very effective sales report. The detail area of the banded object displays a line for every sales order; the group and report footers are used to display the respective cumulative sales totals. We used a single dynamic summary to present three different totals: total by order, total by month and also a grand total.