RSS

Date validation for Dash board prompt Calendar control

13 Apr

One the question you see regularly on the forums is how to validate date in dashboard prompt if more important with the use of the calendar control.

calendar-inputs2In OBIEE the Date validation for Calendar control is not happening when user tries to enter any invalid dates (i.e. Dates in invalid formats and strings).otherwise if you select from the calendar pop up window it works good.

calendar-error1

Because of this date column mismatching at date data type we are getting error in our Reports.

So to make it fair we need to handle the calendar control input values if the user enters directly.

For this we have to change the calendar control functionality when the user hits the ‘Go” button in dashboards as Go button is sending our prompt input values to reports.

In OBIEE the GO button functionality was defined in globalfilterprompt.js. It is located in two locations

1. <install_dir>\OracleBi\web\app\res\b_mozilla\prompts

2. <install_dir>\OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\res\b_mozilla\prompts

For example let’s validate the date to dd/mm/yyyy or dd-mm-yyyy format

Open globalfilterprompt.js and search for the GFPFixDateTime() function it is the function which handles the calendar control date.

function GFPFixDateTime(sDate, sPrefix)

{

 if (/*(sDate == ksDropDownUnspecified) || */(sDate == ksDropDownAllChoices) || (sDate == ksEditBoxAllChoices))

  return sDate;

 // check for null or just whitespace

 else if (sDate == null || sDate.search(/^\s*$/) != -1)

  return “”;

 else

  return (sDate);

}

Now here we need to change the existing behavior of this function.

Step1: Make sure that you should taken a back up of this file as if you not done this changes properly it will effects the prompts behavior and breaks the pages to work.

Step2: Comment/Delete the code (here I made it in Bold) in this function GFPFixDateTime(sDate, sPrefix)

Step3: Add the following code to  function GFPFixDateTime(sDate, sPrefix)  and now your code should look like this:

 function GFPFixDateTime(sDate, sPrefix)

{

////////////////////////SHIVA DATE VALIDATION CODE/////////////////////////////////////////////

var sDisplayDate = “31/12/1960”;  //To make this date as default date if  you entered wrong format date //

var datePat = /^(\d{1,2})(\/|-)(\d{1,2})(\/|-)(\d{4})$/;  //Validating the date with Regular expression//

var matchArray = sDate.match(datePat);

if (matchArray == null) {

alert(“Please enter date as either dd/mm/yyyy or dd-mm-yyyy.”);

return sDisplayDate;

}

day = matchArray[1];

month = matchArray[3];

year = matchArray[5];

if (month < 1 || month > 12)  //To validate month number in date//

{

alert(“Month must be between 1 and 12.”);

return sDisplayDate;

}

if (day < 1 || day > 31)   //To validate Day no in Date//

{

alert(“Day must be between 1 and 31.”);

return sDisplayDate;

}

if ((month==4 || month==6 || month==9 || month==11) && day==31) //To validate correct no of days in month//

{

alert(“Month “+month+” doesn’t have 31 days!”)

return sDisplayDate;

}

if (month == 2)  //To validate  no of days in Feb month based on year//

{

var isleap = (year % 4 == 0 && (year % 100 != 0 || year % 400 == 0));

if (day > 29 || (day==29 && !isleap)) {

alert(“February ” + year + ” doesn’t have ” + day + ” days!”);

return sDisplayDate;

}

}

return (sDate);

////////////////////////SHIVA DATE VALIDATION CODE/////////////////////////////////////////////

}

Step4: Make the steps 1, 2 and 3 in two locations.

Step5: Restart Oracle BI Java host and Oracle BI Presentation server and also OC4J (For better results clear your browser cache and history).

 Now open the OBI Dashboard and give the input values to calendar control.

calendar-alert1

 

 

 

 

calendar-alert2

If you click the ok button of this alert box then you will see the report like this

calendar-message12

If you observed the Date value in message it is taking date “31/12/1960”.This is because we have fixed this in Our code to make as default date for calendar

if any wrong input entered by User.(For me I don’t have data before 31/12/1960 even you should  make sure the date for which you don’t have any data)

To avoid this message add no-results view in your answers report.

 no-results

 

Add message to No Results View

no-results-message

Now check the Report in Dashboard with proper validaton and Message:

calendar-message2

In this Post I have validated the date only for dd/mm/yyyy or dd-mm-yyyy format.If you want to validate in your own format you can validate in the same way how i have done.

Advertisements
 
17 Comments

Posted by on April 13, 2009 in OBI EE

 

17 responses to “Date validation for Dash board prompt Calendar control

  1. chuppyFupt

    April 19, 2009 at 9:51 am

    great site this shivabizint.wordpress.com nice to see you have what I am actually looking for here and this this post is exactly what I am interested in. I shall be pleased to become a regular visitor 🙂

     
  2. SD

    August 2, 2009 at 6:47 pm

    Nice post Thanks!!!

     
  3. Thilak

    August 25, 2009 at 1:15 pm

    Hi Siva,

    My requirement is to have date prompt in format dd.mm.yyyy and pass this format to the request for filter. But the date format entered in the prompt is always passed on to the request as ‘mm.dd.yyyy’.

    I have tried changing nqsconfig.ini file, set the date format to dd.mm.yyyy (system wide) in answers and also tried the javascript you have provided here. Still i face the same problem.

    Please let me know where i need to change for this.

    Thanks,
    Thilak
    thilak.g@gmail.com

     
  4. Krishna

    October 8, 2009 at 1:29 pm

    Hey Shiva
    I have a requirement where users want to see date in DD-MON-YYYY format and also they want to see the same on the calendar prompt,currently it is in DD-MM-YYYY format.I have tried to change it in NQSCONFIG, Calendar.js etc but still I am unable to get the required format. Do you have a method(steps) to show this in UNIX?

    Please let me know if you have a way.

    Thanks,
    Krishna

     
  5. Neelam

    November 9, 2009 at 10:16 am

    Thanks !.. Really helpful post ,it solved my problem

     
  6. Vishal

    March 5, 2010 at 9:22 am

    Thank god I found this site…

     
  7. Ancendemi

    June 19, 2010 at 5:41 pm

    The lakers beat the boston celtics in match seven from the 2010 national basketball association finals. I dropped $150 on the game, don’t you folks bet on sports? Any kind of techniques for newbies?

     
  8. jeff

    August 23, 2010 at 7:45 am

    Hi,

    Thank you for your post and that I can solve this critical problem. However, I found that while I am using language other than “English”, e.g. Chinese, as I am from China HK, the date validation is correct but error will be shown when processing the query.

    Example, I chose “1/1/2001” and “1/2/2001”
    The message is:
    取得探鑽資訊時發生錯誤:SELECT “D0 Time”.”T00 Calendar Date” saw_0 FROM “Sample Sales” WHERE “D0 Time”.”T00 Calendar Date” BETWEEN date ‘0001-01-00’ AND date ‘0001-02-00’

    Do you know why and any solution can solve this?

    Thank you so much ~!

    Jeff

     
  9. Sugan

    September 22, 2010 at 4:08 pm

    Nice post..Keep it up..

     
  10. Jhon

    September 23, 2010 at 2:31 pm

    Great post, however I’ve tried the above and it doesnt seem to work. I updated both files with updated script, restarted all the services including OC4J but still no luck.

    I’ll put in ‘ABC’ to test and when I press go, nothing Happens

    I there anything I’m Missing?

    Thanks

    Jhon

     
  11. rupesh

    November 10, 2010 at 7:03 pm

    Hi Gurus,

    I have created one logical column and casted as an integer

    CAST ( EXTRACT( HOUR FROM DW.TABLE2.COL2) AS INTEGER )

    and attached along with date in page prompt level with greter than condition
    when i selected 10 then it is showing data related to 10 AM only and not showing time after 10 AM and PM as well
    http://forums.oracle.com/forums/thread.jspa?threadID=2127783
    it is really really urgent

    thanks

     
  12. Pradeep

    August 19, 2011 at 1:53 am

    Hi,

    Can you let me know how the same can be done in OBIEE 11. I am not able to find the above mentioned directories of the javascript files. Thanks

    Regards,
    Prad

     
  13. Nitin Aggarwal

    April 6, 2012 at 2:09 pm

    Hi Siva,

    My issue is that there are a bulk of reports which are working fine with older version of OBIEE 10g.
    I did a new installation and facing this issue. Now, its not possible to change all the reports or prompts.

    What I need is a way may be a change in some script so that date selected in calender is passed in the same format to query via presentation variable.

    Like in calender, its in the format ‘MM/DD/YYYY’ whereas in query its being passed as ‘YYYY-MM-DD’ which is creating the issue. I want the value selected in calender to be received in the format ‘MM/DD/YYYY’.

    Please help.

    Thanks & regards
    Nitin Aggarwal

     
  14. nagu

    July 20, 2012 at 7:37 pm

    Hi
    I tried your code but it is not working.I am using obiee 10.1.3.4.2 and IE 9
    Please help.

     
  15. Azmat Hussain

    June 20, 2013 at 7:45 pm

    Hi dear

    same date validation is required in OBIEE 11g .Please help any body.it’s urgent

    Azmat Hussain

     
  16. Laxmi Goswami

    May 29, 2014 at 11:46 am

    Hi,

    Same date validation is required in OBIEE 11g .Please help .

    Thank you

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: