Transpose Rows in BIRT

mhammer

Hello,
Does anyone know a good way to transpose rows into (concatenated) columns in BIRT, without using the Pivot table?

For example given the following data:

Category1 Item1
Category1 Item2
Category2 Item3
Category3 Item4
Category3 Item5
Cateogry3 Item6

the desired output is:

Category1 Item1;Item2
Cateogry2 Item3
Category3 Item4;Item5;Item6

This can be done on database level (Oracle) using the CONNECT BY clauses, but in our case the cost of doing so is prohibitive, so we want to do it in BIRT.

Thanks for any ideas.

johnw

It won't be easy. You will have to have 2 tables, the first is the one that will iterate over the dataset, and it will populate a HashMap<String, ArrayList>. It will also be hidden.

The second table will be based on a scripted data set you are going to have to make an assumption that ArrayList won't ever be more than a certain size (say 6 for this example). Your table will look something like:

Column1, Col2, col3, col4, col5, col6, Col7
Key ArrayList(0), ArrayList(1), ArrayList(2), ArrayList(3), ArrayList(4), ArrayList(5)

Then, you set a visibility expression on each column that will look something like:
(ArrayList(col) == null) //setting to hide column if this is null

Thats the basic idea.