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();

}

NOTE: 
* 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


Sadakar
BI developer