How do I do my Pentaho Report take the Excel datasource file along to BA/BI Server?

Question

I want to create a Pentaho Report (with Report Designer) based on an Excel file. However, I want to have this Excel file kind of generic and select a specific subset from it using a Pentaho Data Integration (Kettle) transformation.

I can make it work with Pentaho Report Designer: I've created a transformation data source, which in its turn reads a Excel file, all three files (report, transformation and Excel) being in the same directory.

How do I get this to work when published to the BA(BI) Server?

I have followed this answer (Using PDI transformation in Pentaho BI server as data source for report parameters) to have my transformation loaded by Pentaho BA Server, so now I can have a report based on a transformation that loads from the very Report Designer file itself. My question is how I bring the Excel file being used as source for the transformation along? I tried to include it as a resource and reference it as a filename without path in the transformation, but it have not worked.

Answer 1

This one's tricky, as you need to refer to the file inside the PRPT (which is basically a ZIP file). Here's a procedure that worked for me:

1) Add the Excel (or whatever) file as a Resource in Pentaho Report Designer (PRD). For my test, I used the customers-100.txt file from the PDI samples, and named the resource "customers-100". Then I saved the report as prd_test.prpt.

2) Open Spoon and create a new PDI transformation in the same directory as the PRPT. I used a Text File Input and for the filename, I put the following:

zip:${Internal.Transformation.Filename.Directory}/prd_test.prpt!customers-100

The "zip" is a VFS scheme and tells PDI it's a ZIP file. The variable is the transformation location (should be the same as the PRPT), and the bang (!) tells VFS to go into the PRPT package and look for a file called customers-100 (the name of the resource). I think I also had to tell it Mixed line-endings and US-ASCII encoding, I don't know if these are really necessary or if they will apply to your Excel step.

3) Open prd_test.prpt in PRD, add the transformation as a resource (I named it prdTest), and create a query against the resource name (prdTest in this case, NOT the filename). Save the PRPT, close PRD, and restart.

I didn't try this for a published report, I was just working from PRD.

If you run into errors and the console says something like "Stream closed", try restarting PRD and/or re-importing your resources.