know.bi blog

Multi-sheet Excel reports in Pentaho Report Designer

Dec 11, 2017 8:30:00 AM / by Yannick Mols

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!

Topics: pentaho, prd, excel, pentaho report designer

Yannick Mols

Written by Yannick Mols

Subscribe to Email Updates

Recent Posts