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

Saturday, 24 August 2013

Extract year,quarter,month & day from date input control in pentaho CDE using java script - MDX Query Scenario

Hello guys...!!

Some times you need to extract the parts(year,month,day) of Date for some specific use..
For example:
Assume you are creating a report with MDX query which has dimension called "Date" having levels "Year", "Quarter","Month" & "Day".

(Note : Assume your schema is having
          Year: yyyy Quarter: 1 or 2 or 3 or 4 Month : 1,2,3,4.......12  Day : 1,2,3.... 31)

Also assume you do not have direct date dimension in your schema (i.e, you do not have a dimension which takes 'yyyy-MMM-dd' column.

But, you need to display date(yyyy-QQ-MMM-dd) or (yyyy-MMM-dd) on X-axis of chart.. Remember you are not having any direct date in your schema but have "Date" with year,quarter,month & day as levels.

From your start_date(or end_date) input control you can extract the individuals using the following java script for CDE and use them properly in your MDX date range place.

This should be done in "Pre Execution" section of Chart component

function extract_function(){
    tmp_date = new Date(param_start_date);
    var quarter = ['Q1','Q2','Q3','Q4'];
    var month = ['JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC'];
    param_start_year = tmp_date.getFullYear();
    param_start_quarter = q[Math.floor((tmp_date.getMonth()+ 3) / 3)-1];
    param_start_month = m[tmp_date.getMonth()];
    param_start_day = tmp_date.getDate();
    tmp_date = new Date(param_end_date);
    param_end_year = tmp_date.getFullYear();
    param_end_quarter = q[Math.floor((tmp_date.getMonth()+ 3) / 3)-1];
    param_end_month = m[tmp_date.getMonth()];
    param_end_day = tmp_date.getDate();


* quarter and month variables are taken as arrays with default values.
* You need to calculate the month and send it to array ..
    When you calculate months
    1 becomes JAN, 2 becomes FEB and etc as well
    When you calculate quarters
 1 becomes Q1, 2 becomes Q2, 3 becomes Q3 & 4 becomes Q4

BI developer