Pentaho Tools :

Pentaho C-Tools(CDE,CDF,CDA),Pentaho CE & EE Server,OLAP-Cubes,Analysis using Pivot4J, Saiku Analytics, Saiku Reporting, Ad-hoc Reporting using Interactive Reporting Tool,Dashboards,Reports using PRD, PDD,Data Integration using Kettle ETL,Data Mining usign WEKA,Integration of Servers with Databases,Mobile/iPad compatible Dashboards using Bootstrap Css,Drilldown dashboards,Interactive Dashboards

Thursday, 17 April 2014

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.