## Lesson 6: Creating a crosstab report

A report with quantities of different products that are sold in different regions and their sales totals is needed. You are required to create a report that can clearly represent the necessary information, which allows the sales manager to easily compare the sales of different products in different regions.

Here is the sketch that is given to you:

In this lesson, we learn to create a crosstab report from the following tasks:

- Task 1: Create the crosstab
- Task 2: Format the crosstab
- Task 3: Change the page layout of the report
- Task 4: Save the report style as a CSS file

### Task 1: Create the crosstab

Before taking this task, make sure you have enabled the Insert field name label with field option in the Options dialog as noted at the end of Lesson 3. Otherwise, the name labels will not be inserted together with the fields when you add fields to the crosstab.

1. From the JReport Designer toolbar, click the New Report Set button .
2. In the New Report Set dialog, clear the text in the Report Title text box, select Crosstab 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 the Crosstab Report Wizard, select the New radio button.
4. Expand the Queries node in Data Source 1, click <Add Query...>, enter ProductSalesAnalysis in the Input Query Name dialog and click OK.
5. In the Query Editor, click Query > Add Table to add the Customers, Orders, Orders Detail, and Products tables to the query.
6. Check all the columns in the Orders Detail table, the following columns in the Customers table: Customer Name, Customers_City, Customers_State, Customers_Country, Customers_Territory and Customers_Region, and Products_Product ID, Product Name, Category, Product Type Name and Price in the Products table.

   You may notice that here we do not check any columns in the Orders table, that is because in this report, columns in this table are not needed but we need this table to create joins between tables in the query.

7. Click OK at the bottom of the Query Editor to create the query.
8. In the Display screen, add the DBField Category to the Columns box, Customers_Country to the Rows box, the Quantity DBField and Total formula to the Summaries box, and specify their function to Sum, then click Finish to create the crosstab report.

### Task 2: Format the crosstab

In this task, we will format the crosstab to make it look more professional.

1. Click Insert > Label to add three labels and edit their text respectively to Units, Sales and Product Sales Analysis.

2. Select the three labels and set their Position property to absolute in the Report Inspector.
3. Select the crosstab and change its Position property to absolute as well.

After changing the Position property, the four objects in the report overlap each other. So next we will adjust the position of the objects.

By setting the Position property of an object to absolute, the object will be located at the position specified by dragging and dropping or by setting its X and Y coordinate property values. Meanwhile, when objects in a flow layout container have their Position property value set to absolute and are overlapped, we can set the display order of the objects by right-clicking the object and then selecting an item from the Move submenu.

4. Adjust the crosstab and the three labels, and align them as follows:

5. Right-click the crosstab and choose Move > Move to Back from the shortcut menu.

Next, we will edit properties of the crosstab report objects in the Report Inspector to improve the appearance of the report.

1. Select the four ##### cells in the crosstab and specify their Bottom Line property to solid, Border Color property to Lightgray, and Format property to #,###.

2. Select the crosstab and specify its Border Color property to Lightgray as well.
3. Select the Units label and edit its Bold property to true, Background property to Gray and Foreground property to White.
4. Select the Sales label and edit its Bold property to true, Background property to 0x99ccff and Foreground property to White.
5. Select the Category field and edit its Background property to 0x99ccff, Foreground property to White, Bold property to true and Word Wrap property to true.

6. Select the two Total cells, the Customers_Country field and the four ##### cells in the crosstab, and then specify their Foreground property to Gray.

7. Select the Customers_Country field and the two Total cells and edit their Bold property to true.
8. Select the four #,###.00 cells, change their Foreground property to 0x99ccff, Bold property to true and Format property to $#,###.00.

9. Select the Product Sales Analysis label, set its Font Size property to 18, Bold property to true and Foreground property to Red, then resize it to make its text not be truncated.
10. On the report tab bar, right-click the tab of the report and select Rename from the shortcut menu to rename the report as ProductSales.
11. Click File > Save to save the report set as ProductSalesAnalysis.cls.
12. Click the View tab, and the crosstab report appears somewhat like 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`.

### Task 3: Change the page layout of the report

When previewing the report, we can see that the crosstab is displayed in two pages. However, with JReport's page mode feature, we can decide how to view a report: in pagination mode or continuous mode. In pagination mode, the page shape and size can be specified in the page panel settings. In continuous mode, the whole report is laid out in a single page, and has no specific shape and size. You can refer to Designing a report page in the Reports chapter of the JReport Designer User's Guide for more details about other page related features.

1. Uncheck Page Layout on the View menu.
2. Click the View tab to view the report again.

   Now, the crosstab is displayed in a single page. We can drag the scroll bar to have a complete view of the crosstab.

For a crosstab component, when it is in continuous page mode, we can further set another two properties to determine how many rows and columns we would like to view.

1. Select the crosstab, and set its Items per Row Block and Items per Column Block property values to 3 in the Report Inspector.
2. View the report. Now only three items are displayed in the row and column blocks.

3. Check Page Layout on the View menu to switch back to the pagination mode, then the two properties will not take effect.
4. Click File > Save to save the report set again.

### Task 4: Save the report style as a CSS file

After formatting the report step by step in the Task 2, we can then save the report style as a CSS file, which can be applied to other crosstab reports directly.

1. Right-click the crosstab and select Save Style from the shortcut menu.
2. Keep the default settings in the New CSS Style dialog and click OK.

3. In the Save CSS As dialog, enter Crosstab.css in the File Name field, then click Save to save the file to the default directory `C:\JReport\Designer\style`\.

   The CSS Style Definition for CTCrosstab dialog appears.

4. Add all the properties of the crosstab from the All Properties box to the Selected Properties box by clicking the button , then click Save to save these properties in the crosstab.css file.

Now, the style of the crosstab report has been saved as a CSS file. Properties in the file can be applied to corresponding components of other reports directly by selecting the CSS file in the <Import CSS File...> drop-down list on the Format toolbar of JReport Designer.

### Lesson 6 summary

In this lesson, we created a crosstab report to represent the product sales analysis of different regions, and then formatted it to improve its appearance. Then, we used the page mode feature to preview the report and edited some properties to reduce the records on the row and column blocks. And finally, we saved the style of the crosstab report as a CSS file so that the styles we defined here can easily be applied to another report.