This article demonstrates how you can integrate Pentaho Data Integration (Kettle) and Pentaho Report Designer (PRD) with CTOOLS(CDE,CDF,CDA etc) and export the integrated data in Excel, CSV and PDF.
Having been able to do data manipulation tasks using PDI Kettle and use it as data source in CDF/CDE and Designing pixel perfect report using Pentaho Reporting Engine is useful skills in any projects. In this article I wanted to share my experiences how we can integrate these two systems with CTOOLS for creating dashboard and to do further data analysis.
For anyone not familiar with PDI Kettle & Pentaho Reporting I would recommend you to visit community site here.
Both are great projects and I was trying these projects for some of my BI requirement last week.HL7 data support, Web services support, Talend job support and custom java code support were some of the striking features that motivated me to explore more in Kettle project. There are some great samples for you to look at if you want to explore more on what these projects are capable of how it can help your project.
If you download there project from this wiki site,
it will basically create folders viz. data-integration and report-designer. If we go one step inside you will be able to see samples folders. If you just try these samples out it will give you good technical overview what each project does.
For my PDI data source integration with CDE I am using this sample as shown in screen shot.
You can open this sample in PDI and execute and see results and double click and see what each step does. I am focusing on the integration this sample with CDE data source so let me start with that.
Basically to use this ETL job as data source in CDE you just require defining three properties as shows in screen shot viz. Name, Kettle Transformation file and Query section you define a ETL step that you want to work as data source. The last part is tricky since it’s the ETL step name that will act as query which wasn’t that initiative for me to figure out.
Once you do that you can define a row using Layout and use table component and hook these three things together which you can refer from downloadable source code that I will provide you in this article and we will be able to see that data in CDE as shown in screen shot.
Next part I am trying to introduce is the three kind of export options (PDF, Excel, CSV) using CDE. For Excel and CSV part you can just use the java script and export this table data defining a small java script function in Button component under Expression property.
For the PDF part I am using the same Kettle transformation file as data source in PRD and designing the report there and using in CDE as PRPT component.
In the screen shot above I am defining the PDI data source as same transformation file in CDE and using those four columns as parameters highlighted yellow. Then I will publish this report to pdf folder under my cde project as shown in following screen shot.
Now if I click the pdf button then I will be able to see the pdf I designed in PRD in CDE as shown in the following screen shot.
I hope this solution is simple and powerful enough to help you explore more in CTOOLS, PDI and PRD integration. Please download the source code to give it a shot.
This article demonstrates with a demo application how you can integrate the PDI and PRD component with CTOOLS to get best of all three components to solve your business need. Hope you enjoyed reading and were useful in your exploration of your BI development and research.
Pentaho Reporting 3.5 for Java Developers by Will Gorman
Pentaho Data Integration 4 Cookbook by Adrian & Maria
Pentaho Kettle Solutions by Matt,Roland,Jos van