Topic
  • 14 replies
  • Latest Post - ‏2013-01-07T14:08:49Z by mandreas
RonnieHe
RonnieHe
5 Posts

Pinned topic Date Parameter

‏2011-02-07T05:49:01Z |
Hi,

I have 1 BIRT report using Date Parameters, StartDate and EndDate.while run as one-off, got results successfully, no issue.
However if scheduled the report, entered same date range, got error:
The type of parameter "StartDate" is expected as "date", not "java.lang.String".
at com.ibm.tivoli.maximo.report.birt.admin.ReportAdminService.runReport(ReportAdminService.java:3012)
at com.ibm.tivoli.maximo.report.birt.queue.ReportQueueManager.runQueuedReport(ReportQueueManager.java:889)
at com.ibm.tivoli.maximo.report.birt.queue.ReportQueueManager.runQueuedReport(ReportQueueManager.java:822)
at com.ibm.tivoli.maximo.report.birt.queue.ReportQueueManager.doWork(ReportQueueManager.java:626)
at com.ibm.tivoli.maximo.report.birt.queue.ReportQueueManager$ReportRunThread.attemptToRunReport(ReportQueueManager.java:481)
at com.ibm.tivoli.maximo.report.birt.queue.ReportQueueManager$ReportRunThread.run(ReportQueueManager.java:420)
Caused by: com.ibm.tivoli.maximo.report.birt.runtime.ReportExecutionException: The type of parameter "StartDate" is expected as "date", not "java.lang.String".
at com.ibm.tivoli.maximo.report.birt.engine.MXReportEngine.executeReport(MXReportEngine.java:328)
at com.ibm.tivoli.maximo.report.birt.engine.MXReportEngine.executeReportInImmediateMode(MXReportEngine.java:124)
at com.ibm.tivoli.maximo.report.birt.admin.ReportAdminService.runReport(ReportAdminService.java:2981)
... 5 more
Caused by: com.ibm.tivoli.maximo.report.birt.runtime.ReportExecutionException: The type of parameter "StartDate" is expected as "date", not "java.lang.String".
at com.ibm.tivoli.maximo.report.birt.runtimeprovider.XLSReportGenerator.generateReport(XLSReportGenerator.java:102)
at com.ibm.tivoli.maximo.report.birt.runtimeprovider.ReportExecutionTaskImpl.executeReport(ReportExecutionTaskImpl.java:127)
at com.ibm.tivoli.maximo.report.birt.engine.MXReportEngine.executeReport(MXReportEngine.java:323)
... 7 more
Caused by: org.eclipse.birt.report.engine.api.impl.ParameterValidationException: The type of parameter "StartDate" is expected as "date", not "java.lang.String".
at org.eclipse.birt.report.engine.api.impl.EngineTask.validateParameterValueType(EngineTask.java:785)
at org.eclipse.birt.report.engine.api.impl.EngineTask.validateScalarParameter(EngineTask.java:749)
at org.eclipse.birt.report.engine.api.impl.EngineTask.access$0(EngineTask.java:679)
at org.eclipse.birt.report.engine.api.impl.EngineTask$ParameterValidationVisitor.visitScalarParameter(EngineTask.java:623)
at org.eclipse.birt.report.engine.api.impl.EngineTask$ParameterVisitor.visit(EngineTask.java:1095)
at org.eclipse.birt.report.engine.api.impl.EngineTask.doValidateParameters(EngineTask.java:607)
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:84)
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRenderTask.java:72)
at com.ibm.tivoli.maximo.report.birt.runtimeprovider.XLSReportGenerator.generateReport(XLSReportGenerator.java:96)

Code in the script is:
reportdate between "+ MXReportSqlFormat.getStartDayTimestampFunction(params)
+" and " + MXReportSqlFormat.getEndDayTimestampFunction(params)

And data type is Date in Parameter properties.

Has anybody else experienced same error, and suggestion to fix this?

Best regards,
Ronnie
Updated on 2013-01-07T14:08:49Z at 2013-01-07T14:08:49Z by mandreas
  • RonnieHe
    RonnieHe
    5 Posts

    Re: Date Parameter

    ‏2011-02-08T04:37:54Z  
    Sorry, the code in script is:
    between "+ MXReportSqlFormat.getStartDayTimestampFunction(params)
    +" and " + MXReportSqlFormat.getEndDayTimestampFunction(params)
  • RonnieHe
    RonnieHe
    5 Posts

    Re: Date Parameter

    ‏2011-02-08T04:39:51Z  
    • RonnieHe
    • ‏2011-02-08T04:37:54Z
    Sorry, the code in script is:
    between "+ MXReportSqlFormat.getStartDayTimestampFunction(params)
    +" and " + MXReportSqlFormat.getEndDayTimestampFunction(params)
    between "+ MXReportSqlFormat.getStartDayTimestampFunction(params)
    +" and " + MXReportSqlFormat.getEndDayTimestampFunction(params)
  • RonnieHe
    RonnieHe
    5 Posts

    Re: Date Parameter

    ‏2011-02-08T04:42:34Z  
    • RonnieHe
    • ‏2011-02-08T04:39:51Z
    between "+ MXReportSqlFormat.getStartDayTimestampFunction(params)
    +" and " + MXReportSqlFormat.getEndDayTimestampFunction(params)
    Sorry, the first params is StartDate, the 2nd is EndDate
  • BKDowning
    BKDowning
    15 Posts

    Re: Date Parameter

    ‏2011-03-28T19:17:45Z  
    • RonnieHe
    • ‏2011-02-08T04:42:34Z
    Sorry, the first params is StartDate, the 2nd is EndDate
    Two things:

    First the code does not include the name of the parameter itself. You need to pass the name of the parameter to the method you are calling. The code should read like this:
    + " and reportdate between " + MXReportSqlFormat.getStartDayTimestampFunction(params)
    + " and " + MXReportSqlFormat.getStartDayTimestampFunction(params)

    Please keep in mind that java script can be finicky with case sensitivity. (I treat it as case hostile!) so if this does not work check the case of the parameter.

    Second, the parameter needs to be of the correct data type. Dates are not strings. Set up your parameter so that it is of type Date. This will enable your report to run correctly using the above method in your code.
  • SystemAdmin
    SystemAdmin
    5842 Posts

    Re: Date Parameter

    ‏2011-10-27T19:10:42Z  
    • RonnieHe
    • ‏2011-02-08T04:42:34Z
    Sorry, the first params is StartDate, the 2nd is EndDate
    I have encountered the same problem using both the MXReportSqlFormat.getStartDayTimestampFunction and the MXReportSqlFormat.getDateFunction. To add to the confusion, some reports with the same date parameter setup run fine in the Maximo Scheduler. I have checked and I even got an Acutate developer to look at the code and the parameters are defined as date and the SQL looks right.

    Have you had any luck determining what is causing the problem? It must me something simple but tricky.

    Thanks.
  • SystemAdmin
    SystemAdmin
    5842 Posts

    Re: Date Parameter

    ‏2012-03-27T13:29:58Z  
    I have encountered the same problem using both the MXReportSqlFormat.getStartDayTimestampFunction and the MXReportSqlFormat.getDateFunction. To add to the confusion, some reports with the same date parameter setup run fine in the Maximo Scheduler. I have checked and I even got an Acutate developer to look at the code and the parameters are defined as date and the SQL looks right.

    Have you had any luck determining what is causing the problem? It must me something simple but tricky.

    Thanks.
    HI

    I am having the same problem it seems.. Did anyone get any further with this one?
    I can run a report fine on demand in the browser with dates in the format

    YYYY-MM-DD (no quotes around them, i.e. startDate = 2011-11-01 endDate = 2011-11-30

    But when I run on a schedule with the same format I get the error as Ronnie does regarding date to string conversion

    Both my parameters are of type Date

    Any help appreciated , thanks

    Eug
  • SubratM
    SubratM
    9 Posts

    Re: Date Parameter

    ‏2012-04-04T15:10:16Z  
    What version of Maximo Base Services you have? In many versions Maximo supports only DateTime parameter. This is fixed in APAR IZ88730. You have two options:

    1. Change your parameter to DateTime.
    2. Check if a fix is available for APAR IZ88730 and apply that fix.
  • SystemAdmin
    SystemAdmin
    5842 Posts

    Re: Date Parameter

    ‏2012-04-04T21:56:56Z  
    • SubratM
    • ‏2012-04-04T15:10:16Z
    What version of Maximo Base Services you have? In many versions Maximo supports only DateTime parameter. This is fixed in APAR IZ88730. You have two options:

    1. Change your parameter to DateTime.
    2. Check if a fix is available for APAR IZ88730 and apply that fix.
    Finally found a solution to this....

    I made my date parameters Strings then reconstructed the values entered to ISO format (YYYY-MM-DD), knowing that DB2 accepts these in its queries.
    Open method script

    // Created 2 local variables to hold the parameters

    var startDate = params;
    var endDate = params;

    // Split date inputted in dd/MM/yyyy format into ISO format blocks (YYYY-MM-dd).

    var sdayportion = startDate.substring(0,2);
    var smonportion = startDate.substring(3,5);
    var syrportion = startDate.substring(6,10);

    var edayportion = endDate.substring(0,2);
    var emonportion = endDate.substring(3,5);
    var eyrportion = endDate.substring(6,10);

    // Create ISO format string to pass to where clause for start and end dates

    var ISOstartdate = syrportion + "-" + smonportion + "-" + sdayportion;
    var ISOenddate = eyrportion + "-" + emonportion + "-" + edayportion;

    ...

    // Where clause uses the ISO format strings

    where = where + " and ticket.creationdate between '" + ISOstartdate
    + "' and '" + ISOenddate + "' ";

    As a bonus I was also able to associate a datelookup control with each of parameters in Report Administration which validated that the values entered were in DD/MM/YYYY format

    Eug
  • SystemAdmin
    SystemAdmin
    5842 Posts

    Re: Date Parameter

    ‏2012-04-04T22:00:23Z  
    Finally found a solution to this....

    I made my date parameters Strings then reconstructed the values entered to ISO format (YYYY-MM-DD), knowing that DB2 accepts these in its queries.
    Open method script

    // Created 2 local variables to hold the parameters

    var startDate = params;
    var endDate = params;

    // Split date inputted in dd/MM/yyyy format into ISO format blocks (YYYY-MM-dd).

    var sdayportion = startDate.substring(0,2);
    var smonportion = startDate.substring(3,5);
    var syrportion = startDate.substring(6,10);

    var edayportion = endDate.substring(0,2);
    var emonportion = endDate.substring(3,5);
    var eyrportion = endDate.substring(6,10);

    // Create ISO format string to pass to where clause for start and end dates

    var ISOstartdate = syrportion + "-" + smonportion + "-" + sdayportion;
    var ISOenddate = eyrportion + "-" + emonportion + "-" + edayportion;

    ...

    // Where clause uses the ISO format strings

    where = where + " and ticket.creationdate between '" + ISOstartdate
    + "' and '" + ISOenddate + "' ";

    As a bonus I was also able to associate a datelookup control with each of parameters in Report Administration which validated that the values entered were in DD/MM/YYYY format

    Eug
    First part should read

    var startDate = params;
    var endDate = params;
  • SystemAdmin
    SystemAdmin
    5842 Posts

    Re: Date Parameter

    ‏2012-04-04T22:03:46Z  
    First part should read

    var startDate = params;
    var endDate = params;
    Code is being truncated in the post so have attached for anyone interested
  • SystemAdmin
    SystemAdmin
    5842 Posts

    Re: Date Parameter

    ‏2013-01-04T20:00:58Z  
    • SubratM
    • ‏2012-04-04T15:10:16Z
    What version of Maximo Base Services you have? In many versions Maximo supports only DateTime parameter. This is fixed in APAR IZ88730. You have two options:

    1. Change your parameter to DateTime.
    2. Check if a fix is available for APAR IZ88730 and apply that fix.
    Even when my parameter is defined as DateTime there is a difference in the way the parameter is evaluated when the report is run as immediate as opposed to scheduled. For instance, your report SQL can check for a value in a DateTime parameter to determine if all records should be selected or just the ones in the date range. When the report is submitted as an immediate report, it works fine however if you submit the same report as a scheduled report the scheduler errors out because the DateTime parameter is blank. In this example, I am building a where statement that is used in the SQL.

    if(params.value){
    params+= " and INVS.CHANGEDATE >= " + MXReportSqlFormat.getStartDayTimestampFunction(params)
    }

    We are running on Maximo 7.5.0.2 and this has not been fixed.

    Any ideas or help?
  • SystemAdmin
    SystemAdmin
    5842 Posts

    Re: Date Parameter

    ‏2013-01-04T20:08:02Z  
    Even when my parameter is defined as DateTime there is a difference in the way the parameter is evaluated when the report is run as immediate as opposed to scheduled. For instance, your report SQL can check for a value in a DateTime parameter to determine if all records should be selected or just the ones in the date range. When the report is submitted as an immediate report, it works fine however if you submit the same report as a scheduled report the scheduler errors out because the DateTime parameter is blank. In this example, I am building a where statement that is used in the SQL.

    if(params.value){
    params+= " and INVS.CHANGEDATE >= " + MXReportSqlFormat.getStartDayTimestampFunction(params)
    }

    We are running on Maximo 7.5.0.2 and this has not been fixed.

    Any ideas or help?
    The statement did not copy completely. It should have been:

    if(params.value){
    params+= " and INVS.CHANGEDATE >= " + MXReportSqlFormat.getStartDayTimestampFunction(params)
    }
  • SystemAdmin
    SystemAdmin
    5842 Posts

    Re: Date Parameter

    ‏2013-01-04T20:13:30Z  
    The statement did not copy completely. It should have been:

    if(params.value){
    params+= " and INVS.CHANGEDATE >= " + MXReportSqlFormat.getStartDayTimestampFunction(params)
    }
    Ok. Hopefully the 3rd time is the charm. The previous copy/paste was leaving out the actual parameter name which is begin_date and would be between the params and the .value.

    if(params.value){
    params+= " and INVS.CHANGE >= " + MXReportSQLFormat.getStartDayTimestampFunction(params)
    }
  • mandreas
    mandreas
    89 Posts

    Re: Date Parameter

    ‏2013-01-07T14:08:49Z  
    Hi Ronnie

    Have you tried to enter different time-formats?
    In 'my' environment (TSRM 7.2.1) I have to enter different formats for the same report for adhoc and sheduled reports (DD/MM/YYYY, mm-dd-yyyy, ...).

    Andreas