3/30/15

Pentaho CDE SQL query

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

 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 :

dynamic_sql_cde-02
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:
dynamic_sql_cde-03
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:

dynamic_sql_cde-04

3. Using the query in the table:

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

0 commentaires:

Post a Comment