### 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 more than 10 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.

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 JReport Designer toolbar, click the New Page Report button .
2. In the New Page Report dialog, clear the text in the Report Title text box, select Chart from the layout box, then click 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.

3. In the Data screen of the Chart Wizard, select the query AnnualSalesbyRegion from the Queries node of Data Source 1 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, click OK, define the formula in the Formula Editor window as follows (you can copy the formula to the formula editing area directly), click File > Save to save the formula, and then click File > Close to close the dialog and 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 by selecting them and clicking button one by one. 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 Resources panel of the Display screen and then select <Add Summary...>.
2. In the New Summary dialog, specify the aggregate function as Sum, add the field Annual Sales from the Customers table to the Summary 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 by selecting it and clicking button. The Display screen appears as follows:

5. Click Layout to switch to 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.

   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.

6. Click Next to go to the Style screen and select Classic from the style list.
7. Click Finish to create the chart report and the report shows as follows in the design view:

8. 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 pt.

   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 Format Legend dialog, click the Font tab, set Font as Arial and Font Size to 10 pt.
8. Switch to 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 chart 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. Double-click the Regions label. In the Format Label dialog, switch to the Font tab, specify Font as Arial and Font Size to 10 pt , and then click OK.
15. On the report tab bar, right-click the report tab and select Rename from the shortcut menu, then enter AnnualSales in the Input Report Tab Name dialog and click OK.
16. Click File > Save to save the report as AnnualSalesbyRegion.cls.
17. 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 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`.

#### 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 Report Format and keep the default settings of other options. Then click OK.

3. Open the file AnnualSalesbyRegion_AnnualSales.xls saved in `<install_root>\Demo\Reports\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 2013 and 2014, 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.