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

Forget about your problems …!!!! and Meet us @   http://www.helicaltech.com/contact.php


BI developer


1 comment

  1. Faby

    I have to vehemently dsrigaee with dbunic. Javascript’s automatic translation of “1/32/2012” to “2/1/2012” is NOT a nice feature, it is a nightmare! It makes writing a generic isValidDate(sDate) function nearly impossible. E.g.:These two statements are perfectly fine:(new Date(“10/30/1961”)).toString() -> ‘Mon Oct 30 00:00:00 CDT 1961′(new Date(“October 30, 1961”)).toString() -> ‘Mon Oct 30 00:00:00 CDT 1961’How the heck do I determine that “October 32” is invalid?(new Date(“October 32, 1961″)).toString() -> ‘Wed Nov 1 00:00:00 CDT 1961′”October -3” means October 3? Are you kidding me?(new Date(“October -3, 1961”)).toString() -> ‘Tue Oct 3 00:00:00 CDT 1961′(new Date(“10/-3/1961”)).toString() -> ‘Tue Oct 3 00:00:00 CDT 1961’You can’t be serious:(new Date(“13/1/1961”)).toString() -> ‘Mon Jan 1 00:00:00 CST 1962’Looks to me like some programmer decided to be “helpful” when designing the Date object. And since I can’t control the date format that someone may use, I have to program around all this “helpfulness”. It drives me insane!

Leave a Reply