For example, given the following data:

Category  Item
-------   -----
Category1 Item1
Category1 Item2
Category2 Item3
Category3 Item4
Category3 Item5
Cateogry3 Item6

The desired output is:

Category  Item
-------   -------
Category1 Item1;Item2
Cateogry2 Item3
Category3 Item4;Item5;Item6

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

Here is the basic idea, if you want just to use BIRT:

You will have to have two 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

But if you use esProc with BIRT. It’s very simple. Here is the SPL script:

Compare SPL with stored procedures.

The original data is:

id   name     subject  grade
---- ------   -------  -------
1    Jerry    Chinese  80
1    Jerry    Math     90
1    Jerry    Physics  85
2    Thomas   Chinese  85
2    Thomas   Math     92
2    Thomas   Physics  82

The desired output is:

id   name      Chinese   Math      Physics   Total     RTotal    RChinese  RMath     RPhysics
--   --------  --------  --------  --------  --------  --------  --------  --------  --------
1    Jerry     80        90        85        255       2         2         2         1
2    Thomas    85        92        82        259       1         1         1         2

The implement using SQL Server stored procedure:

declare @sql nvarchar(4000),@sql1 nvarchar(4000)
select @sql='',@sql1=''
select @sql=@sql+',['+subject
	+']=sum(case subject when '''+subject+''' then grade else 0 end)'
	,@sql1=@sql1+',[R'+subject+']=(select sum(1) from # where ['
	+subject+']>=a.['+subject+'])'
from(select distinct subject from #t) a
exec('select id ,name '+@sql+',Total=sum(grade)
,RTotal=(select sum(1) from(select id,aa=sum(grade) from #t group by id) aa where sum(a.grade)<=aa)
into # from #t a group by id,name
select *'+@sql1+' from # a
')

The implement using esproc:

It’s not easy to write a stored procedure. The principle is that you should use as few stored procedures as possible. esProc helps cut down the procedures in the database greatly. An algorithm will be stored and managed along with the report template in the file system and become a part of the reporting module. This will reduce its coupling with the other parts of the application while won’t add more coupling with other applications. For more explanations and examples, please refer to Transposition.

For detail esProc integration with BIRT, see How to Call an SPL Script in BIRT.