Crosstab in Jaspersoft

Posted on by By Sohail, in Business Intelligence, Data Visualization, Databases, Jaspersoft, Open Source Business Intelligence | 0

Beginner’s Guide to Crosstab in Business Intelligence Using Jaspersoft IReport

What is Cross Tab?

A Table is just of 2 Dimension but at times we do need our table to be represented in a 3 dimensional manner or in a Dynamic Manner.


Take for example a table with 2 data set i.e. in the row we have countries and in the column we have months of the year

Now at times our data set will have an extra data which needs to be represented in the table where then can we keep these column or even row?

So here Cross tab Helps in those situation to make the report more meaningful

Now we can have Rows as “Country” and a sub row “State” grouped by the country.

To get a better understanding I’ll be taking an Example to represent my data in-form of a cross-tab.

From the following

Tables :

  • sales_fact_1997 sf7
  • customer
  • time_by_day

Fields like country, state, month, store_sales and etc if required,
should be represented in Cross Tab.


Open Jasper-soft I Report

-> Click on File->New->Report->Select Any Template of your choice

->Remove the Bands you don’t require

in My case i just need the Title and Summary Band so i wont remove them

Connect to the required Tool/Database where your Query will be retrieved from

After connection

Enter your query by clicking on ->


and then


After entering your query select Read Fields and Click OK.

->Go to the palette section and Drag and Drop the cross tab to which ever band you want
(In my case “Summary” Band)

Now select Main Report Data-set and Click Next

Now In Your Row Group 1 select what do you want as the Group i.e. the 1st Dimension (in My Case Country)

Now in Row Group 2 select what do you want as the Group i.e. the 3rd Dimension which is a Sub-Group of the First Group (in My Case State_province)

As shown below:


Then Click Next

Now in Your Column Group 1 Select what should be your Column Group which is your 2nd Dimension (month of year)

As shown below:


Then click Next

Now in measure what will you like to calculate in your table? So you input in the Measure Field (in my case Store Sales)

And what function will you like to perform (in my case SUM of store sales)

As shown below


Then click Next.

The Next Dialogue Box is of your Choice

Then click Finish

For any formatting select Cross-tab


As shown above

And then perform your formatting techniques.


final output-> Crosstab_blog_3

By Sohail Izebhijie


Best Open Source Business Intelligence Software Helical Insight is Here


A Business Intelligence Framework

0 0 votes
Article Rating
Notify of
Inline Feedbacks
View all comments