Percentages in BIRT crosstab

With a small example, based on the Classicmodels database, I demonstrate how to get percentages in a BIRT crosstab table.

In the first step I create a crosstab table that shows just numbers. In the second step I modify the crosstab so that it shows percentages.

Step 1: the Query, the Data Cube and the Crosstab
Suppose you want to build a report that shows each country (from the customers table) and the number of orders in that country by year. That’s not too difficult:

First write the query:

select c.country,
       o.ordernumber,
       o.orderdate
from   customers c,
       orders o
where  c.customernumber = o.customernumber

Then build the Data Cube. We have two dimensions and one summary field:

For the year dimension, create a Date Group on orderdate:

Finally create the crosstab by dragging the Data Cube to the report layout. The Country will be a row dimension (drag to the left side of the crosstab), the Year will be a column dimension (drag it to the top of the crosstab). The results now look like this:

Step 2: Turning the numbers to percentages
So, now I want to know the percentage of orders placed by each country by year. In order to be able to calculate the percentage, I need to know the total number of orders by year. This can be done by dragging an aggregation item to the crosstab aggregation cell. I make the aggregation item a sum of the measure field (measure[“ORDERNUMBER”]) grouped by year (GrpYear/Year), like this:

Finally, drag a Data item to the crosstab aggregation cell and enter an expression:

In the Number format property, choose Format as “percent”.
After making the measure field and the aggregation field invisible, the final result of the report now looks like this: