Good morning all. I have a report that runs against an oracle database. I have a data item that is a date-time data type on on a crosstab report and the date has a format of: 2008-09-29 00:00:00.000. I used a cast function to mask the date and it looks like this: cast([date-time data item], date).
Once i do this, the report runs in hours versus the minutes it took previously. The WHERE clause with the cast function now looks like this: TRUNC("T"."Completion_Date")=Date '2008-09-29' so oracle will not use the index on the data item.
I have also tried formatting the date with no luck. Any ideas?
Did you check how the date is stored in Oracle? Oracle typically stores date and time components in seperate fields (as opposed to say , SQL Server).
Your case is a shining why you should use a date dimension that has a surrogate key relation between fact and dimension (and the integer key is indexed)
That way you lose the time component and you can access the larger facttable through an index from the dimension.
I found a way to mask the date. I clicked on the Crosstab Node Member where the date is located and then in the properties under Text Source, I changed the Source Type to Data Item Value (it defaults to Member Caption). Then you can format the date.