Setting up a Date Range parameter

How does your query look like? Are you using a to_date construct?

The ORA error means you're using the wrong date format string

Thanks for the response.

This is the query:

select DATABASE.TABLE.INCIDENT_NUMBER, DATABASE.TABLE.TIMEOFCALL, DATABASE.TABLE.TYPE
from DATABASE.TABLE

Output Columns:
Name Type
INCIDENT_NUMBER String
TIMEOFCALL Date Time
TYPE String

Example of data:
INCIDENT_NUMBER TIMEOFCALL TYPE
1234 01/04/2009 00:11:38 Lost
1235 02/03/2009 13:24:15 Lost
1236 29/03/2009 09:35:42 Found

This is the script I’m using on BeforeOpen (as modified from the report I linked in my previous post):

today = new Date();
df = new Packages.java.text.SimpleDateFormat("yyyy-MM-dd");
today = df.format(today);

if (params["Date Range"].value == "Custom Range"){
this.queryText = this.queryText + " where DATABASE.TABLE.TIMEOFCALL between '" + params["StartDate"].value + "' and '" + params["EndDate"].value + "'";
}
else{
if (params["Date Range"].value == "Last 6 Months"){
rangeStart = DateTimeSpan.subDate(today, 0, 6, 0);
}
else if (params["Date Range"].value == "Last 12 Months"){
rangeStart = DateTimeSpan.subDate(today, 0, 12, 0);
}

rangeStart = df.format(rangeStart);

this.queryText = this.queryText + " where DATABASE.TABLE.TIMEOFCALL between '" + rangeStart + "' and '" + today + "'";
}

You are comparing a DATE database column to string values... try replacing the lines where you add the where clause by these:

this.queryText = this.queryText + " where DATABASE.TABLE.TIMEOFCALL between to_date('" + params["StartDate"].value + "', 'yyyy-mm-dd') and to_date('" + params["EndDate"].value + "', 'yyyy-mm-dd')";

and

this.queryText = this.queryText + " where DATABASE.TABLE.TIMEOFCALL between to_date('" + rangeStart + "', 'yyyy-mm-dd') and to_date('" + today + "', 'yyyy-mm-dd')";

Please try both cases, I mean: try Custom range as well as Last 6 Months or Last 12 Months and see if one of them works now.

If it still doesn't work, can you tell me what the exact values of params["StartDate"].value, params["EndDate"].value, rangeStart and today are?

Success!

That worked, thanks for the help, it's very much appreciated

Following on from my original query, I would like to include year to date selection options in the drop down box; one for Calendar ytd and one for Financial ytd.

I tried including the following in the script for calendar ytd:

var today = new Date();
var year = today.getFullYear();

else if (params["Date Range"].value == "Calendar Year to Date"){
rangeStart = "01/01" +year;
}

Unfortunately it didn't work; I'm guessing there is probably a formatting issue.

Any help with the script required to do this would be much appreciated.

seems to be the same issue as before: You can't compare a string with a column that has datatype DATE
when working with dates i always use java.util.Calendar as it's a simple API to interact with a java date.

I see that you decided to use the to_date function of your database instead of setting the right datatype so the issue is that the to_date function has format parameter yyyy-mm-dd but you set rangeStart to dd/mm/yyyy format.
Just change

else if (params["Date Range"].value == "Calendar Year to Date"){
rangeStart = "01/01" +year;
}

to

else if (params["Date Range"].value == "Calendar Year to Date"){
rangeStart = year+"-01-01";
}

But again: I would prefer to work with the correct datatype instead of using the parse function to parse strings to dates using your database system.

hope this helps

Thanks for the response CBR, I tried

else if (params["Date Range"].value == "Calendar Year to Date"){
rangeStart = year+"-01-01";
}

but the script still failed to execute.

I used the to_date function, as it was suggested previously by Hans_vd.

I have no background/training in scripting and I'm not sure how to use java.util.Calendar to get the required results.

Do you get a error message?

Let's try to fix the problem using the to_date function because you already built the report using string parameters

I get the following error message when I try to preview the report having selected the “Calendar Year to Date” option (the report still works if I select one of the other date range options):

Failed to execute the report
Error Description: There are errors evaluating the script

The script I’m using is as follows:

today = new Date();
year = today.getFullYear()
df = new Packages.java.text.SimpleDateFormat("yyyy-MM-dd");
today = df.format(today);

if (params["Date Range"].value == "Custom Range"){
this.queryText = this.queryText + " where VISION.IRS_INCIDENT.TIMEOFCALL between to_date('" + params["StartDate"].value + "', 'yyyy-mm-dd') and to_date('" + params["EndDate"].value + "', 'yyyy-mm-dd')";
}
else{
if (params["Date Range"].value == "Last 6 Months"){
rangeStart = DateTimeSpan.subDate(today, 0, 6, 0);
}
else if (params["Date Range"].value == "Last 12 Months"){
rangeStart = DateTimeSpan.subDate(today, 0, 12, 0);
}
else if (params["Date Range"].value == "Calendar Year to Date"){
rangeStart = year+"-01-01";
}

rangeStart = df.format(rangeStart);

this.queryText = this.queryText + " where VISION.IRS_INCIDENT.TIMEOFCALL between to_date('" + rangeStart + "', 'yyyy-mm-dd') and to_date('" + today + "', 'yyyy-mm-dd')";
}

Sorry i was wrong. Seeing the full script i noticed that rangeStart is alrady of type date and not of type string.
You parse it to a string just before you build the query text:

else if (params["Date Range"].value == "Calendar Year to Date"){
rangeStart = year+"-01-01";
}

rangeStart = df.format(rangeStart); //this line causes problems because rangeStart is a string if Calendar ... was choosen

To make the whole script work we have to provide a date object for rangeStart if Calendar Year to Date was choosen.

To do that change:

else if (params["Date Range"].value == "Calendar Year to Date"){
rangeStart = year+"-01-01";
}

to

else if (params["Date Range"].value == "Calendar Year to Date"){
importPackage(Packages.java.util):
calendar = Calendar.getInstance();
calendar.setTime(today):
calendar.set(Calendar.DATE, 1);
calendar.set(Calendar.MONTH, Calendar.JANUARY);
rangeStart = calendar.getTime();
}

Hope it works because i couldn't give it a try atm.

Thanks for the response.

I tried this, but it's still saying "There are errors evaluating the script"

If the code is copy / paste, then you have forgotten to put a ';' after the getFullYear function

Thanks Hans, but that code didn't work for Calendar Year to Date even with the ';', I'm guessing for the reason that CBR stated; that I need to provide a date object for rangeStart.

I modified the script as follows to include the code suggested by CBR:

today = new Date();
df = new Packages.java.text.SimpleDateFormat("yyyy-MM-dd");
today = df.format(today);

if (params["Date Range"].value == "Custom Range"){
this.queryText = this.queryText + " where VISION.IRS_INCIDENT.TIMEOFCALL between to_date('" + params["StartDate"].value + "', 'yyyy-mm-dd') and to_date('" + params["EndDate"].value + "', 'yyyy-mm-dd')";
}
else{
if (params["Date Range"].value == "Last 6 Months"){
rangeStart = DateTimeSpan.subDate(today, 0, 6, 0);
}
else if (params["Date Range"].value == "Last 12 Months"){
rangeStart = DateTimeSpan.subDate(today, 0, 12, 0);
}
else if (params["Date Range"].value == "Calendar Year to Date"){
importPackage(Packages.java.util);
calendar = Calendar.getInstance();
calendar.setTime(today);
calendar.set(Calendar.DATE, 1);
calendar.set(Calendar.MONTH, Calendar.JANUARY);
rangeStart = calendar.getTime();
}

rangeStart = df.format(rangeStart);

this.queryText = this.queryText + " where VISION.IRS_INCIDENT.TIMEOFCALL between to_date('" + rangeStart + "', 'yyyy-mm-dd') and to_date('" + today + "', 'yyyy-mm-dd')";
}

When the 'Calendar Year to Date' option was selected for preview however, I got the following error message:

Report generation failed. Cause: Fail to compute value for computed column “Year”.
A BIRT exception occurred: There are errors evaluating script “row[“TIMEOFCALL”].getFullYear()”:
TypeError:Cannot find function getFullYear.(<inline>#1)..See next exceptionfor more information.
There are errors evaluating script”row[“TIMEOFCALL”].getFullYear()”:
TypeError:Cannot find function getFullYear.(<inline>#1).

hi Sarah,

the error is not in the script. The error message you posted below belongs to the script to calculate the value of a computed column in your report called "Year".
This computed column is calculated by expression: row[“TIMEOFCALL”].getFullYear();
I assume that the database column TIMEOFCALL is of type DATE or DATETIME.
This means that row[“TIMEOFCALL”] is of Java type java.util.Date.
The java.util.Date doesn't have a function like getFullYear (this is what the error message is saying).

Are you seeing the error only when 'Calendar Year to Date' was selected?

One of the computed columns had an incorrect data type, which I've now ammended. The error was only occuring when 'Calendar Year to Date' was selected however.

Now when I try and preview the report with 'Calendar Year to Date' selected it's just saying:

Failed to execute the report
Error Description: There are errors evaluating the script

there must be a + sign in the error message. When clicking on the + you can expand the error message. The short message seems to be useless in this case because it doesn't even say where the error is occuring.

I'm afraid the rest of the message just copies the code and isn't very helpful (see attachment).

I thought perhaps there was an issue with:

calendar.setTime(today);

so I tried:

else if (params["Date Range"].value == "Calendar Year to Date"){
importPackage(Packages.java.util);
calendar = Calendar.getInstance();
calendar.set(Calendar.SECOND, 0);
calendar.set(Calendar.MINUTE, 0);
calendar.set(Calendar.HOUR_OF_DAY, 0);
calendar.set(Calendar.DATE, 1);
calendar.set(Calendar.MONTH, Calendar.JANUARY);
rangeStart = calendar.getTime();
}

It seems to work now.

Hi,

shame on me.

I didn't see that you format your variable today to a string in line 2. I thought it's a java.util.Date.

The error was in:

else if (params["Date Range"].value == "Calendar Year to Date"){
...
calendar.setTime(today); //today is a string here but i thought it's a date
...
}

simply change this line to:

calendar.setTime(new Date());

Of course you don't have to set a date object. You can interact with the calendar like you do to set day, seconds, year, month. So your solution is just fine and works!

I just wanted to say thank you for all the help both of you have given me in solving this problem.

The report user is now able to select from the following options:
- Custom Range
- Last 6 Months
- Last 12 Monoths
- Calendar Year to Date
- Financial Year to Date

The final code I've used is as follows and seems to work:

today = new Date();
yy = today.getYear();
year = (yy < 1000) ? yy + 1900 : yy;
df = new Packages.java.text.SimpleDateFormat("yyyy-MM-dd");
today = df.format(today);

if (params["Date Range"].value == "Custom Range"){
this.queryText = this.queryText + " where VISION.IRS_INCIDENT.TIMEOFCALL between to_date('" + params["StartDate"].value + "', 'yyyy-mm-dd') and to_date('" + params["EndDate"].value + "', 'yyyy-mm-dd')";
}
else{
if (params["Date Range"].value == "Last 6 Months"){
rangeStart = DateTimeSpan.subDate(today, 0, 6, 0);
}
else if (params["Date Range"].value == "Last 12 Months"){
rangeStart = DateTimeSpan.subDate(today, 0, 12, 0);
}
else if (params["Date Range"].value == "Calendar Year to Date"){
importPackage(Packages.java.util);
calendar = Calendar.getInstance();
calendar.set(Calendar.SECOND, 0);
calendar.set(Calendar.MINUTE, 0);
calendar.set(Calendar.HOUR_OF_DAY, 0);
calendar.set(Calendar.DATE, 1);
calendar.set(Calendar.MONTH, Calendar.JANUARY);
rangeStart = calendar.getTime();
}
else if (params["Date Range"].value == "Financial Year to Date"){
importPackage(Packages.java.util);
calendar = Calendar.getInstance();
calendar.set(Calendar.SECOND, 0);
calendar.set(Calendar.MINUTE, 0);
calendar.set(Calendar.HOUR_OF_DAY, 0);
calendar.set(Calendar.DATE, 1);
calendar.set(Calendar.MONTH, Calendar.APRIL);
var ThisYear = calendar.getTime();
calendar.add(Calendar.YEAR, -1);
var LastYear = calendar.getTime();
if (today < year+ "-04-01"){
rangeStart = LastYear;
}else{
rangeStart = ThisYear;
}
}
rangeStart = df.format(rangeStart);

this.queryText = this.queryText + " where VISION.IRS_INCIDENT.TIMEOFCALL between to_date('" + rangeStart + "', 'yyyy-mm-dd') and to_date('" + today + "', 'yyyy-mm-dd')";
}

All,

Since the concept presented in this thread close to my objective, I'd like to request some assistance.

I'm currently using BIRT Designer 2.2.0.
I've a dataset which contain a sql script that queries a number of tables. The query has 2 data set parameters: StartDate & EndDate.
Both parameters is linked to a single report parameter called ReportDate (yyyy-mm-dd) entered by the user.
Here's the portion of the sql script which contains the parameters:

S.CHANGEDATE BETWEEN
TO_DATE(concat(to_char(add_months(to_date(?,'yyyy-mm-dd'),-11),'yyyy-mm-dd'),' 00:00:00'),'yyyy-mm-dd hh24:mi:ss')
AND
TO_DATE(concat(to_char(last_day(add_months(to_date(?,'yyyy-mm-dd'),-1)),'yyyy-mm-dd'),' 00:00:00'),'yyyy-mm-dd hh24:mi:ss')

The script is getting the data for the last 11 months and the results are grouped into monthly.

My objective is to use the dataset script and achieve the following:
datePar = new Date(params["RP_EndDate"].value);
df = new Packages.java.text.SimpleDateFormat("yyyy-MM-dd");
datePar = df.format(datePar);

if(datePar.getMonth()<=5) {
rangeStart = DateTimeSpan.subDate(datePar,1,0,0);
lastYear = rangeStart.getYear()+1900;
this.queryText = this.queryText + " and s.changedate >= to_date(concat(to_char(" + lastYear + ",'yyyy'), '07-01 00:00:00'), 'yyyy-mm-dd hh24:mi:ss')";
}
else {
thisYear = datePar.getYear()+1900;
this.queryText = this.queryText + " and s.changedate >= to_date(concat(to_char(" + thisYear + ",'yyyy'), '07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')";
}

However, the script failed to execute and returned error message (as per attached file)

Can any one assist?

Thanks,
Albert

The error says that the method getMonth doesn't exist

datePar = new Date(params["RP_EndDate"].value);
df = new Packages.java.text.SimpleDateFormat("yyyy-MM-dd");
datePar = df.format(datePar); --> You parse the date parameter to a string using SimpleDateFormat and assign the value to variable datePar

if(datePar.getMonth()<=5) --> now you call the method getMonth on datePar which is a string and not a date object

try to remove the line datePar = df.format(datePar);

Thanks CBR, your suggestion eliminated the getMonth() error.

However, my console is showing:
RHINO USAGE WARNING: Missed Context.javaToJS() conversion:
Rhino runtime detected object 2010-04-02 of class java.sql.Date where it expected String, Number, Boolean or Scriptable instance. Please check your code for missig Context.javaToJS() call.
21/04/2010 08:10:39 org.eclipse.birt.data.engine.odaconsumer.PreparedStatement getRuntimeMetaData
SEVERE: Cannot get resultset metadata.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1: ORA-00933: SQL command not properly ended

The report parameter ReportDate is a date data type.

Any suggestion?

Thanks,
Albert

Can you attach your report?
Now you got the problem that a parameter or variable has datatype Date but is used as String somewhere in your report. Unfortunately it doesn't say the paramters name or location

First, you should not use the getYear function, use the getFullYear function instead, in that case there's also no more need to add 1900 to the result

Second, I think the to_char of lastYear is causing you trouble.
Try replacing

to_date(concat(to_char(" + lastYear + ",'yyyy'), '07-01 00:00:00'), 'yyyy-mm-dd hh24:mi:ss')

by

to_date(concat(" + lastYear + ", '-07-01 00:00:00'), 'yyyy-mm-dd hh24:mi:ss')

Ok, I've reviewed SQL script for the data set and realised that it was not working with the script I've written hence the SQL error message "SQL command not properly ended".
I've fixed the SQL script for data set since and also amended the getFullYear().

however, I'm not getting the result that i want ie. report returns all.
Here's the current script on the "beforeOpen":

datePar = new Date(params["RP_EndDate"].value);
if(datePar.getMonth()<=5) {
rangeStart = DateTimeSpan.subDate(datePar,1,0,0);
lastYear = rangeStart.getFullYear();
this.queryText = this.queryText + " and s.changedate >= to_date(concat(" + lastYear + ",'07-01 00:00:00'), 'yyyy-mm-dd hh24:mi:ss')";
}
if(datePar.getMonth()>=6) {
thisYear = datePar.getFullYear();
this.queryText = this.queryText + " and s.changedate >= to_date(concat(" + thisYear + ",'07-01 00:00:00'), 'yyyy-mm-dd hh24:mi:ss')";
}

However, if i just write a simple query such as following I'm getting the result:
this.queryText = this.queryText + " and s.changedate >= to_date(concat('2009','07-01 00:00:00'), 'yyyy-mm-dd hh24:mi:ss')"

You're still missing a "-" sign:

...concat('2009','07-01 00:00:00')... (-> leads to '200907-01 00:00:00')

should be:

...concat('2009','-07-01 00:00:00')... (-> leads to '2009-07-01 00:00:00')

so that the date string and the format string match

Hans_vd

I've tried your suggestion and still couldn't get it to work.
The same rhino usage warning message keep appearing.

I've read from ggogle that js.jar version may have contributed to this error, can anyone confirm this?

Thanks.

Ok, try bypassing the javascript date object as Rhino doesn't seem to like it. That also means, back to getYear instead of getFullYear. Try something like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
if(params["RP_EndDate"].getMonth()<=5) {
rangeStart = DateTimeSpan.subDate(params["RP_EndDate"].value,1,0,0);
lastYear = rangeStart.getYear() + 1900;
this.queryText = this.queryText + " and s.changedate >= to_date(concat(" + lastYear + ",'-07-01 00:00:00'), 'yyyy-mm-dd hh24:mi:ss')";
}

if(params["RP_EndDate"].getMonth()>=6) {
thisYear = params["RP_EndDate"].getYear() + 1900;
this.queryText = this.queryText + " and s.changedate >= to_date(concat(" + thisYear + ",'-07-01 00:00:00'), 'yyyy-mm-dd hh24:mi:ss')";
}
</pre>