Display current date in prompt 1

[timida]

Hello!

Is there a possibility to display the current date in a type-in prompt as default value?
Note: I'm using impromptu 7.0.

thanks
Timida

 

[DoubleD]

The only way I know of to do this is as follows:
Create two prompts
1. ?Current Date? - this is a type-in text prompt with a default value of 'Y'. Text tells the user to change to N if they want to view other than today's data.
2. ?Type-in Date? - this is a type-in date prompt with a default value of 1/1/1900. Text tells the user to enter a date if they want to see a date other than today's date.

The filter for the report then reads as follows:
If (upper(?Current Date?) = 'Y' then (today()) else (?Type-in Date?))
If the user enters anything other than Y in the first prompt, the report will use the date from the Type-in Date prompt.


Pain is stress leaving the body

DoubleD

 

[timida]

Hi DoubleD,

The problem with this solution is that impromptu doesn't transfer the filter condition to the sql/DB.. so the performance is really bad. The filter on the date is not passed to the DB only the value of the first prompt (Y or anything else) is in the select part [...,upper('N'),...].

For me it is very important that the date-condition is passed to the DB, otherwise there are too many records and so the performance is not good.

Also with this solution I get 2 prompts... I would rather prefer to have only 1 prompt.

Maybe there is another solution...
Timida

 

[GilMerc]

Timida,

I'm not relly sure but I think it's impossible to display current in a prompt without use a macro. But you can display a prompt with value null, if a user type a value then use this value in a filter otherwise (if is always null) take a filter with now date time.

Filter like :

1 = IF (&prompt$ IS NOTHING AND «datetime field» = now) THEN(1) ELSE IF ($prompt IS NOT NOTHING AND «datetime field» = $prompt$) THEN (1) ELSE (0)

Gilles

 

[timida]

Hi,

the problem with this solution is that impromptu does not pass the date to the DB/SQL. So the query execution time is way too long.

I'm a newbie with macros. How can I do this (display the current date in a prompt and pass it to the sql) in a macro? And will this macro also work in the IWR?

Timida

 

[timida]

additionally:
I want to display a date type-in prompt (?date?). This prompt should be used in the filter:
Delivery_Date >= ?date? and Delivery_Date <= add-days(?date?;1).
The date in the filter has to be passed to the database and should not be executed locally.

 

[DoubleD]

About your additional filter, what database are you running against? You need to ensure that any functions you are using are supported by the db. If they are not, you will always process locally. Unfortunately, Impromptu can get finicky with dates and Oracle. Impromptu has a tendency to work dates locally because Oracle only has Date-Time fields.
The Now() function will always be passed to Oracle, but that will include the time so you may need to set the time to zero, just depends on what you are trying to accomplish.

If you can provide your filter, and the SQL shown in your profile tab, we can probably help you figure this out.

Pain is stress leaving the body

DoubleD

 

[timida]

Hi!

The DB is an oracle 8 DB. The filter above always works for me in other reports (and is passed to the DB). The problem is that when I use an if statement in the filter or a calculated column the date-filter is processed locally.

I created a type-in prompt (string - default value NULL)
I also created a calculated column (DateCon). Which contains the if-statement
"if (?date? is missing) then (now()) else (to_date(?date?;'YYYY-MM-DD'))
My filter condition then looks like: DeliveryDate >= DateCon and DeliveryDate < add-days(DateCon;1)

This doesn't work. The SQL-Statement looks like:
with no value for the prompt:
[select T1."DATUM", SYSDATE, TO_DATE('' ,'YYYY-MM-DD' ),...]
with the value 2004-01-01 for the prompt:
[select T1."DATUM", SYSDATE, TO_DATE('2004-01-01' ,'YYYY-MM-DD'),...]

In the where-clause the date is not included - but there it should be included in order to filter the data and to have a good performance.

 

[DoubleD]

Try the following calculation for your filter:
DeliveryDate >= decode(?Date?,'',Now(),to_date(?Date?,'YYYY-MM-DD')) and DeliveryDate < add-days(decode(?Date?,'',Now(),to_date(?Date?,'YYYY-MM-DD')),1)

I used this filter and it was all passed to Oracle. I think the "is missing" part may be causing the problem.

Pain is stress leaving the body

DoubleD

 

[timida]

Hi DoubleD!

This filter works for me - thanks for your help!!

Timida

 

[GilMerc]

timida,

You must modify your calculation field. If prompt ?date? is a string change (?date? is missing) by (?date? = ""). Impromtu is little sensible on this!!!

Gilles

 

[timida]

Hi GilMerc,

if I use a calculated field in the filter it is not passed to the SQL/DB.
So for me only the filter with decode works.

Anyway, thanks for your help
Timida

 

[MitraMIS]

Hi Group;

I have a question on GilMerc's response.



I'm not relly sure but I think it's impossible to display current in a prompt without use a macro. But you can display a prompt with value null, if a user type a value then use this value in a filter otherwise (if is always null) take a filter with now date time.



Date prompt is mandatory field in my system. Is there any set up for this? How can I change it so that it will accept "NULL" value?

TIA

PS. I'm using Impromptu 7.1

 

[DoubleD]

I believe you would have to make your prompt a string, then convert it to date in your filter.

So the filter GilMerc gave:

1 = IF (&prompt$ IS NOTHING AND «datetime field» = now) THEN(1) ELSE IF ($prompt IS NOT NOTHING AND «datetime field» = $prompt$) THEN (1) ELSE (0)

would change to:
[Filter Date] = (IF($prompt$ IS NULL) THEN (Now()) ELSE (string-to-date($prompt$)))

Pain is stress leaving the body.

DoubleD

 

[GilMerc]

You're right DD.

 

[MitraMIS]

Thanks DoubleD.
I don't have string-to-date function though. How can I add that?
There are string-to-integer and string-to-number components in the Function folder, but not string-to-date.

Mitra

 

[DoubleD]

If you're using Oracle, it's the to_date function.

Pain is stress leaving the body.

DoubleD

 

[stewm]

bTimida/b,

Instead of having the user have to type in the date why don't you offer a drop down list of dates in descending order. Starting with today's date. Make sure you want todays date. Are you reporting off live data or day old data? Create a report that lists dates starting from today going back to the point in the that you want to report on. Then create a report driven prompt. Piece of cake, and you're users will love you.

Mark Stewart
Senior Analyst
Consultants Club Corp.
Windsor, Ontario
Canada

http://www.consultantsclub.biz

 

[MitraMIS]

DoubleD;

I'm using sql. I can't find neither string-to-date nor to_date function.

But my main issue is that even if I had the string-to-date function it wouldn't be available to select when I get to choose it in the code below.

[Filter Date] = (IF($prompt$ IS NULL) THEN (Now()) ELSE (string-to-date($prompt$)))

 

[DoubleD]

Do you mean your using SQL Server?
If so, look up how to use the CAST or CONVERT functions to convert from String to Date.

Correction on the above code, it should have read:

[Filter Date] = (IF($prompt$ IS MISSING) THEN (Now()) ELSE (to_date($prompt$)))

This works for Oracle.


Pain is stress leaving the body.

DoubleD