## Lesson 1: Creating a standard banded report

A report of monthly sales from 2005 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
- Task 2: Add summaries and a print date to the report
- Task 3: Fine tune the report layout

### 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. Start JReport Designer by choosing Start > All Programs > JReport 9.0 > Designer.

   The JReport Designer window and the Welcome to JReport Designer dialog appear.

2. In the Welcome to JReport Designer dialog, click the Catalog link in the Open category to bring out the Open Catalog File dialog.

3. Browse to select the JinfonetGourmetJava.cat catalog file in `C:\JReport\Designer\Demo\Reports\JinfonetGourmetJava`, then click the Open button.

   If JReport Designer is not installed in its default location of "`C:\JReport\Designer"`, adjust the path accordingly.

4. Click File > Options, and in the Options dialog, select the Componnet category, choose Basic from the Report Style Group drop-down list, then click OK to accept the change.

   By default, when you create a banded, table, crosstab or chart report via the report wizard, a default style Commercial will be applied to it. However, in our lessons, we do not want to apply the default style in all cases, instead, we will customize the style by ourselves, so here we change the default style group setting to Basic, which is the simplest style with very little formatting.

5. Choose File > New > Report Set or click the New Report Set button on the Standard toolbar.
6. In the New Report Set dialog, clear the text in the Report Title text box, select Standard Banded Report from the layout box, then click OK.

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

   A report set is a container which holds multiple reports. Reports in the same report set 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 report set file has a .cls extension.

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

9. In the Query Editor window, click Query > Add Table, then in the Add Table dialog, add the tables Orders, Orders Detail, and Products to the query, select all the columns in the three tables by selecting the * checkbox, then uncheck the Inventory and Reorder Leave 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 conditions. 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.

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 to create the query.
2. Click Next button in the Standard Banded Report 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 panel, add the following fields in the Products table to the right-hand panel one by one: Product Type Name, Products_Product ID, Product Name and Category.
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 panel, 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. Click Finish to create the report. The Summary, Filter and Style 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:

4. 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 Function drop-down list, select the Total formula from the Resource panel and click to add it to the Sum 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.

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

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

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

10. Edit the text of the Print Date and Print Time labels to Date and Time, respectively.
11. 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. Select all the objects in the report and change their font face to Arial by selecting the item from the Font Face Box on the Format toolbar.
2. 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.
3. Change the text of the Products_Product ID name label to Product ID, so as to save some space to make the report look tidy.
4. 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:

5. Select the five labels Product Type Name, Product ID, Product Name, Category and Total, then in the Report Inspector, set their Bold property to true, and the properties Background and Foreground to Lightgray and White.
6. 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.
7. Select the Total by Order and Total by Month summaries, then change their Bold property to true and Foreground property to Gray in the Report Inspector.
8. Select the Grand Total summary in the BandedFooter panel, then change the Bold property to true and Foreground property to Red.

9. Select the Order ID group by field in the second GroupHeader panel and change its Bold property to true, and Foreground property to Red.
10. 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 Bold property to true and Foreground property to Gray.
11. Resize the Order List by Date label, change its Font Size property to 18 and Foreground property to Red.
12. Resize the Date and Time special fields and change their position in the BandedPageHeader panel to make them look tidy in the report.
13. Select the DBField Product ID in the Detail panel and its name label, then change them to be left aligned to improve the layout of the report.
14. 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 a meaningful name and save the report set.

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

2. In the Input Report Name dialog, enter OrderDetails and click OK.
3. Click File > Save in the menu bar to save the report set 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 versions of the tutorial reports provided by JReport. To do so, you will need to save and close this catalog and open the TutorialReports.cat catalog file instead, which is located at `C:\JReport\Designer\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.