Pentaho CDE SQL query

Pentaho CDE SQL query  :the implementation of dynamic SQL queries in open source reporting engine Pentaho, JasperSoft and BIRT.
 

 A dynamic query - to put it simply - it is the ability to pass a parameter in a SQL statement other than in the WHERE clause, eg to enter the parameter (s) columns you want to return.

Let's take a simple example based on SampleData basic Pentaho to implement the following dashboard with Pentaho CRC:

This dashboard contains 2 parameters "Country" and "Measure", the second allows you to select the column where to get the information (or TotalPrice QUANTITYORDERED).

One would imagine the corresponding SQL query to create a DataSource type CDE sqlJndi :

SELECT CUSTOMERS.COUNTRY AS PAYS, PRODUCTS.PRODUCTLINE AS GAMME_PRODUIT, ORDERFACT.YEAR_ID AS ANNEE, SUM(ORDERFACT.${param_mesure}) AS MESURE FROM PRODUCTS INNER JOIN ORDERFACT ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE INNER JOIN CUSTOMERS ON ORDERFACT.CUSTOMERNUMBER = CUSTOMERS.CUSTOMERNUMBER WHERE CUSTOMERS.COUNTRY = '${param_pays}' GROUP BY PAYS, GAMME_PRODUIT, ANNEE

Bad luck, it does not work, as confirmed by Pedro Vale (WebDetails) in this forum entry: "parametrized SQL clause in CDE":-(
So how?

=> As so often, it's Kettle will save the day thanks to its dynamic SQL query management capacity

1. Creating a Kettle transformation set :

Note that if the connection specified in the Kettle transformation is based on a JNDI source, it will be used Pentaho server side (if it exists!)

2. Creating a datasource type "Kettle" in the dashboard:

Select the Kettle transformation then step "output" responsible for data recovery:

Fill in the "param_mesure" settings and "param_pays" in the "Variables" property AND "Parameters".

You can then test the operation of the CDA request by filling in the settings in the area for this purpose:

3. Using the query in the table:

He just set the kettle query as datasource "picture" component, not forgetting to specify Listeners and Parameters: