Date Prompt with Default Today

I’ve seen the request many times, but have never seen a good solution. People ask for a date prompt in the universe, with a default value of today. I recently had this request from a client, and, as usual, informed her that there’s no built in functionality to accomplish this. Yes, I have seen a few convoluted solutions, but nothing that ever impressed me. Then, the next morning, I was thinking about this in the shower (Hold your comments, please. I do my best thinking in the shower.). I started to get a few ideas about how to solve this dilemma, and was anxious to return to my client’s site to try some experiments. Of course, any solution will be based around using the @Prompt function to prompt for a value. So, let’s first take a look at the @Prompt function, and it’s numerous arguments.

Here is the syntax for the @Prompt function, as presented in the Designer help:

@Prompt(‘message’,’type’,[lov],Mono|Multi, free|constrained|primary_key,persistent|not_persistent,[default_values])

So, let’s dissect this.

Message: This is the text of the prompt. In other words, it is the message that you want to convey to the user, asking them to enter, or select, a value. This is always enclosed in single quotes.

Type: This represents the data type to be entered at the prompt. You have three choices here:

A for alphanumeric

N for numeric

D for date

The type is always enclosed in single quotes.

LOV: This is the list of values to be presented to the user along with the prompt. There are two choices for presenting the list of values:

  • You can use an existing object from the universe that has a list of values that will work for this object. The format is Class\Object. For example, if you want to use the State object from the Geography class, you would enter ‘Geography\State’. Make sure it is enclosed in single quotes.
  • You can hard code a list of values. This can be useful for small, static lists, such as Yes, No, Maybe. The format is as follows: {‘Yes’,’No’,’Maybe’}

Mono|Multi: This determines if the user will be allowed to enter multiple values or only a single value. If you enter mono, only one value will be allowed. If you enter multi, the user will be able to enter one or more values.

Free|Constrained|Primary_Key: Normally, you would enter free or constrained for this argument. Free allows the user to enter a value, or select from the list of values. Constrained limits the user to select only from the list of values. However, if you are using Index Awareness on the object from which the list of values comes, you can enter Primary_Key in this argument, which will trigger the indexed field to be sent to the database.

Persistent|Not_Persistent: Enter Persistent if you want the last value selected at the prompt to be retained the next time the query is run. If you enter Not_Persistent, the prompt value will be empty each time the query is run, unless there is a default value, in which case the default value will be displayed.

Default Value: If desired, you can enter a default value for the prompt. The format of the default value is as follows: {‘Yes’}. If you want to enter multiple default values, enter them as follows: {‘Yes’,’No’}. If you are using the Primary_Key argument, you can even enter the key values in the default values: {‘Yes’:’1′,’No’:’0′}.

OK, so now let’s look at my final solution. I spent about three hours experimenting, and, in the end, this was the best solution, and easiest to implement. My client is using a DB2 database, so this is based on DB2. Here is my final syntax for the predefined filter object in the universe:

CALENDAR_TABLE.CAL_DATE = (CASE WHEN @Prompt(‘Enter date or Today’,’A’,,mono,free,not_persistent,{‘Today’})=’Today’ THEN CURRENT_DATE ELSE @Prompt(‘Enter date or Today’,’A’,,mono,free,not_persistent,{‘Today’}) END)

Here’s a few thoughts on this:

  • Notice that the data type is ‘A’. The reason for this is that the default value is text, not a date. So, if you use ‘D’ as the data type, you will get an error using the default value. The bad news is that you won’t get a calendar button with the prompt, so if you want to enter a date, you will need to type it in.
  • The default value, “Today” will always be the default, as I have set the 6th argument to not_persistent. This avoids the need for the user to type in “Today”. If they want today, they just run the query without entering a value.
  • CURRENT_DATE returns the current system date from the server in DB2. See below for alternate syntax for other common databases.

If you are using a database other than DB2, you can use the following:

For Oracle: CALENDAR_TABLE.CAL_DATE = (CASE WHEN @Prompt(‘Enter date or Today’,’A’,,mono,free,not_persistent,{‘Today’})=’Today’ THEN SYSDATE ELSE @Prompt(‘Enter date or Today’,’A’,,mono,free,not_persistent,{‘Today’}) END)

For SQL Server: CALENDAR_TABLE.CAL_DATE = (CASE WHEN @Prompt(‘Enter date or Today’,’A’,,mono,free,not_persistent,{‘Today’})=’Today’ THEN GetDate() ELSE @Prompt(‘Enter date or Today’,’A’,,mono,free,not_persistent,{‘Today’}) END)

Give it a try, and let me know what you think.


33 Responses to Date Prompt with Default Today

  1. Nniixx says:

    That’s very clever. I’ll give it a try at my current client using Oracle to see how it works.

  2. lakshmi says:

    what if i try to use index awarenss along with the date prompt with calendar ?

    @Select(object)[this has index awarenss defined] = @Prompt(‘Enter date’,’D’,,mono,free…)

    • Index Awareness won’t work with this trick, as there is no List of Values provided. Index Awareness relies on the LOV to store the indexed values for the LOV. So, if you don’t use the LOV, the Indexed column isn’t used in the query.

  3. lakshmi says:

    hmm… the problem is i have date key indexed in the database..but the prompt is calendar prompt which uses date column..if i try to convert date into keys the conversion hampers the performance… 😦

  4. sridhar says:

    Thanks for helpful post,

    here im my situation , the latest date should display as default value. I tried to use max(date) but not working. In my database we have one Flag=’Y’, where this equals yo latest date in data base.
    Please help me how to achieve this…

    Thanks in advance…

    • Sridhar, that’s a great question. Have you posted it on BOB? On BOB you could get input form people much smarter than me. 🙂
      In my experimentation, I tried using a subselect in the @prompt to get the current date, but, of course, that didn’t work. This almost sounds like a job for a stored procedure, but I’ve not worked with SP’s before.

  5. Zaif says:

    Nice Post!!
    Since the prompt is in Alphanumeric, no calendar!! the user have to type the date in which format?

  6. Jansi says:

    Michael, can it be done using Current Date() in DB2? Your thoughts, please…

  7. Jansi says:

    Cool. 🙂 Thanks!

  8. Jansi says:

    Oops. It seems I have skipped many lines. 😦

  9. RAJ says:

    Hi Michael,
    Good post but I am getting an error in the 2nd half of your formula while parsing. It says ORA-00932: inconsistent datatypes:expected DATE got CHAR. What am I doing wrong?

    • Raj, can you share your formula?

      • Abdul Jamal says:

        Hi Michael
        I am getting parsing error on the 2nd half if the formula as well.(the syntax of the string representation of a datetime value is incorrect). I am using DB2 as well.
        Received_date is a date formate.
        Here is my formula:

      • Abdul, that formula looks right to me. Are you doing this in Designer, or IDT?

      • Abdul Jamal says:

        I am doing this in Designer. Using 3.1
        Any suggestions what else i can do.
        Appreciate your help.

    • Anthony Van den Bossche says:

      hello RAJ,
      a bit late, but i found this thread while searching on the same problem.
      I solved this inconsistent datatypes error in Designer, by adding a To_date function :

      case when @Prompt(‘Give a valid Report date or M0 for 1st of this month’,’A’,,mono,free,not_persistent,{‘M0’}) = ‘M0′
      Then trunc(sysdate,’mm’)
      else to_date(@Prompt(‘Give a valid Report date or M0 for 1st of this month’,’A’,,mono,free,not_persistent,{‘M0′}),’dd/MM/yyyy’)
      hope this helps somebody.

  10. boman says:

    Awesome! Works great for me using a customized sysdate logic.

  11. zaif235 says:

    Michael, UserResponse(“Enter date or Today”) will pick Today right?

  12. Jurriaan says:

    Michael, I have been using a similar prompt in XI. In my case the “CAL_DATE”” column is a datetime. But when you try to use this prompt in IDT, it doesn’t work anymore. Because in IDT you are not allowed to cast a prompt of type ‘A’ to datetime. (I think that IDT is using a default value, “answer” in the case of a string/aplha numeric, to see if the cast is valid)

    Have you experienced similar issues?

  13. Sarfaraz says:

    Hi Michael,
    I execute below statement my database is Oracle 9i, passing Error 3rd parameter

    PRODUCT_SALES_FACT.SOLD_DATE = (CASE WHEN @Prompt(‘Enter date or Today’,’A’,,mono,free,not_persistent,{‘Today’})=’Today’ THEN trunc(SYSDATE) ELSE @Prompt(‘Enter date or Today’,’A’,,mono,free,not_persistent,{‘Today’}) END)

  14. Saqib says:

    SAP IDT Date prompt with default date on ORACLE 10g:


  15. Saqib says:

    above works in Data Foundation and Business Layer Query but gives the following error when used in WebI 😦

    An internal error occured while calling ‘processDPCommandsEx’ API. (Error: ERR_WIS_30270) (WIS 30270)

  16. san2sreshta says:

    I have tried with SQL server as the example above

    Project.CreatedDate = (CASE WHEN @Prompt(‘Enter date or Today’,’A’,,mono,free,not_persistent,{‘Today’})=’Today’ THEN GetDate() ELSE @Prompt(‘Enter date or Today’,’A’,,mono,free,not_persistent,{‘Today’}) END)

    Please help me

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: