Date Prompt from Macro - Convert from String->Date for performance

[spichette]

With the help of this forum, I've been able to build a macro to run an Impromptu report (Ver.6), passing a date calculated within the macro, as a string "yyyy-mm-dd" to the fill-in prompt field in the .imr. Unfortunately, because of type conversions, the only way I've found to make the filter work in the .imr is: Date-to-String(OrderDate) = %rptdate%. Order Date is indexed in the database, but with millions of records, it is still doing a sequential read. Performance is worse than bad. The only other thing I can think of is to split the Date parameter into three parameters (rptYear) (rptMonth) (rptDay) within the Macro, and have a filter such as: OrderDate = datetime-to-date(make-datetime(string-to-integer(%rptYear%),string-to-integer(%rptMonth%),string-to-integer(%rptDay%))) ---- But this seems like SUCH overkill for a simple, probably VERY common query restriction. D.Griffin ???

 

[DoubleD]

First of all, I believe you're voiding the index by doing the Date-To-String.
Next, what type of index is on your OrderDate field? If you're in Oracle, cardinality probably suggests a bitmap, yet it may be set to btree.

How is the OrderDate stored in the database? Is it a Date-Time field? If so, is the time stamped or defaulted to 00:00:00? This all impacts how you should go about modifying your macro to increase performance.

I am what I am based on the decisions I have made.

DoubleD

 

[spichette]

SyteLine / Progress is the Database.

 

[DoubleD]

I haven't worked with SyteLine/Progress, but here's what you can do.
Find out whether OrderDate is a Date or DateTime field.
A. If it is a DateTime field:
Assuming the Time is defaulting to midnight, change your prompt to a DateTime prompt, then add the default time onto your String in the macro.
B. If it is a Date field:
Ensure RptDate is a date prompt. Your macro will still pass a string, but Impromptu will recognize it as a date.

I am what I am based on the decisions I have made.

DoubleD

 

[spichette]

... B. If it is a Date field:
Ensure RptDate is a date prompt. Your macro will still pass a string, but Impromptu will recognize it as a date..."


That's what I was looking for !!! Thanks !

 

[spichette]

I had originally set up rptDate as a DATE datatype, but what I also had to do was reformat it in the macro before sending it to the report :

RptDate = Format((Today - 1),"yyyy-mm-dd")

Thanks for the help.