Tables and Crosstabs in Jaspersoft Studio

Overview

Jaspersoft@reg; Studio can handle simple tables or crosstabs. Crosstabs are advanced tables where two or more fields are tabulated one against the other, and are heavily used in survey research. The main goal of this tutorial is to show how and when to use tables and crosstabs.

Prerequisites

In this tutorial we will use many features of Jaspersoft Studio and JasperReports, and many of these relevant features are explained in other tutorials. To follow this tutorial, we suggest you follow the following tutorials:

Create the Report

The first thing to do is create a new report. In Jaspersoft Studio, select File > New > Jasper Report, and name the new report "TablesExample" . Use the Cherry template. For the data adapter use Sample DB - Database JDBC connection that came with Jaspersoft Studio. As SQL query to discover the fields use:

select * from orders

Now you have a list of the discovered fields, add them to the report in this order: ORDERDATE, ORDERID, SHIPNAME, SHIPADDRESS and SHIPCITY. Don't use groups, and complete the wizard. Now you have a report containing fields. You can test it switching to the Preview tab and compiling the report. If it compiles successfully, your result should be something like this:

The Tables

Now we can create our first table. First, decide what the table will display and where it will be located. For the location, we can choose the report summary, because it is printed at end of the report and can be used to easily view aggregated data. For this example we'll display in the table the number of shipments made in every year. If you don't see the Summary band in your report it is probably because its size is set to zero, so select the summary from the Outline view and change its height. Then drag a table element from the palette to the Summary band and a wizard appears. On the first step, choose whether the table will use an existing dataset or if you want to create a new one. For our purposes choose to create a new one and click Next.

In the next step, name the dataset "TableDataset", select to create a new dataset from a connection or a database and click Next.

Now you have to choose the data adapter. Use the built-in database Sample DB - Database JDBC Connection with the SQL query:

select YEAR(SHIPPEDDATE),count(*) from orders group by YEAR(SHIPPEDDATE)

This query selects the year from the shipment date and the number of records grouped also by the shipdate year. Doing this we have the number of record and the year of the shipments done in that year. Then you can click Next to see the discovered fields, here you can see two fields named C1 and C2, add them to the fields of the new dataset using the button >> or by double-clicking on each one. Then you can click Next and, since we don't need groups, click Next. In the next step you can select the connection to the data source. Leave the default option, Use the same connection used to fill the master report, and click Next.

At this point we can define the fields in the columns of the table. Add them all and click Next. Finally, we can choose how the table is composed. We have six types of special cells:

Table Header and Table Footer: printed at the start and at the end of the table.
Column Header and Column Footer: printed at start and at the end of the table on every column, underabove the table header and footer.
Group Header and Group Footer: printed at start and at the end of every group, visibles only if the table is using groups

For what we need leave enable only the Column Header and Column Footer and hit Finish.

Now that you have created the table, arrange the layout and change the labels C1 and C2 to display Year and Number of Shipments. To change the value of these labels, or of any other table cell, you must go in the cell editor by double-clicking on the table in the main report. Now you have a fully working table, that can be enhanced by updating the following:

Since the table has no column footer defined, this is where we can put the total number of shipments.
Some element has no shipment date, and for this the value null is shown. We can change this to"Undefined".
The color of the column header and footer cells are very different from the ones of the main report.

For the first point we need a support variable that sums all the values returned from the second field of the TableDataset query. First switch into the main report, expand the dataset TableDataset, right-click on Variables and select Create Variable. A variable called "variable_1" is created. Select it from the outline and set its fields as follows:

Value Class Name: set it to java.lang.Integer, since we need a numeric value;
Calculation: set it to sum since we want to sum single values;
Expression: use the value $F{C2}, since we want to sum every single value of the field C2;
Initial value Expression: new Integer(0), since the sum start from zero.

Go in the table editor by double-clicking on the table and putting a static text from the Palette into the left column footer cell. Set the text to Total, then drag and drop variable_1 from the Outline view into the right cell of the footer, and resize them to fit the cells.

Now we need to change the value null with the string undefined. In the table editor select the element with the value $F{C1}. As you can see this is a text field inside the cell that as has only an expression in the field. Edit the expression of this field by double-clicking it, and set its expression to:

$F{C1} == null ? "Undefined" : $F{C1}

This is a simple IF expression in a compact form (condition ? case then : case else) that checks if the field is null, in which case returns the value "Undefined", otherwise it leaves the value of the field.

After that the only thing to do is change the color of the cell. When a table is created in Jaspersoft Studio the cell color is not set, but other styles are created. For example:

Table_TH for the table headerfooter cells
Table_CH for the column headerfooter cells
Table_GH for the gourp headerfooter cells

And these styles are created for every table, to avoid the name clashes a progressive number is placed at the end of the name of the style until an unique name is found. So select the style "Table_CH" and from the properties change the background color to a light gray. The final result should be something like the following image:

Now switch to the Preview tab to see the result:

The Crosstabs

Crosstabs are little more complicated than tables, because its not easy to understand how the data is "crossed". Now that we have a table that shows the number of shipments made each year. With the crosstab we can have something more informative, like the shipment done year by year, but also divided by the destination countries. So the goal is to have the countries on the rows, the years on the columns and in every cell of the table the number of shipment done for a precise country in a precise year.

First, remove the old table from the report, and drag and drop a Crosstab from the palette into the designer to launch the crosstab wizard. In the first step of the wizard, you can choose to create a new dataset for the crosstab or use an existing one. For our purposes, this time we can reuse the main dataset, so select Create a crosstab using an existing dataset, select [Main Dataset] and click "Next".

Now you need to choose the fields that go on the columns. For our goal we need the shipped date, but we need only the year and we have two ways to get this:

Create a new dataset or change the main dataset query to have another field that contains only the year, something like "select ORDERDATE, ORDERID, SHIPNAME, SHIPADDRESS, SHIPCITY, SHIPCOUNTRY, SHIPPEDDATE, YEAR(SHIPPEDDATE) as SHIPPEDYEAR from orders", that returns all the fields we need.
Using the main dataset but change manually the column element expression in the crosstab, from something like "$F{SHIPPEDDATE}" to "YEAR($F{SHIPPEDDATE})".

The first way is easier and cleaner, but since we already know how to create datasets and change the main dataset query, and we have never seen how to edit an already created crosstab, let's choose the second option.

Returning to the wizard add the field SHIPPEDDATE by double-clicking it and click Next.

Now we need to add the country to the rows, so in the next step select the field SHIPCOUNTRY and click Next.

It's time to define the detail data. Normally, the detail is the result of an aggregation function like the count of orders by country by year, or the sum of freight for the same combination (country/year). We'll choose to print the number of orders placed by adding the field ORDERID with the Count calculation (default value) and then click Finish.

Now that you have your crosstab created, change its size to fit the band and compile the report by switching to the Preview tab. Here you will see two problems:

Since we have used a the shipped date on the columns now we have a column for every day where there was a shipment, instead of every year;
Some elements don't have a shipped date, so there is a column for them with the label null, it would be better to have the string Undefined (in a similar way to what happens with tables).

For the first problem go in the crosstab editor by doubleclicking on the its element. Here you see a dedicated outline view for this element, expand the element "Crosstab" and here you will see three very important elements: "Row Groups", "Column Groups" and "Measures". Here are placed the fields choosen for row, column and detail and to use this fields and are used elements of the type "RowGroup", "ColumnGroup" and "Measure" that are someway similar to variables since they use an expression that refer a field, they have a type and a calculation time.

Now select the element SHIPPEDDATE1 and from its Properties tab set the values:

Expression to "YEAR($V{SHIPPEDDATE1})" because we want only the year in the columns;
Value Class Name to "java.lang.Integer" because the year is seen as an integer number, instead the date has a different type.

Now you can compile and you will see that the crosstab has fewer columns, since all columns are now grouped by year.

At this point we need to substitute the null value with the Undefined one, and to do this we must change the expression value of the header of the column. Select the header cell of the column that represents the shipment date and you can see that the element inside the cell is a text field. In the Properties tab you'll see the properties of this text field, move to the tab Text Field and change the value of the expression from $V{SHIPPEDDATE1} to $V{SHIPPEDDATE1} ==null ? "Undefined" : $V{SHIPPEDDATE1}. This adds an if expression in the valorization of the header to return a string when the value is null.

Now you can compile your report, which should produce a result like the following:

Now your table shows total number of shipment year by year, and state by state, so it is much more informative than a simple table.

The "Total" Element

As you can see, by default in every crosstab there is a "Total" row and a "Total" column at the edges of the table. This elements are present by default because in most of cases they are really useful, but you can remove them and change their position if needed. Suppose that you want to remove the "Total" column. In the Crosstab editor, from the Outline view, select the ColumnGroup SHIPPEDDATE1. In the Properties tab, there is a field called "Total Position" that can have any of the following values:

None: no total column is shown;
Start: The total column is shown as first column of the table;
End: The total column is shown as last column of the table.

These also apply to the total row. Generally, there is a total element for every group, and you can remove that element from its group properties.

Additional Information About the Wizard Process

Some operations described in this tutorial can be done during the wizard process. For example, you can extract the year from the SHIPPEDDATE and remove the total column. After we add the SHIPPEDDATE field to the columns set we can configure some other properties, as shown in the following image: