### Lesson 1: Creating a standard banded report

A report of monthly sales from 2014 to 2015 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. Click Start > All Programs > JReport 13.5 Update 2 > Report 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.

3. Browse to select the JinfonetGourmetJava.cat catalog file in `<install_root>\Demo\Reports\JinfonetGourmetJava`, click the Open button, and then click Yes in the Warning dialog.
4. Choose File > New > Page Report on the menu bar, or click the New Page Report button on the Standard toolbar.
5. In the New Page Report dialog, clear the text in the Report Title text box, select Banded from the layout, 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.

6. In the Data screen of the Banded Wizard, click <Add Query...> in the Queries node of Data Source 1, enter OrderListbyDate in the Input Query Name dialog and then click OK.

7. In the Add Table/View/Query dialog, expand the JDBC connection node and then the Tables node, then select the tables Orders, Orders Detail and Products and click to add them to the query. Click OK to close the dialog.

8. In the Query Editor window, 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.

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, select the following fields in the Products table and click button to add them 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, select the fields Order Date and Orders_Order ID in the Orders table and click to add them 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. The Summary, Chart, and Filter screens are skipped.

   By default, when you create a banded, table, crosstab or chart report via the report wizard, a default style Basic 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 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...> in the Summaries node.
2. In the New Summary dialog, choose Sum from the Aggregate Function drop-down list, select the Total formula in the Formulas node of 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.

5. Drag the summary Sum_ProductSales_DynamicSummary from the Summaries node of the 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 double-click it to edit its text to Order List by Date.

8. Click Insert > Special Fields > Date-time > 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. Double-click the Print Date and Print Time labels and edit their text to Date and Time, respectively.
11. Adjust the position of the two groups by fields, add two labels ahead of them for identification by clicking Insert > Label 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, Product ID, Product Name and Category name labels in the BandedPageHeader panel by holding the Ctrl key on the keyboard 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 by holding the Ctrl key on the keyboard, 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 by holding the Ctrl key on the keyboard, then in the Report Inspector, set their properties Background and Foreground to Lightgray and White.
5. Select the three summaries and their name labels in the two GroupFooter panels and the BandedFooter panel by holding the Ctrl key on the keyboard and make them right aligned with the Total formula, then double-click the summary labels to edit their text to Total by Order, Total by Month and Grand Total respectively.
6. Select the Total by Order and Total by Month summaries as well as their name labels by holding the Ctrl key on the keyboard, then change their Foreground property to Gray in the Report Inspector.
7. Select the Grand Total summary and its name label in the BandedFooter panel by holding the Ctrl key on the keyboard, then change the Foreground property to Red in the Report Inspector.

8. Select the Order ID group by field and its name label in the second GroupHeader panel by holding the Ctrl key on the keyboard and change their Foreground property to Red in the Report Inspector.
9. Select the Date and Time special fields in the BandedPageHeader panel and the Order Date group by field in the first GroupHeader panel with their name labels by holding the Ctrl key on the keyboard, then change their Foreground property to Gray in the Report Inspector.
10. Resize the Order List by Date label, change its Font Size property to 18, and Foreground property to Red in the Report Inspector.
11. Resize the Date and Time special fields and their name labels and change their position in the BandedPageHeader panel to make them look tidy in the report.
12. In the Report Inspector, select the two GroupFooter panels and the BandedFooter panel by holding the Ctrl key on the keyboard, 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.