## Lesson 4: Creating a chart report

The fourth quarter of the year has just ended, and you have been asked to produce a chart that shows product annual sales for each sales region. The sales manager wants to compare the historical sales data with the current data. Here's a sketch of the report that the sales manager has given to you:

You recognize that this is a bar chart. JReport supports 11 general chart types, and most of them have many sub-types or variations. After showing the sales manager the available bar chart types, he chooses the 2-dimensional clustered bar chart. For a summary of the JReport chart types, see Chart types in the Components chapter of the JReport Designer User's Guide.

Additionally, the sales manager requests that the report output format should be Microsoft Excel. This is not a problem, because all pre-defined reports in JReport can be exported to HTML, PDF, Excel, RTF, XML, Text, Postscript, Mail and Fax.

This lesson contains the following tasks:

- Task 1: Create the chart
- Task 2: Format the chart
- Task 3: Export the report to an Excel file

### Task 1: Create the chart

1. From the File menu in JReport Designer, select New > Report Set.
2. In the New Report Set dialog, clear the text in the Report Title text box, select Chart Report from the layout box, then click OK.

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

3. In the Data screen of Chart Report Wizard, check the New radio button, scroll down to the Queries node in Data Source 1, select the query AnnualSalesbyRegion and then click Next.
4. In the Type screen, check the Single chart radio button, select Bar as the chart type and Clustered Bar 2-D as the sub type, then click Next.

In this chart, we want to display the region name in the Category(X) axis, and annual sales of each region in the Value(Y) axis. However, as the region names are too long to be displayed completely in the Category(X) axis, we will create a formula here to just get the abbreviations of the region names.

1. In the Display screen, scroll down to the Formulas node in the Resources panel, and then select <Add Formula...>.
2. Enter the formula name Region_AbbreviationName in the Input Formula Name dialog and define the formula in the Formula Editor window as follows, then click OK to finish editing.

       if (@Customers_REGION == "Asia-Pacific (APAC)")
                   "APAC"
       else if (@Customers_REGION == "Europe, Middle East, Africa (EMEA)")
                   "EMEA"
       else if (@Customers_REGION == "Latin America (LATAM)")
                   "LATAM"
       else if (@Customers_REGION == "North America (NA)")
                   "NA"

3. Add the formula Region_AbbreviationName to the Category box, and YearofOrderDate to the Series box. In the Show Values box, a numerical value is required.

In this lesson, we need to show the annual sales of each region, so a summary which is created on the column Annual Sales, and grouped by the formula Region_AbbreviationName is needed.

1. Scroll down to the Summaries node in the Display screen and then select <Add Summary...>.
2. In the New Summary dialog, specify the function as Sum, add the field Annual Sales from the Customers table to the Sum On field, check the Static Summary radio button, specify the Group By field as Region_AbbreviationName from the Formulas node, then click OK.

3. Enter Sum_AnnualSalesbyRegion_AbbreviationName in the Summary Name dialog and click OK to create the summary.
4. Add the newly-created summary to the Show Values box. Then, click Next.

   The Display screen appears as follows:

5. In the Layout screen, select Title from the Options box, and then enter Annual Sales by Region as the Chart Title and Regions as Category(X) Axis Title.
6. Click Finish to create the chart report and the report shows as below in the design view:

7. Click the View tab to view this chart, and it appears as follows:

### Task 2: Format the chart

The chart is accurate, but a little simple. We can add some polish to it by setting some of the Chart properties.

1. Click the Design tab to return to the design mode to do the adjustments.
2. Double-click a bar of the chart, and the Format Bar dialog appears.
3. In the General tab of the Format Bar dialog, set the Use Depth option to true, and specify Depth as 10px and Direction as 45deg.

   In the Format Bar dialog, you could also choose another sub-type for the chart in the Layout box if desired. In this lesson, we will continue using Clustered - 2D.

4. Switch to the Data Label tab, specify Font as Arial and Font Size to 10.

   The Data labels that charts contain can be either static or dynamic. You can check the Show Static Data Label option and specify the Position for data labels according to your requirements, so that the labels are displayed statically in the chart. But in this lesson, we will just use the labels as dynamic ones, which will appear when the cursor is placed on bars.

5. Click OK to apply these property settings to bars of the chart.
6. Right-click the legend and then select Format Legend from the shortcut menu.
7. In the Placement tab of the Format Legend dialog, select the Right radio button and the second alignment way.

8. In the Mark tab, select Item 0 from the Mark Items list, and select circle from the mark drop-down list.

9. Select Item 1, and apply upward triangle to it in the same way.
10. Click OK in the Format Legend dialog to apply these changes.
11. Right-click the Value(Y) axis and then select Format Axes > Format Value(Y) Axis from the shortcut menu.
12. In the Format tab of the Format Value(Y) Axis dialog, select $#,##0. in the Number category, and then click Add.

13. Click OK in the Format Value(Y) Axis dialog.
14. Resize the Annual Sales by Region and Regions labels to avoid them to be truncated.
15. Adjust the position of the chart and the labels as desired by dragging and dropping them.

JReport provides a set of CSS styles that can be applied to reports to easily change the format and appearance of the report. We will apply the Classic style to the chart.

1. Select the ChartObject node in the Report Inspector and then select Classic from the <Import CSS File...> drop-down list on the Format toolbar.
2. On the report tab bar, right-click the tab of the report and select Rename from the shortcut menu to rename the report as AnnualSales.
3. Click File > Save to save the report set as AnnualSalesbyRegion.cls.
4. Click the View tab to preview the report and it looks somewhat as follows:

   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`.

### Task 3: Export the report to an Excel file

Now we can export the report to different formats as required. In this lesson, we will export it to an Excel file.

1. Click File > Export To > Excel on the menu bar.
2. In the Export to Excel dialog, check Preserve Report Formatting and keep the default settings of other options. Then click OK.

3. Open the file AnnualSalesbyRegion_AnnualSales.xls saved in `C:\JReport\Designer\Demo\JinfonetGourmetJava`. It appears as follows:

### Lesson 4 summary

In this lesson, we created a chart report to represent the annual sales of four regions in both 2005 and 2006, formatted the chart by setting properties and then made a quick and effective change to its appearance by applying a CSS. Finally, we exported the chart to an Excel file, where the report layout and format are accurately represented.

By reviewing the chart, the sales manager can easily compare the sales per region over a two-year period.