DateRange Component Example in pentaho CDE

Hi Guys,

A small work out on Date Range component in pentaho CDE.

• As shown in images create 2 parameters of "Date Parameters" type from Generic section of Components Panel. 
• lets say one for start date and other for to date.
• Give default values for both of them.
• Click on "Date Range input componet" from selects section of Components panel. 
• These date parameters you have to use in your query and the query dates should be converted to String type before you use.
• i.e., Sample query written in postgresql(foodmart db of jasperserver) is

SELECT p.brand_name "Brand Name",
    SUM(sf7.store_sales) "Store Sales",
    --SUM(sf7.store_cost) "Store Cost",
    SUM(sf7.unit_sales) "Unit Sales"
FROM sales_fact_1997 sf7
INNER JOIN product p ON sf7.product_id=p.product_id
INNER JOIN time_by_day t ON sf7.time_id=t.time_id
INNER JOIN customer c ON sf7.customer_id=c.customer_id
WHERE
to_char(t.the_date,'YYYY-MM-DD') >= ${param1_FromDate}
AND to_char(t.the_date, 'YYYY-MM-DD') <= ${param2_ToDate}
AND c.country=${param3_Country} AND c.state_province=${param4_State}
GROUP BY p.brand_name
ORDER BY 2 DESC,3 DESC
limit 5

Observations:
My default values for start date and end date are :  2012-01-01 and 2012-01-07

Test 1 : Default values

Test 2 : Today

 

Test 3 : Date Range

In this way you can make use of Date Range Component.

Thank you.