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!

Advertisement