Sunday, June 21, 2009

BIRT Subreport Tutorial

Say you have a list of BSCs and you want to display information for each BSC. You have a query that takes input as BSC. And another query that list your BSCs. Here's how to merge the two using BIRT.

First your BSC query:
select bsc from rx_bsc_region order by BSC asc

Create the query using BIRT as shown. Nothing fancy to do here. Just a simple query:

Now simply drag and drop this query into the layout to create a table:
You might want to make the [BSC] body section bolder to highlight BSC sections in the table. Also add a row below the BSC using right clicking:

You might want to add another detail row in case you want multiple sub tables each with a different header:

Now you can make a query that takes BSC as input . e.g. :
Select OSS_CELL_AVGVIEW.CELL , OSS_CELL_AVGVIEW.bsc ,(OSS_CELL_AVGAVG.f_tch_availability- OSS_CELL_AVGVIEW.f_tch_availability ) as TCHAvailDec from oss_CELL_AVGview INNER JOIN OSS_CELL_AVGAVG ON (OSS_CELL_AVGVIEW.CELL = OSS_CELL_AVGAVG.CELL) WHERE OSS_CELL_AVGVIEW.thedate > (sysdate -2) and (OSS_CELL_AVGAVG.f_tch_availability- OSS_CELL_AVGVIEW.f_tch_availability ) > 1 and bsc = :BSC ORDER BY TCHAvailDec DESC

Notice the bsc = :BSC. A a query parameter as you like:

Preview results to check everything is working fine and click ok.

Next drag and drop this table into the detail row you added earlier. You should get:

Now select the newly added table and select the binding tab. Click on Dataset parameter binding. You should see the parameter binding input as shown:

Finally modify it (using edit button) to point the parameter to the running detail row value as shown:

Click ok... You are all set. Preview to see the result:

So in short:
  • make the main query
  • make the sub query using a constant parameter
  • Make the main table
  • Drag the sub table into a new detail row.
  • Link the sub tables dataset parameter to the main row detail using : row["columnname"] format