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 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
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.
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.