After adding date parameters, report doesn't return results

Hi All,

To the existing report I have added date parameters,from then it doesn't return any rows. But data is available. I think there is something wrong with query execution.

Attached the rptdesignfile. Please help.

Thanks,
Suresh.

Find more posts tagged with

Comments

mwilliams

Have you checked to see that your date passes in the correct format? You might try printing your final query to the report, if you have access to it, so you can see what's getting passed.

peraka

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="86953" data-time="1322845916" data-date="02 December 2011 - 10:11 AM">
Have you checked to see that your date passes in the correct format? You might try printing your final query to the report, if you have access to it, so you can see what's getting passed. </blockquote>
 
Thanks for the reply, attached the rptdesign file. Please let me know if something wrong there.

mwilliams

I don't have access to your maximo data, so I cannot run your report. If you can display your "whereClause" in a dynamic text box in your report design after a table that's bound to your dataSet, you'll see what is being sent to your database and see if there is a formatting issue.

peraka

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="86956" data-time="1322849486" data-date="02 December 2011 - 11:11 AM">
I don't have access to your maximo data, so I cannot run your report. If you can display your "whereClause" in a dynamic text box in your report design after a table that's bound to your dataSet, you'll see what is being sent to your database and see if there is a formatting issue. </blockquote>
Hi Williams, 
 
I have printed the query and found that timestamp is passed to the query. I'm not able to run this query in toad. It throws "ora-9011 Invalid Character" error. 
 
A function is used to format the date, i.e.getStartDayTimestampFunction and getEndDayTimestampFunction 
workorder.reportdate >= { ts '2011-01-01 00:00:00' } and workorder.reportdate <= { ts '2011-05-01 23:59:50' } 
 
Please let me know how to overcome this error. 
 
Suresh.

Hans_vd

Hi Peraka,

You have to tell Oracle how your dates are formatted. Try writing the query like this:

workorder.reportdate >= to_date('2011-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and workorder.reportdate <= to_date('2011-05-01 23:59:50', 'yyyy-mm-dd hh24:mi:ss')

Hope this helps
Hans

peraka

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="89550" data-time="1323074857" data-date="05 December 2011 - 01:47 AM">
Hi Peraka, 
 
You have to tell Oracle how your dates are formatted. Try writing the query like this: 
 
workorder.reportdate >= to_date('2011-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and workorder.reportdate <= to_date('2011-05-01 23:59:50', 'yyyy-mm-dd hh24:mi:ss') 
 
Hope this helps 
Hans </blockquote>
Thanks for the reply. I have used getStartDayTimestampFunction and getEndDayTimestampFunction but it has to be customised to dd/MM/yyyy in BIRT Report Parameters. I have done that, it started working.