Crosstab for dynamic columned report in the Jasper report with JasperSoft Studio Tutorial

We cannot predict the number of records available in a database table. To display it in a jasper report, we have to generate the table dynamically. We use jasper report tables to achieve this. What if we want to generate a table with dynamic columns in the jasper report. With jasper report Crosstab, we can generate both columns and rows dynamically.

The output of the jasper report Crosstab tutorial

Output of the Jasper Report crosstab

This tutorial we are not going to demonstrate how to create a new jasper report and other basic stuff. You can refer to our previous tutorials for the jasper report.

Let's drag and drop jasper report crosstab element into the main report.

Create crosstab Jasper report

In the popup, We are going to select Create a crosstab using a new dataset. Click next. We are going to keep the default name for the data set, but you can change it.

Crosstab data set configuration

Click Next again. In this screen, I'm going to select musql_data_adaptor, which is already created in my jasper studio.

Crosstab data adapter configuration

Next screen, you will see all available databases in our jasper report data adapter. In this screen, you can select the required tables under a database.

Crosstab database and the tables

Select the diagram tab on the right side of the screen and drag and drop your table into it. Before click Next, select the fields that you want to continue with.

Crosstab database and the table fields

Next screen, select all the fields.

Crosstab database and the table fields

Crosstab data structure is similar to a Map data structure. Each cell is mapped to a column and a row. You can consider columns and the row as 2 ids to uniquely identify a cell.

Click Next 2 times. You will see the below screen. Here you can select the data member that use to generate columns of the jasper report crosstab.

Crosstab define columns

In the next screen, you need to select a data member to use as a row id. Here i'm selecting student name field.

Crosstab define row id

In the next screen, you need to select a data member for measures. For each measure, you can have an aggregate method. I'm not going to select any aggregate calculations here. So I will select the First option.

Crosstab define measures

In the next screen, you can change the colors and other styles. Click Finish.

Jasper report Crosstab

Adjust the size and place of the jasper report crosstab. After that its ready to preview. Click on the preview. Then, you can see the output of jasper crosstab.

Jasper report Crosstab preview

We have created our first jasper report example about the jasper report crosstab element.
Check out our video. for the more information up to now.

How to set a default value to a jasper report crosstab cell with null value

Click on the detail element on the Outline Palette as below.

Jasper report Crosstab Outline Palette

Go to the properties of the element. Under Text Field properties, you can configure what to do with null values.

By selecting "Blank When NULL" option, you can set your nullable cells to blank.

Jasper report Crosstab cell blank when null

You can do more customizable data manipulation from the "Expression" option. Click on the Expression button.

Jasper report Crosstab Cell Expression

In the jasper report expression editor, you can write custom code to manipulate jasper report crosstab cell data.

Jasper report Crosstab Cell Expression

Now you are almost ready. Click on the preview tab and preview the final results of the jasper report crosstab after setting a default value for the null cells.

Jasper report Crosstab final preview

For more information, check out our video.

Thank you for checking out our tutorial. Hope you will enjoy this tutorial and learn a lot from this.