[Solved] Frustrating Timestamp Problem

[rhunter]

Hi All,

I'm using reportnet with an oracle 10 db, when using a date prompt to filter a report I noticed the SQL was converting the date to a TIMESTAMP in the query. The date field in the db was really a date/time field and so the query was very slow, using the cast_date function around the parameter date in the filter solved this problem. But now I want to drill through to another report from this master report passing the date to the child report. The same problem arises with TIMESTAMP but I'm unable to fix this with the cast_date function in the child report as a run time error occurs when calling the child report, it does however run ok when run directly. I'm assuming that the problem is with the way the parameter date is being passed by reportnet and the cast_date is failing as the datatype of the parameter date passed is not compatible. Has anyone else come across this problem? Does anyone know what the datatype/format of passed parameter dates is? Any help would be much appreciated.

[JGirl]

In the child report, try having the filter in your tabular model as [namespace].[querysubject].[DateQueryItemToBeFiltered] = cast(?parametername?,date)

You need to cast the parameter itself, rather than the string that is returned by the report functions ParamValue(?parametername?) or ParamDisplayValue(?parametername?)

My understanding is that if you build a "date prompt" (not a date time) that filters a datetime column, your parameter value is converted to a datetime, and the parameter display value is a string.

If I havent understood you correctly, maybe you could post the xml for the two reports?
J

[bdybldr]

rhunter,
The easiest way to handle this is to change the data type in FM to date.

[rhunter]

Thanks for the response JGirl, but using the cast(?parameter date?, date) means that the filtering is done locally and not on the oracle db side which makes the query even slower than before.

[rhunter]

Managed to find a workaround for this using the following in the filter
<date field> = to_date(substr(to_char(?parameter date?),1,10),'YYYY-MM-DD')
A bit messy but solves the problem.

Thanks

[JGirl]

my bad.

I'm running on a database that won't allow me to do date comparisons unless I do it locally anyway (lovely), so of course, it works for me