It is easy to transpose your data in database. You won’t need to change the database table, just the query to get the data. It will depending on your database type and version whether it supports this feature.

But If you are using BIRT RCP as an administrator and are not a Java developer, and if the application is a black box to you, exporting .csv files as a result of a query, you have to work from is the .csv which does not allow sql querying. You will have to find a solution outside of BIRT.

For example, given the following data:

empID       2005        2006        2007
------      ------      -------     -------
1           12000       18000       25000
2           15000       6000        0
3           0           20000       24000

The desired output is:

empID       yr          sales
-------     -------     -------
1           2005        12000
1           2006        18000
1           2007        25000
2           2005        15000
2           2006        6000
2           2007        0
3           2005        0
3           2006        20000
3           2007        24000

Using esProc with BIRT, it’s very simple. Here is the SPL script:

It is not complicated to use the database for static transposition. For whatever reason (complex query is prohibited or only static data is provided), when no database is available, the report developer can only seek the solution in the report. To deal with simple problems, a little more complicated must be implemented manually in Java. The problem that esProc has to solve is to make this part of the manual implementation very simple, and it can be easily combined with reports. These calculations are easy to write with SPL. For more explanations and examples, please refer to Transposition.

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