Jul 15 2008

How Can I Make “Today” My Default Prompt Value?

Categories: Prompts, Universe Design Dave Rathbun @ 11:18 pm

I have seen this question come up frequently on BOB lately. And if the question keeps coming up, it becomes an easy idea for a blog post. That way, once I document a concept or a solution I have somewhere consistent to link to. The short answer to the question, unfortunately, is no. But let’s investigate and explain a bit further than that, shall we?

Problem Description

I have a report. The report has a date prompt. I want the date prompt to have a default value (partially possible) and I want the default value to be “today” instead of a standard date. The question is quite reasonable, in my opinion. Prompt handling has been one of the weakest points of the Business Objects tools for a long, looonnng time. A prompt can be created on a report or in the universe. If the prompt is defined in the universe there is a standard syntax. So I will start with a brief review of that syntax and move on from there.

@Prompt() Me One More Time…

The standard prompt syntax has five arguments. The first two are generally considered required while the remaining three are optional. Here is the syntax:

@prompt('Enter prompt text here','A',LOV Source,mono/multi,free/constrained)

I’ll break down each of those five parameters next. First, the prompt text. The first argument is required and is the most obvious… it contains the text that the user will react to. It’s a good idea to let the user know what’s going on and to provide some clue as to how the prompt will operate. For example, if you are using a prompt in an equality test, you should inform the user that they can only select a single value. If you are using the prompt in a list, then let them know they can select one or more values.

The next argument has three options: A, N, or D. These match with the datatype expected from the prompt. The ‘A’ represents alphanumeric data, and any data entered is accepted as it was entered. Using an ‘N’ denotes a numeric prompt and only valid numeric values are accepted. Finally, a ‘D’ represents a date prompt. With this type of prompt Webi will include a spiffy calendar that a user can use to select their entry. This is available even if the LOV has been turned off.

The third parameter is a reference to a potential list of values for the prompt. There are two possible options: either a hard-coded list, or an object from the universe. If I wanted to make a hard coded list with the values “Yes” and “No” on it here is the syntax:


The values are surrounded with curly braces { } and entered as a comma-separated list. Character data is in quotes. Simple enough, yes? no? ;-)

If I wanted to use an existing object in the universe for the list of values I would enter that choice without the curly braces and in the format 'Class Name\Object Name' instead. One common designer mistake is attempting to use a table.column syntax here. The table.column is of course the eventual source for the list of values, but the LOV query is defined as part of an object definition. That’s why you enter an object here rather than a table column.

The last two parameters are optional, but even if you leave them out you have to include the commas for the prompt syntax. In other words, this is valid:

@Prompt('Do you like BOB?','A',{'Yes','Yes, of course'},,)

Even though the values are missing the commas remain. What do the values do if you include them? The fourth parameter allows you to specify whether the prompt is a multi-select option or not. The value mono (no quotes here) says the prompt is restricted to a single value. The value multi (again, no quotes) allows for multiple values to be selected. The fifth parameter determines whether the user can type the prompt response or if they are required to pick from the list of values. The value free does the former, and the value constrained requires the LOV selection. Here’s a hint: don’t make a prompt constrained without providing a valid list of values source. ;-)

So those are the five basic arguments for the @Prompt() function. Most of you will have noticed that none of them have anything to do with providing a default value. :-?

@Prompt() Extended Syntax for Web Intelligence

We got some new prompt options starting with Web Intelligence 6.x. One of the options a report writer could use was whether or not the last prompt value entered was the default for the next report run or not. Another option was whether the prompt had a default value or not. And finally, report prompts could appear in a user-specified order rather than in alphabetical order as before. But all of these options were report specific and not supported in Universe Designer.

Here’s what the extended prompt syntax looks like:

@Prompt('Enter value(s) for Something:','A','Class\Something',Multi,Free,Persistent,{'Default Option'},User:0)

I’ve already covered the first five parameters so now I’ll detail the last three. The sixth parameter has the value Persistent or Not_Persistent. The default value is to retain the last value entered (Persistent). This is also the legacy behavior; previous versions of the reporting tools only worked in a persistent manner.

The seventh parameter has a syntax that looks a bit like the hard-coded LOV from earlier with the curly braces. This is where a default value for the prompt can be stored. And finally, the last parameter determines the order of the prompt, starting with zero (0) as the first prompt and incrementing from there.

To reiterate something I stated earlier: These are report options only. These parameters can be entered into a prompt object definition in the universe, but they are unlikely to parse because of the extended syntax. In order to test this syntax I would have to export my universe and test in a Web Intelligence query.

What About That Default Value?

Extended prompt parameter number six: a default value. That brings me back to the original subject for this post, doesn’t it? I wanted to have a value like “sysdate” or a formula of some kind like CurrentDate() plugged into that parameter. Unfortunately, it doesn’t work. Any value entered for that parameter is treated as data, and no formulas or calculations will be calculated. So anything like sysdate or getdate() or CurrentDate() is going to fail.

The next trick is to try to reference an object, and store the formula as the object definition instead. Unfortunately, again, it fails. The object name becomes the default value for the prompt which is generally not the intended result.


What it boils down to, as I see it, is this: you can’t do it. There is no way to create a prompt syntax that will substitute in a value like “today’s date” for a prompt default value. It would be nice, but it doesn’t work.

However, I do have a work-around that I’ve used for many years. I create a “magic date” and some creative logic within the prompt to do the substitution after the user has already responded to the prompt. That’s the subject for the next post in this series. Stay tuned for details. 8-)

Things I really REALLY wish prompts could do…

  1. Set a default with a variable. It’s the subject of this post, need I say more?
  2. Be optional! With XI 3.0 a report writer can create optional prompts now, but we still can’t do them in Designer. Boo.
  3. Do cascading prompts effectively. This has been on my wish list for-EVER. I have another post about cascading prompts coming up soon so I’ll save the majority of my whining until then.
  4. We have free and constrained, I want a third option: validated. This would be the best of both worlds. A user would be “free” to enter the value if they wanted to, but it would be validated against the LOV provided in the universe. Today I have to allow the user to shoot themself in the foot by typing the wrong value, or I have to force them to use the LOV.

63 Responses to “How Can I Make “Today” My Default Prompt Value?”

  1. Comment by Roshni

    Another nice to have would be – Validate the prompts- not against LOV but against a business rule.
    For eg 3.0 has optional prompts in webi…And thats a great advantage. but how about prompt 1 and prompt 2 are optional- BUT filling one out of them is mandatory. Right now, a user would leave both blank, we’d run the query with those invalid prompts, and then we’d hide the data and instead show a message like “U should have selected at least one out of them, stupid!”. This is very easy to do in javascript, and the advantage is that it can be done BEFORE the query is run- rather than afterwards. If its wrong anyway, why do we need to wait to know.

  2. Comment by jsanzone

    And another nice to have would be the ability to order prompt as a report writer similiar to how we can order tabs in the “Edit Report”, but re-ordering prompts in the “Edit Query” mode as well. The use of “user:0″, “user:1″, etc, is not always applicable when dealing with many objects in Designer, and then going for execution for a report.

  3. Comment by james

    Hi Dave!

    re: #2
    in 3.0 we can define optional prompts in universe! For example,
    @Select(Batch Type\Batch Type Code) = @Prompt(’Batch Type’,'a’,'Batch Type\Batch Type Code’,mono,constrained,Persistent,{},User:1,Optional)
    and it is working WebI.


  4. Comment by roshknee

    Theres a problem in the 3.0 implementation of optional prompts. You can order webI prompts only. If you have a combination of universe and report level prompts, and this happens often, then you cant order the prompts! In the universe condtion you may put your prompt as sequence 2. But in the reports, you would see 2 prompts, and you can only order between them. So finally you have 2 prompts at sequence 2, and you cannot control which appears first.

  5. Comment by Dave Ledgett

    Hi James,
    I wonder if you would be kind enough to expand on your example of developing a prompt in a universe. As i am relatively new to BO so i would be interested to how one would go about . Do i need to create a dimension and then write the statement in the select box.
    Any further assistance would be appreciated


  6. Comment by Irishorizon


    Can we not use something like the following

    @Select(Date\Value) = CASE WHEN
    @Prompt(’Select a Time Period’,'A’,'Date\Value’,mono,free,not_persistent,{’Current Year’})= ‘Current Year’
    ELSE @Prompt(’Select a Time Period’,'A’,'Date\Value’,mono,free,not_persistent,{’Current Year’})

    I have assumed that the user would like to have the Current Year as default. I am sure this can be modified to have the Default as DATE, of course, depending on the syntax differing upon the selected Database.

  7. Comment by Dave Rathbun

    Hi, Irishorizon, welcome to my blog and thanks for your comment. The difference between what you have posted and what most folks want is that your data is “alpha” and therefore anything is allowed. Also you’re not putting in a true value but the text placeholder of “current year” which is okay for some, but won’t work with a true date prompt.

    If you want a date prompt then you need ‘D’ as the prompt type which – as an added bonus – gives users the calendar to pick from.

    But what people generally want in this case is for when they run the report for the date value to automagically become today’s date (rather than the text string “today”) on each refresh. That, unfortunately, we cannot do.

  8. Comment by Jon Smyrl

    Hi All -
    Can anybody confirm that they were able to get the syntax posted by James to work?

    Everytime I try running a WebI report using a universe-level filter object containing the ‘optional’ parameter, I get a WIS 00027 error message. As soon as I remove the ‘optional’ parameter, though, everything works fine.

    Looking forward to hearing your thoughts!

  9. Comment by Mark Penner

    Hey Jon,

    I am getting the same error and have not found anyway to correct it. It only works when you do select a value, which makes the optional parameter pointless.

  10. Comment by Rizwan SM

    Hi there is a way to implement today’s date as a default value in the prompt.

    but u need additional coding !

    I think people here are not completely wrong .. they are right but to a certain extent.

    With my experience with BOXI , i have seen that most of the things have work arounds.

  11. Comment by Dave Rathbun

    Rizwan, welcome and thanks for your comment. If you have a platform independent way to provide “today” as a default date that can be done within the universe (meaning no VBA or other scripting) please share. ;)

  12. Comment by yadavendra singh


    Thanx a lot for all ur posts it help me a lot in creating prompts and giving the default values.

    yadavendra singh

  13. Comment by Manoj

    Exactly what I was looking for and also has clear explanations. Dave I did not find a better explanation anywhere else on the web. Thanks!

  14. Comment by Marc Burger

    Done in XI 3.1 with Oracle. Requirement: User wants report to default to “yesterdays date” when he/she opens it the first time to view. (Scheduling not a option, don’t ask…) User also wants option to select “Custom Date range”
    1.You have a date time field = “DateX”
    2.Build another object of the same field in unv = “Date X New Object”
    case when (DateX between trunc(sysdate – 1) and trunc(sysdate)) then ‘Yesterday’
    when (trunc(DateX) = trunc(sysdate)) then ‘Today’
    when (DateX between trunc(sysdate – 10) and trunc(sysdate+1)) then ‘Last 10 Days’
    when to_number(to_char(DateX,’yyyy’)) < 1900 then ‘Custom’
    3.Build Condition in Unv = Date Picker
    @Select(Cass\DateX New Object) = @Prompt(’Date Picker’,'A’,{’Custom’,'Yesterday’,'Last 10 Days’,'Today’},mono,constrained,persistent,{’Yesterday’})
    (To more about this code, consult Dave’s blog.)
    4.Build Webi Report.
    Add Date X result object **DO NOT ADD DATE X NEW OBJECT
    Add Query Filter “Date Picker”
    Test your results and you should get a parameter which allows you to choose ‘Custom’,'Yesterday’..etc.
    Verify it works prior to continuing, ‘Custom’ will not return anything at present.
    Next add Date X to query filter in Webi. Set it be between to date prompts. In the option screen, only tick optional prompt and make sure everything else is unticked.
    Then, make the two filters (Date Picker + Date X Filter) part of an “OR” statement.
    Run and test the report now.
    Your end result should be when you select Custom date, no data returned and thus report will use next prompt which is your optional date range.

    Hope this helps a few people.

  15. Comment by Marc Burger


    As they say, give someone a hand and they’ll take your arm. Based on my previous comments I’ve made, lets say the user does’nt want to see the word “yesterday” or “today” in the report header.

    Try this, its for Oracle though, but I’m sure you SQL nuts will get the idea:

    Create a derived table.
    Example of Code:
    ‘Today’ as Date_Range,
    TO_CHAR(trunc(sysdate),’dd/mm/yyyy’) as Begin_Date,
    TO_CHAR(trunc(sysdate),’dd/mm/yyyy’) as End_Date

    Union as much these as you want together. Typicall they should contain all the values for the parameter already built as previous post.
    Add details to seperate date class.

    Save + export universe.

    Add new query to report.
    Create new report tab and add results from date clase.
    Add two new colums and add formula below for BEGIN and END date.

    Example of code:
    =If(UserResponse(”My Date Param name:”)=[Date Range]) Then [Begin Date]
    Test that in the new report tab and you’ll see it’ll only display the relevant date.
    Create a variable from this formula code. Call them “Begin Date Display” and “End Date Display”

    Create a new variable for report header section.
    Code will look something like this:
    =”My new special code dates: ” + If(UserResponse(”:”)=”Custom”) Then (UserResponse(”Begin Date:”) +” to “+UserResponse(”End Date:”) Else [Begin Date Display] +” to “+ [End Date Display]

    Trick here is to have your header text as a MEASURE. Having it as a dimension or detail will fail.

    Its a very long approach, but it work. There is nothing that cannot be done in BusinessObjects, its just takes a little longer.

    Lets hope they sort this out and give us more flexible parameters in future.


  16. Comment by Bill


    I am looking for a way to make the parameter optional ie. if the user doesn’t want to input any value, they can leave it NULL (blank). Is this possible through @prompt function? if so then how. Thanks

  17. Comment by Dave Rathbun

    Hi, Bill, and thanks for visiting my blog. The answer to your question is covered in the “things I wish prompts could do” section at the very end of the post.

    Be optional! With XI 3.0 a report writer can create optional prompts now, but we still can’t do them in Designer.

    So report writers can make optional prompts, but officially we can’t yet put them into the universe design. You might be able to grab the syntax from a Webi report and copy it into a prompt definition, and it might work, but it’s not officially part of the syntax.

    Not yet, but I have hopes. :)

  18. Comment by Dacha

    Hello everyone,

    I have beginner question, is there possibility to call dataprovider(sql query) in prompt and execute variable seted in that query.

    Thanks in advance.

  19. Comment by Dave Rathbun

    Hi, Dacha, and thank you for your comment. The DataProvider() function is a report function, and cannot be evaluated by the database within the SQL code.

  20. Comment by Dacha

    thx Dave.

  21. Comment by Douglas Lange

    Hi Dave.

    Thanks for running this site! I’ve been using this as a guide for a problem I have in BO, and thanks to the posts I found here I was able to solve my problem. The users want the option to select an ending month for a report, but they also want it to default to the previous month (by number), so there was a need for a default based on a date calculation. Getting the static list of month numbers in the prompt was easy; the hard part was a dynamic default. After a couple of days of tweaking the case statement, this solution worked in the Universe Designer (ver 12.0, w/WEBI 12.0)(set the object return value as a number). The value can be compared in WEBI to a month number or used however.

    case @prompt(’Enter the ending month number (1 – 12):’ ,’A',{’Last Month’,'1′,’2′,’3′,’4′,’5′,’6′,’7′,’8′,’9′,’10′,’11′,’12′},Mono,constrained,Not_Persistent,{’Last Month’})
    when ‘Last Month’ then To_Number(To_Char(add_months(sysdate,-1),’MM’))
    else To_Number(@prompt(’Enter the ending month number (1 – 12):’ ,’A',{’Last Month’,'1′,’2′,’3′,’4′,’5′,’6′,’7′,’8′,’9′,’10′,’11′,’12′},Mono,constrained,Not_Persistent,{’Last Month’}))

  22. Comment by Shiva

    Hi Dave,

    Under “Things I really REALLY wish prompts could do…” topic… the point 3 is always a points of concern for me… As i have a lot of confusion..

    How cascading prompts are created?
    What are the things we should keep in mind while creating a cascading prompts?
    How they work?
    How are they related to cascading LOVs?

    I’ve once tried when i started learning BO on my oown and i couldn’t do it .. as i knew no one working on BO at that time.. Ever since i’m not able to do it or understand it.

    Could you please explain these with example?? This help will be deeply appreciated. :D


  23. Comment by Dave Rathbun

    Hi, Shiva, as I mentioned in the post I will use that as a topic for a later blog post. :)

  24. Comment by Jayanth Kumar

    I’ve been able to use the trick suggested by Marc Burger and tweaked it up a bit.

    I have a report that defaults to Current Month to Date as well as gives a prompt option to be able to select different dates. I’ve tried scheduling the report with these options and it runs sweeeeet.

    Thanks a lottttttt to Marc for having shown a way ahead and thanks Dave for this very useful blog of yours:)

    Jayanth KN

  25. Comment by Dacha

    Hi Dave,

    I have one question for desktop intelligence.
    SQL-1 return data set and I want (if is it possible) to use one of the dimension variables as input in another SQL-2

  26. Comment by Anjali

    Hi Jayanth,
    Can u please tell me how did u achieve it. I have to schedule a report to run on every monday. The req is that there is a promt : Start Date .It should take the sysdate of every monday and run the report.And the user should also be able to enter a value in the prompt when he wants to run the report.

  27. Comment by Marc Burger

    Re comment by Anjali:

    Hi Anjali,

    To schedule reports, you need to have defaults. So I achieved this by setting default values on the universe prompt:

    @Select(Rep01\Arrival Date Object) = @Prompt(’Reporting Period:’,'A’,{’Today’,'Yesterday’,'Custom’},mono,constrained,not_persistent,{’Yesterday’})

    Thus “yesterday” is the default date and what is used when scheduling.
    When a user runs the report manually, they have to select “Custom” from the picklist. If you follow the intructions I mentioned previously, you will see this will no return no values untill you use your “secondary” prompts which is in actual fact your webi prompts which are set to optional.

    One behaviour we have noticed with the date objects as described above, if you don’t have a table, its best to seperate the code for “yesterday” and “month to date” and then have i.e “previous month”. If you need more detailed explantion let me know and I’ll update when I have time.

    Hope that clears it up for you.

  28. Comment by Daif

    My Question is from Island Resort Mark. Can i keep a single prompt on Country as Country =US and Country Not Equal to US (rest of the values)in Designer, If the user selects Country = US it should show US Data and if Not Equal to US i should show rest of the countries Data?

  29. Comment by Dave Rathbun

    Hi, Daif, thanks for your comment. I would not implement the solution as I think you described it. Instead, I would create a predefined condition in my universe that prompts the user by asking, “Do you want US or All Other?” If the user answered US, then only US rows would be returned. If the user answered “All Other” then only non-US rows would return. You can’t change the operator in a prompt; meaning you can’t ask the user if they want “equal to” or “not equal to” as a part of the prompt process. You have to handle it with logic that processes their requested result instead.

  30. Comment by Dan Fields

    I usually make contidions in my universes that contain the code below for my various date fields.

    WHEN (@Prompt(’Expiration Time Period’,'A’,{ ‘Last Full Month’,'Year to Date’, ‘Last 12 Months’},mono,free)) = ‘Last Full Month’
    WHEN (@Prompt(’Expiration Time Period’,'A’,{ ‘Last Full Month’,'Year to Date’, ‘Last 12 Months’},mono,free)) = ‘Year to Date’
    WHEN (@Prompt(’Expiration Time Period’,'A’,{ ‘Last Full Month’,'Year to Date’, ‘Last 12 Months’},mono,free)) = ‘Last 12 Months’
    THEN add_months(TRUNC (SYSDATE,’MM’),-13)

    WHEN (@Prompt(’Expiration Time Period’,'A’,{ ‘Last Full Month’,'Year to Date’, ‘Last 12 Months’},mono,free)) = ‘Last Full Month’
    WHEN (@Prompt(’Expiration Time Period’,'A’,{ ‘Last Full Month’,'Year to Date’, ‘Last 12 Months’},mono,free)) = ‘Year to Date’
    WHEN (@Prompt(’Expiration Time Period’,'A’,{ ‘Last Full Month’,'Year to Date’, ‘Last 12 Months’},mono,free)) = ‘Last 12 Months’


  31. Comment by Dave Rathbun

    Hi, Dan, and thanks for your input. It’s similar to another solution posted earlier in the comments. My observation on what you have posted is that it works perfectly if the user only uses text tokens that get translated to date values. It gets more complex if the user wants to mix text tokens with actual dates.

  32. Comment by Zaif

    Hi Dave,
    So can we manage to get the desired results using “magic date” and your some creative logic, If yes then waiting for details.

  33. Comment by Dave Rathbun

    Zaif, what are your desired results?

  34. Comment by Zaif

    Hi Dave,
    I mean Can I Make “Today” as My Default Prompt Value?

  35. Comment by Orbica


    We did the following for making “Today” as our prompt date. Go to the universe(s) that are used to build a report. Go to “Insert condition” and under Where of the condition we put:

    @Select(Date\To date)=decode(@Prompt('1-To date:','D',,mono,free),'t',trunc(sysdate),@Prompt('1-To date:','D',,mono,free))

    Which means that a user can input any date he/she wishes when manually refreshing reports, and we can put t (or anything else) in the date prompt when scheduling, and it will take sysdate whenever the scheduler is set to run. Then, instead of creating a prompt in the report, you just drag that condition from the universe to your report DP conditions and that’s it. It works perfectly in BOXIR2…

    N.B. THIS DOES NOT WORK IN BOXI 3.1 (even though it parses OK)!!!

    If anyone has an idea why this doesn’t work in the new BOXI3.1 FP3.2, and/or how to make it work, please help!

  36. Comment by Dave Rathbun

    That’s what the blog post is about, which is why I asked for clarification. There are plenty of good suggestions within the post, and many more within the comments. I don’t understand how what you are asking is different.

  37. Comment by rig

    Hi Dave,

    I work on BOXI R3.1. This particular universe is on the data in MySQL database.
    I use the following formula to set a formula based default value for the prompt:

    Year = @Prompt('Select the past year since when you wish to view the data:','N','Dates\Year',mono,constrained,persistent,{'(YEAR(CURRENT_DATE) -3)'})

    the formula: Year(CURRENT_DATE) -3 sets a default value for the prompt in case the user does not select any value.

    As I found out, what ever is provided inside {”} as the default value, is treated as text and placed as it is in the SQL query that gets generated.
    Hence, what I put inside that is: (YEAR(CURRENT_DATE) -3)

    as a result, in the SQL that gets generated, the where condition will appear like year = (YEAR(CURRENT_DATE) -3)

    i think on the same lines we can use {’(CURRENT_DATE)’} [in case of MySQL] to set today as the default value for the prompt

  38. Comment by Didy

    Hi Dave,
    I am using BOX1r3 and have a requirement to add a default value in my prompt(dynamic value). I have posted in BOB for this question but unfortunately from the reply looks like this cannot be done. I’ve tried also to create @prompt by putting a default value, but it doenst work.
    So I am trying again my luck to check from your forum if this is availe, adding a dynamic value as a default value.
    My customer request to add a default value of a maximum/latest quarter that is flagged thru a web based system. I have created an object to get this latest/maximum quarter(this is working). But i have difficulties in order to add this object as a default value in the @prompt. Any tips/tricks from you? Please advise. Thanks.

  39. Comment by Dave Rathbun

    You cannot put in a dynamic value for a default for a prompt. You have to create some sort of work-around where a hard-coded value gets dynamically reassigned, which is the technique outlined here in this blog post.

  40. Comment by Didy

    Hi Dave,
    Thanks for your reply. So basically I have created an object to select the maximum/latest fiscal year & quarter and names this as Max FYQ. Query fo
    Max FYQ =
    Select : max(Fiscal_Year+PCQR_Quarter)
    Where : Archive IS NOT NULL

    Then I create this @prompt(I have error when tried to parsed this prompt, which I dont understand why):
    @Prompt(’Enter Fiscal Year & quarter’,'A’,’History Issue\FYQ’,mono,constrained,persistent,{’Max FYQ’})

    I received errror : Error parsing default values parameter (7th parameter)

    I’m not sure where should i tweak my syntax at the moment..if anyone can share yours, would be appreciate as well..

  41. Comment by Dave Rathbun

    You can’t put an object in as a default value, it has to be a value. That’s what this blog post is all about; using the “magic date” to translate into a dynamic value. There are other options listed in the comments that have been provided by other folks reading this post. But you have to use a constant (either a constant date or a string or a numeric value) as the default value for a prompt.

  42. Comment by Didy

    Thank Dave..since my requirement is a dynamic value as default value, it’s time to educate the user that this is impossible to implement at this moment..anyway, appreciate your advise :)

  43. Comment by Andreas (Xeradox)

    What you can do though is use “TODAY” for example as a value, but you will loose the calendar picker that way for regular date entries

  44. Comment by Dave Rathbun

    Andreas, Michael Welter covers how to do that in a blog post of his own:


  45. Comment by Anna

    Hi Dave,

    I have a requirement where in I have two derived tables :
    1) derivedtable1 – startdate
    2) derivedtable2 – enddate
    Each of these tables have statements like select ‘01 preivous business day start’ as id UNION ‘02 Previous week day start’ UNION etc. (Similarly for the enddate). and the case statements are defined in another table
    as prompts with (CASE @Prompt(’1. Start Date in MM/DD/YYYY format / Select Quick Date’,'A’,'DATE\Derivedtable1′,MONO,FREE)
    WHEN ‘01 Previous Business Day_Start’ THEN
    case (trim(to_char(current_date,’day’)))

    WHEN ‘02 Previous Week Start Date’ THEN to_date(date_trunc(’week’, (to_date(date_trunc(’week’, current_date),’yyyy-mm-dd’)-1)), ‘yyyy-mm-dd’)
    WHEN ‘03 Month-To-Date Start Date’ THEN to_date(date_trunc(’month’, current_date),’yyyy-mm-dd’)
    etc…. and
    WHEN ‘07 Last 365 days from the Previous Business Day’ THEN (current_date- cast(’365 days’ as interval))
    WHEN ‘08 Two weeks from the Previous Business Day’ THEN (current_date- cast(’14 days’ as interval))

    Then the case statements for enddate are also mentioned in a similar fashion below this with appropriate prompts.

    My concern is when the user selects Last 365 days from the Previous Business Day or Two weeks from the previous Business day as his start date options, he should get only One option for enddate which should be “Previous Business day End”. I dont want him to select any other options from the end date for these two cases alone. So how do I limit this. Please provide me with an example code for it.

    Thanks in advance.

  46. Comment by Dave Rathbun

    Unfortunately there is no way to validate one prompt with rules based on another prompt. It is one of the enhancements requested for the next generation of the semantic layer. The only way we have to do this today is to either pre-determine what the second value should be based on the first (thus avoiding a second prompt, but removing some flexibility) or to include the first prompt as a part of the second in the form of a cascading prompt event. The disadvantage there is the user has to select the same value both times… and they have to do it twice. There is still no way to enforce that the second selection matches the first because the prompts are independent.

    Put another way, the user is free to enter a response for the second prompt before they even touch the first prompt. That shows that each prompt is fully independent of the other.

    You could add some SDK code as a custom prompt handler but that’s beyond the scope of what I’m able to provide on my blog.

  47. Comment by sd

    The optional prompt as suggested by james giving Error
    “Removing skipped prompts from the query will introduce new prompts. This introduces complexities that cannot be supported. (Error: WIS 00027″ any updated on this.
    I am on XI 3.1

  48. Comment by Suganya

    Hi Dave,
    Could you also explain how to work on ‘query prompts’ in DeskI. When I tried to insert it, the option looks disabled. Is there any settings to enable this option or how else can I insert query prompt in DeskI report.

  49. Comment by Dave Rathbun

    Prompts are either created in the universe (as prompt objects) or on the query panel, not on the report itself. Is that what you’re trying to do? When you create a condition on the query panel, one of the operands should allow you to configure a prompt.

  50. Comment by Didy

    Did you find the solution for your error (WIS 00027) as I also received the same error when created an optional prompt in the Universe and pull this prompt in the Webi. It works if a value is selected but if no value selected it give this WIS00027 error. I’m confused now as I saw some people said this optional prompt is working fine for them, but some complaint it didnt work (same as my situation).

  51. Comment by Dave Rathbun

    Optional prompts are not currently supported within a universe. They might work, but they’re not expected to, nor will the concept be supported by SAP. I have seen cases where users would generate a prompt in Web Intelligence and then copy the SQL back to an object in their universe and it worked… but I have also seen cases where it failed.

    Another consideration: Even if you get it working, there is no guarantee that it will continue to work during subsequent upgrades.

  52. Comment by Dmytro

    I CAN DO THIS! :)

    Here is the trick using optional prompts:


  53. Comment by Bala


    I have universe built on SAP BW 7.1, we’re on Business Objects 3.1. I want to have a default prompt as sysdate, when the user try to schedule the report. I can’t create any prompts based on the database functions as its not allowing at universe side. Request your help.

  54. Comment by Dave Rathbun

    BW is an entirely different animal. I understand there are things that can be done on the back-end since the ability to do manipulations in the universe is so limited, but I’m not a BW person. I’m afraid I won’t be able to help with this question.

  55. Comment by Andreas J.A. (Xeradox)

    For SAP BW and date prompts defaulting to sysdate/current date –> ask your Bex query Designer and ABAB programmer to create some so called “customer exit code” within SAP BW to create an so called “input ready variable”, which if left empty is automatically substitued by sysdate/current date.

  56. Comment by Abida Arif

    SAP Global Support has no answer for this basic functionality yet. I did some workaround within the Universe but this is not a solution, these workarounds may or may not fulfil the requirement.

    1)Create current date field in class:
    Case when 1=1 then Convert(varchar(30), getdate(), 101) else Convert(varchar(30), Table_Name.createdDate, 101) end

    2)Create universe prompt filter for date:
    @Select(Table_Name\createdDate) Between @prompt (’1. Enter Start Date: ‘, ‘D’, ['Table_Name\Your_Getdate()_Field'], mono, free) AND @prompt (’2. Enter End Date: ‘, ‘D’, ['Table_Name\Your_Getdate()_Field'], mono, free)

    You can manipulate date or use any date function according to your need in universe promtp filter.

    3)In Webi Report set report option refresh when open so current date will always be available.

  57. Comment by JGraves

    I have a pre-defined condition for business month that defaults to current date. I also have a optional prompt for date range. I want to query for date range if the user enters it without deselecting the default business month. I am working on BI4 Web Intelligence against SQL Server 2012 database.

  58. Comment by Will M

    Hello Dave,
    In my webi report I am prompting for a date, @prompt(’Enter Date:’,'D’,,Mono,Free,Persistent,,User:0)
    but do not wish to have the date and time value offered such as 8/15/2014 12:00:00 AM. Is there a way that the prompt or date picker can be formatted to only show the date 8/15/2014?
    Thank you

  59. Comment by Joyce

    Hi Dave,

    I have been using date range prompt(sample below) in most of our universes. But would like some advice on making it an optional prompt. Appreciate your expertise on this. Thank you.

    (to_char(PO_Hdr_OrderDt.ACTUAL_DATE, ‘yyyymmdd’) >= decode(@Prompt(’Select timeframe for the report:’, ‘A’, {’Current Month’, ‘Last Month’, ‘Enter From and To Dates’}, MONO, CONSTRAINED, , User:90)
    , ‘Current Month’ , to_char((select sysdate from sys.dual), ‘YYYYMM’) || ‘01′
    , ‘Last Month’ , to_char(add_months((select sysdate from sys.dual), -1), ‘YYYYMM’) || ‘01′
    , ‘Enter From and To Dates’,@Prompt( ‘FROM Date(YYYYMMDD):’, ‘A’,{’NA’}, MONO, FREE, ,{’NA’} , User:98)
    to_char(PO_Hdr_OrderDt.ACTUAL_DATE, ‘yyyymmdd’) < decode(@Prompt('Select timeframe for the report:', 'A', {'Current Month', 'Last Month', 'Enter From and To Dates'}, MONO, CONSTRAINED, , User:90)
    , 'Current Month' , to_char(add_months((select sysdate from sys.dual), 1), 'YYYYMM') || 01
    , 'Last Month' , to_char((select sysdate from sys.dual), 'YYYYMM') || '01'
    , 'Enter From and To Dates', @Prompt('TO Date(YYYYMMDD):','A',{'NA'},MONO,FREE, , {'NA'}, User:99)

  60. Comment by Fred T

    Prompts in BO Universe are rubbish unless…you use Business Views and Crystal Reports which have awesome prompt configurability and cascading prompts….

    Obviously Business Views are no longer on the BI roadmap for SAP, nor is it helpful in your situation as you are using WEBI.

    Hopefully IDT will bring the best of “Business Views” and “Universes” together (one hopes…)

  61. Comment by praveen

    In prompt properties you have an option default prompt.
    check the check box and select Now.

  62. Comment by Doug S

    10+ years later and this is still an issue?

  63. Comment by Mohsin

    Hey Can we use @prompt function in TRUNC, we are using EDH redshift as a backend.

    trunc(@Prompt(’Enter Business Date:’,'D’,'Tender Summary\Business Date’,'MONO’,,), ‘MM’) As I want to take the first date of the selected date month,

    Suppose User select 11-04-2019 then it will fetch the data from 1-04-2019 to 11-04-2019.

    Thanks in advance

Leave a Reply

If you want to include formulas or code in your comment, please read my Tips for formatting comments first. Tags you can use are listed below.

XHTML: You can use these tags: <a href="" title=""> <acronym title=""> <blockquote cite=""> <code> <em> <strike> <strong> <sup> <sub> <u>

Confirm submission by clicking only the marked checkbox:


Please remember that comments that are not related to this blog post may be ignored or deleted without notice. If you're looking for help on a topic you have already posted on BOB then please do not repost your question here.