Jul 15 2008
How Can I Make “Today” My Default Prompt Value?
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:
{'Yes','No'}
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.
Conclusion
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. 
Things I really REALLY wish prompts could do…
- Set a default with a variable. It’s the subject of this post, need I say more?
- Be optional! With XI 3.0 a report writer can create optional prompts now, but we still can’t do them in Designer. Boo.
- 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.
- 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.
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.
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.
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.
Regards,
james
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.
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
Thanks
Dave,
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’
THEN YEAR(CURRENT DATE)
ELSE @Prompt(’Select a Time Period’,'A’,'Date\Value’,mono,free,not_persistent,{’Current Year’})
END
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.
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.
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!
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.
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.
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.
dave
Thanx a lot for all ur posts it help me a lot in creating prompts and giving the default values.
yadavendra singh
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!
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”
Steps
1.You have a date time field = “DateX”
2.Build another object of the same field in unv = “Date X New Object”
code:
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’
end
3.Build Condition in Unv = Date Picker
code:
@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.
Process:
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.
Hi
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:
SELECT
‘Today’ as Date_Range,
TO_CHAR(trunc(sysdate),’dd/mm/yyyy’) as Begin_Date,
TO_CHAR(trunc(sysdate),’dd/mm/yyyy’) as End_Date
from
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.
M
Hi,
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
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.
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.
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.
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.
thx Dave.
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’}))
end
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.
Regards,
Shiva
Hi, Shiva, as I mentioned in the post I will use that as a topic for a later blog post.
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:)
Regards
Jayanth KN
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
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.
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.
Marc
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?
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.
I usually make contidions in my universes that contain the code below for my various date fields.
DM_OLAP.POLICY_ALL_PIT.INCEPTION_DATE BETWEEN
CASE
WHEN (@Prompt(’Expiration Time Period’,'A’,{ ‘Last Full Month’,'Year to Date’, ‘Last 12 Months’},mono,free)) = ‘Last Full Month’
THEN TRUNC(ADD_MONTHS(SYSDATE,-1),’MM’)
WHEN (@Prompt(’Expiration Time Period’,'A’,{ ‘Last Full Month’,'Year to Date’, ‘Last 12 Months’},mono,free)) = ‘Year to Date’
THEN CASE
WHEN TO_CHAR(SYSDATE,’MM’) = ‘01′
THEN TRUNC (ADD_MONTHS(SYSDATE,-12),’RR’) ELSE TRUNC (SYSDATE,’RR’) END
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)
END
AND
CASE
WHEN (@Prompt(’Expiration Time Period’,'A’,{ ‘Last Full Month’,'Year to Date’, ‘Last 12 Months’},mono,free)) = ‘Last Full Month’
THEN TRUNC (SYSDATE, ‘MM’) – 1
WHEN (@Prompt(’Expiration Time Period’,'A’,{ ‘Last Full Month’,'Year to Date’, ‘Last 12 Months’},mono,free)) = ‘Year to Date’
THEN TRUNC (SYSDATE, ‘MM’) – 1
WHEN (@Prompt(’Expiration Time Period’,'A’,{ ‘Last Full Month’,'Year to Date’, ‘Last 12 Months’},mono,free)) = ‘Last 12 Months’
THEN TRUNC (SYSDATE, ‘MM’) – 1
END
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.
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.
Thanks
Zaif, what are your desired results?
Hi Dave,
I mean Can I Make “Today” as My Default Prompt Value?
Thanks
Zaif,
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!
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.
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
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.
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.
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..
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.
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
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
Andreas, Michael Welter covers how to do that in a blog post of his own:
http://michaelwelter.wordpress.com/2011/02/08/date-prompt-with-default-today/
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.
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.
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