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.
In 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.
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.
If you click the ok button of this alert box then you will see the report like this
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.
Add message to No Results View
Now check the Report in Dashboard with proper validaton and Message:
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.
