Multi-sheet Excel reports in Pentaho Report Designer

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’).


Multi-sheet Excel reports in PRD - standard banded report


Let’s use a table datasource to store the sheet names and create a parameter which we’ll use to switch between the sheets.


Multi-sheet Excel reports in PRD - sheet name parameters


All’ will be used to export all sheets.


Multi-sheet Excel reports in PRD - Sheet name parameters


When we click preview it should show the following. 

Multi-sheet Excel reports in PRD - tabbed report


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. 

Multi-sheet Excel reports in PRD - visibility settings


Multi-sheet Excel reports in PRD - sheet visibility formula


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.


Multi-sheet Excel reports in PRD - bands per sheet


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.

Multi-sheet Excel reports in PRD - multi-tab report, ready to export to Excel


And that's Excel sheets in Pentaho Report Designer!



A demo of this setup is available here!


  Talk to an expert!

You may also like

These blogs about pentaho

As explained in a previous post, has been working with Matt Casters (Neo4j) and our growing community of coders, testers, documentation writers and…

On May, 16th 2018, Hitachi Vantara released Pentaho 8.1 Although this is a minor follow-up release to 8.0 as far as version numbers go, but nevertheless a lot…