Pass Dynamic column to SQL query SELECT clause from the parameter selection in Pentaho CDE

Hi Guys,

In pentaho CDE, you can pass column names dynamically to the SQL query from the selection of an input control..

Why this is useful ?
You need not to develop separate charts for another measure.
You will save the development time and re-usability of CODE.(obliviously reducing the code)
You will save the client time with a single input. 

A scenario : 
 For example in your database you have 2 measure columns let's say unit_sales and store_sales and in your sql query you need to pass these column dynamically..

For this you need to create a parameter with select component.. As these are column names you need not to worry about DataSource in select instead you will take an array of hard coded values of column names.

Let's say the parameter associated with the select is : param_Measure and the default value let's say unit_sales

Make the select to listen this parameter and give the parameter for query data source.


i.e., in select component array : give below

Arg                  value
unit_sales       Unit Sales
store_sales     Store Sales

Arg is the column name and value will appear in drop down.

Now, you need to write some thing similar to below CASE condition ..

For instance :

SELECT
        DISTINCT brand_name AS "Brand Name",
        CASE
        WHEN ${param_Measure}='unit_sales' THEN sum(unit_sales)
        ELSE sum(store_sales)
       
        END AS "Sales"
      
FROM product p
INNER JOIN sales_fact_1997 sf7
ON p.product_id=sf7.product_id
INNER JOIN time_by_day t
ON sf7.time_id=t.time_id
WHERE
t.the_date>='2012-01-01' AND t.the_date<='2012-01-07'

GROUP BY "Brand Name"

ORDER BY "Sales" DESC

LIMIT 5

Sample output images :

Column 1: store_sales Sample OUTPUT

 Column 2 : unit_sales OUTPUT

Example Download here : Click Me
(foodmart database of postgresql - A jasper server sample database).


Sadakar
BI developer
Jasper/Pentaho/Talend ETL