Pentaho Report Designer - Multi-sheet Excel Reports
When you’re designing a report with Pentaho Report Designer (PRD) you may want it to contain sheets like Excel does. For instance we want interactive sheets and the ability to correctly export to Excel. Sadly this isn’t the easiest task, but here’s one of the ways you can get it to work.
First we’ll create a report with 3 bands, add a label to each of them (make sure the layout is set to ‘Block’).
Let’s use a table datasource to store the sheet names and create a parameter which we’ll use to switch between the sheets.
‘All’ will be used to export all sheets.
When we click preview it should show the following.
Next we’ll add some PRD magic: each sheet has a ‘visible’ setting, click on the plus sign and add the following code to each band.
Change the first number to match the current band/sheet (leave the zero as is).
Now when we preview you should be able to select your wanted sheet.
When we want to export the report to Excel you’ll need to explicitly state each sheet name, you can do this by adding a value for the sheetname property for each of the bands.
When you export the report it will let you choose which sheet you want.
Now you can simply add anything you want to each band, but I suggest using sub-reports within each band. If you want more sheets simply add to the table and re-iterate these steps.
And that's Excel sheets in Pentaho Report Designer!
A demo of this setup is available here!