. Transpose rows into columns in BIRT Report
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.