Mulitple Detail Rows in a BIRT Crosstab

2013/09/20

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: