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

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 Page Report button .
  2. In the New Page Report dialog, clear the text in the Report Title text box, select Crosstab 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 of Lesson 1.

  3. In the Data screen of the Crosstab 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. In the Add Table dialog, add the Customers, Orders, Orders Detail, and Products tables to the query, then click Done.
  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 aggregate function to Sum.

  9. Switch to the Style screen and select Simple from the Style list, 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. Select the crosstab and change its Position property to absolute in the Report Inspector, then drag it to the following position:

    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.

  2. From the Toolbox panel, drag three labels to the report, edit their text respectively to Units, Sales and Product Sales Analysis, and then adjust the crosstab and the three labels to align them as follows:

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

  1. Select the Units label and edit its Bold property to true, Background property to Gray and Foreground property to White.
  2. Select the Sales label and edit its Bold property to true, Background property to 0x99ccff and Foreground property to White.
  3. Select the Category field and edit its Background property to 0x99ccff and Foreground property to White.

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

  5. Select the four #,###.00 cells, change their Foreground property to 0x99ccff, Bold property to true and Format property to $#,###.00.

  6. Select the Product Sales Analysis label, set its Bold property to true, Font Size property to 18, and Foreground property to Red, then resize it to make its text not be truncated.
  7. On the report tab bar, right-click the report tab and select Rename from the shortcut menu to rename the report tab as ProductSales.
  8. Click File > Save to save the report as ProductSalesAnalysis.cls.
  9. 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 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: 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 Page 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 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 <install_root>\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.