Creating drill down on charts within Report Designer

Report Designer has the capability of creating charts which you can link the individual values to other reports, creating a drill down chart. This document will help you to create a working example of this using the sample data that comes with Pentaho in addition to a canned sample report that comes with Pentaho (the Order Status report located under the Steel Wheels > Reporting solution folder).

1. Create a new report with Report Designer and add a JDBC connection with the Sample Data and enter the following SQL into the Query Dialog Box.SELECT "ORDERFACT"."STATUS", sum("CUSTOMER_W_TER"."CREDITLIMIT") AS Sales FROM "ORDERFACT" INNER JOIN "CUSTOMER_W_TER" ON "ORDERFACT"."CUSTOMERNUMBER" = "CUSTOMER_W_TER"."CUSTOMERNUMBER" WHERE "ORDERFACT"."STATUS" NOT LIKE 'Shipped' GROUP BY "ORDERFACT"."STATUS" ORDER BY "ORDERFACT"."STATUS" ASC
   
   

1. Add a chart to the Report Header in your new Report and configure it to be a bar chart showing the sales per order status

1. Link to the existing Order Status report located on the BI Server under Steel Wheels\Reporting folder
   
   
   
   Make sure the formula reads as follows:
   
   =DRILLDOWN("local-prpt"; NA(); {"oStatus"; ["chart::category-key"] | "showParameters"; [STATUS] | "solution"; "steel-wheels" | "path"; "reports" | "name"; "Order Status.prpt"})

Preview the Report in HTML and click on the "On Hold" bar to see the drilldown action:

Download the sample .prpt file by clicking here.

or watch the demonstration:

Pentaho reporting and canned reports for distributed solutions

So recently I came across a use case that I helped someone work through that I thought my audience would find useful. Here it is;

Use Case

A company wants to offer Pentaho as the BI Application with their current solution. As part of this solution they want to create some canned reports that has the logo of the customer that is using their solution. Each customer will have the same report, because it is a report that is common across all their customers, however, they want the logo on the report to be that of the customer. Additionally, the solution is installed on premise at each individual customer site so the location of their solution in relation to the web application will be different for each customer (in other words, the installation and configuration of the Pentaho BI Server is different at each customer). In order to accomplish this in Report Designer, they must do the following steps:

1. Place an image in the report
   
   
2. Put the location of that image as an Open Formula consisting of: =[env::serverBaseURL] & "/pentaho/GetResource?resource=solution/folder_path/image_name.png

Here is a video presentation demonstrating this: