Just a couple of days after I posted my Columns to Rows in BIRT Data Set I ran into this birt-exchange forum post where Robilco provides a solution for the exact same problem. I like his approach and he was kind enough to let me write about it on this blog.

This approach has no need for computed columns and a joint data set, just a scripted data set and some more scripting.

Problem Description
The starting point is a .csv file with this content:

The different types of budgets need to be in rows instead of in columns.

CSV data set
Like in my previous post, a data set is created for the purpose of the .csv file. But this time some scripting is added.

In the BeforeOpen method of the data set, two variables are initialized:

idx = 0;
budgetRecs = [];

In the onFetch method of the data set, an array is created with the budget data. The budget types are already separated in this step:

budgetRec = {department: row["Department"], budgetType: "Infrastructue", budget: row["Infrastructure"]};
budgetRecs[idx++] = budgetRec;
budgetRec = {department: row["Department"], budgetType: "Training", budget: row["Training"]};
budgetRecs[idx++] = budgetRec;
budgetRec = {department: row["Department"], budgetType: "Communications", budget: row["Comms"]};
budgetRecs[idx++] = budgetRec;
budgetRec = {department: row["Department"], budgetType: "Consumables", budget: row["Consumables"]};
budgetRecs[idx++] = budgetRec;

Scripted data set
The scripted data set won’t do anything else but fetching the data that is entered in the array in the onFetch method of the CSV data set.

The open method initalizes the array index:

idx = 0;

The fetch method (not the onFetch method!) reads the array and moves the data into the data set columns.

if (idx < budgetRecs.length) {
	row["Department"] = budgetRecs[idx].department;
	row["BudgetType"] = budgetRecs[idx].budgetType;
	row["Budget"] = budgetRecs[idx].budget;
	return true;
	return false;

Dummy grid
To make sure the CSV data set is executed, drag a grid element to the report layout and bind it to the CSV data set. As long as no report items are put in the grid, it will not be visible when the report is executed.

To check if things work as expected, drag the scripted data set to the report layout and run the report. As you can see: every budget type is on a separate row:

Many thanks to Robilco for providing the inspiration to this post!

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,
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["groupRownum"] = 1;
   vars["cubeColnum"] = 0;
   vars["cubeRownum"] = 3;
else {
   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.

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,
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!