Mulitple Detail Rows in a BIRT Crosstab

In this post a crosstab with multiple detail rows is created. I used a question on the birt-exchange forum as a starting point for writing this post and I used the .csv file that was attached to that same question as the datasource. This is a link to the question

If you don’t feel like following the link, the person.csv file contains these rows:
pk,name,DOB,city,spouseName,spouseDOB
1,joey,19770222,nyc,jane,19790303
2,mark,19831103,nyc,leila,19850710
3,lu,19830803,boston,mary,19870905
4,bob,19761222,nyc,bobina,19750524
5,bobby,19670304,boston,andrea,19700103

Computed Column
First add a computed column to the data set. Actually it’s nothing more than a static value, that will be used as a dimension in the cube that will be created in the next step.

The Cube
Create a data cube with two dimensions: one on the PK field and one on the computed column justANumber.
Next create summary items for both the person and the spouse’s names and their birthdays. Put all of these under the same Summary Field and make sure to edit the Data Type to String and the Function to FIRST:

The Crosstab
From the palette drag a crosstab item to the report layout, then take these steps:

• drag the grpPK dimension to the columns area
• drag the grpNumber dimension to the rowss area
• drag the summary fields name and spousename to the summary area
• create a grid (1 column, 2 rows) in the rows area
• create two other grids (1 column, 2 rows) in the name and the spousename columns in the summary area

This is what you should have until now:

Now let’s move on:

• Create labels “Name:” and “Date of Birth” in the grid in the row dimension area
• Drag the name and the spousename fields – they are already in the crosstab – into the first line of the grid that is in the same cell
• Drag the DOB and the spouseDOB fields from the cube into the second line of the grids. For some reason this can’t be done in 1 step, you first have to drag it underneath the grid, then drag from the new column that is in to the grid and finally, remove the newly created column and choose “no” if you are asked if you like to remove unused bindings

Now the crosstab should look like this:

And, after doing some formatting of the gridlines and setting some visibility properties, this is the resulting report:

BIRT – Some kind of column grouping

With “some kind of column grouping”, I mean that the output of the report looks like this:

What you see are employees listed by city, with each city in its own column. If you have a better name for this instead of “column grouping”, please post it in the comments and I’ll be glad to take it over in the title of this post if I like it.

In order to get this output, you need to have the rows in the data set numbered by city. That field will be used as the row dimension in a crosstab table. BIRT does not provide out of the box functionality to get this rownumbers in the data set, so I decided to share my approach to do it.

The Query
The data source for this sample report is the ClassicModels database. The data set query selects all employees and the city they work in:

select o.city,
       e.lastname
from   offices o,
       employees e
where  o.officecode = e.officecode
order  by o.officecode,
          e.lastname

Getting the rownumber
There are a couple of ways to get this number:

• an analytical function in the query
• the GROUPROWNUM function from the group functions plugin
• other creative SQL solutions

I will further talk about the first two solutions.

The Analytical Function
This one is easy. If your database provides analytical functions, it is enough to adapt your query, so that it selects the rownumber by city. The query – tested in an Oracle database – looks like this:

select o.city,
       e.lastname
       row_number () over (partition by o.city order by e.lastname) as cityRownum
from   offices o,
       employees e
where  o.officecode = e.officecode
order  by o.officecode,
          e.lastname

The GROUPROWNUM function
To get this one to work, you need to install the group functions plugin. You can find a download and all you need to know about that in this Devshare post

After installing the group functions plugin, add a computed column to the data set. Use the GROUPROWNUM function and choose CITY in the Aggregate On field:

The Crosstab
The final step to complete the report is creating the crosstab.
First create a data cube with two Dimensions (city and cityRownum) and one Summary field (lastname). Make sure to use the FIRST function in the summary field.

The data cube should now look like this:

Drag the cube to you report and make cityRownum a row dimension and CITY a column dimension:

After some formatting of the styles (removing the grid lines) and the crosstab (Hide Measure Header, set width of row Dimension to zero) your report should produce the output as shown on top of this post.

Creating a multi column BIRT report with grouping

In an earlier post I explained how to use a crosstab item to create a multi column BIRT report. But when there are one or more groups in your report, that approach does not suffice. In this post I’ll describe a slightly different way to determine row and column numbers, so that they can be used in the crosstab together with a grouping on the data.

In this example I use the PRODUCTS table from the Classicmodels database. My goal is to show products in three columns grouped by productline.

Report variables
First I create 5 report variables. They’ll be used in the onFetch event of the dataset:

The query
This is the query I used:

 SELECT productline,
       productname
FROM   products
ORDER  BY productline, productname

The “order by productline” is important here, as you’ll see when the onFetch event script is discussed.

Computed columns
Add four computed columns to the dataset. In the expression you can enter a dummy value, I entered zero for all of them:

Dataset onFetch event script
In the onFetch script of the dataset the report variables that were described earlier will be filled and assigned to the computed columns in the dataset:

 if (vars["groupName"] != row.PRODUCTLINE) {
   vars["groupName"] = row.PRODUCTLINE;
   vars["groupNum"]++;
   vars["groupRownum"] = 1;
   vars["cubeColnum"] = 0;
   vars["cubeRownum"] = 3;
}
else {
   vars["groupRownum"]++;
   vars["cubeColnum"] = BirtMath.mod(vars["groupRownum"]-1, 3)
   vars["cubeRownum"] = BirtMath.ceiling(vars["groupRownum"], 3)
}

row.groupNum = vars["groupNum"];
row.groupRownum = vars["groupRownum"];
row.cubeColnum = vars["cubeColnum"] + 1;
row.cubeRownum = vars["cubeRownum"];

The datacube
In the datacube I create two dimensions. The row dimension combines the PRODUCTLINE column and the cubeRownum computed column. The column dimension uses the cubeColumn computed column.

The PRODUCTNAME column will be the summary field. Don’t forget to change the datatype of this field to String and the function to FIRST.

The crosstab
This is what the crosstab should initially look like:

To get a decent layout, you can execute these simple steps:

• select the crosstab and then select the “Hide Measure Header” checkbox in the general properties tab
• select the cell that contains the cubeRownum data element and set the width property to zero

  If you execute the report after these modifications, it will look like this:
  

  As you can see, the data is nicely spread over three columns until the next group comes up. Exactly how we wanted it.

Creating a multi column BIRT report

Up to now, BIRT has no feature to show records in multiple columns. In the following step by step instructions I explain how you can create a multi column report by using a crosstab.

The query
Create a very simple query to show employees from the sample database:

SELECT lastname||' '||firstname as name,
       email
FROM   employees

Computed columns
Add three computed columns to the dataset:

• rownum: This one is created for nothing but serving the next two computed columns. It’s a runningcount aggregation.
• colnum: this will be the column dimension. It’s a mod calculation of rownum against the number of columns you want in the report. To make sure that (if there is a odd number of records in the dataset) the last record is put in the first column, substract the mod calculation value from the number of columns
• cubeRownum: this column will serve as the row dimension; it gives two, or whatever number of columns you want in your report, consecutive records the same numeric value

The cube
There are two dimensions in the cube:

• rownum
• colnum

And two summary fields:

• name
• email

Don’t forget to change the datatype of the summary fields to String and the function to FIRST, like this:

The crosstab
Follow these steps to create the crosstab:

• drag a crosstab item from the palette to the report layout
• drag the colnum dimension to the top area of the crosstab
• drag the cuberownum dimension to the left area of the crosstab
• drag the summary fields to the data field area of the crosstab
• finally, to create a clean layout, drag a 3 line, 1 column grid to the data field area and drag the name summary field to the first row and the email summary field to the second row of it
• by dragging the summary fields into the data field area, two columns are created. You can remove one of them by right-clicking it and choose: remove.
• you can remove the Name and Email labels from the measure header

The crosstab should look like this now:

If you run the report, it should look like this:

That’s probably not how you want it to be, so a few extra steps are needed to tweak the layout.

Layout issues
First remove the column header by checking the Hide Measure Header checkbox in the crosstab properties:

As there is no such property for the column measure, select both the left measure column and the column above the left measure column like this:

Now go to advanced properties section and set the following properties:

• Width = 0
• Border Left style = No Line Style (same for right, top and bottom)
• Border Left width = 0 (same for right, top and bottom)

Next, select the entire crosstab table and set this properties:

• Border Left style = No Line Style (same for right, top and bottom)

Finally, the report will look like this:

Just perfect!

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: