I’ve heard you can write code in a condition object such that it will examine a default value (like NULL) in a prompt (using @Prompt) and upon that value being true then using something like (current_date -1) for the prompt value. If the prompt value is other than the NULL value, it then processes the query with the prompt value entered by the user. The code, I’m told, looks something like this:
CASE WHEN @Prompt(‘Select a date’,‘D’,‘Time\Metric Date’,Mono,Constrained,not_persistent,) IS NULL then (current_date -1) else @Prompt(‘Select a date’,‘D’,‘Time\Metric Date’,Mono,Constrained,not_persistent,) END
However, this code will not parse against the database. I’m trying to create a Webi report that has a dual role: 1. Used in a schedule where the default value is used to refresh. and 2. Used as a report on demand in corporate documents were users will key values to satisfy the prompt.
Your code is good …
Try to define a pre-defined condition with the above code.
Table.date = CASE WHEN @Prompt('Select a date','D','Time\Metric Date',Mono,Constrained,not_persistent,) IS NULL then (current_date -1) else @Prompt('Select a date','D','Time\Metric Date',Mono,Constrained,not_persistent,) END
Sometimes I have seen that Designer will not parse the CASE WHEN… syntax but when you use the object/condition in the report it will work fine.
I dont think you can achieve those dual requirements in one single report having a prompt.
For a report to run the prompt has to be filled and you cannot just say that if nothing is passed or left blank(null), it should take a default date value and refresh the report.
You will have to keep two different copies of same report, one with default date value and the other with prompt.
Actually, you don’t. At my current project we have what we call a “magic date” that is converted to the current date during a schedule. The date is 1900-01-01 and the logic (pseudo-code) looks like this:
case when prompt is magic date then today else prompt date value end
You are correct in that you cannot leave a date blank… but by providing a default or “magic” date that gets converted to the system date (or yesterday, as in the original post) you can still generate the required result.
I’m now using a ‘magic date’ as you say but with no results. Here’s the code:
Tablename.Date = (CASE WHEN @Prompt(‘Select a date’,‘D’,‘Time\Metric Date’,Mono,Constrained,not_persistent,‘1900-01-01’) ='1900-01-01’ then (current_date -1) else @Prompt(‘Select a date’,‘D’,‘Time\Metric Date’,Mono,Constrained,not_persistent,) END
I want the Webi schedule to process the query using current_date -1 and then want users to refresh the report using whatever date they select from the list of values. The scheduler wants a value for the prompt naturally but I have to select from the list of values and 1900-01-01 is not in that list. Can you elaborate more? Thanks.
Dave,
The trick is cool.
But when we are saying that the prompt = ‘magic date’, is the magic date really passed to the prompt dynamically as there will still be a user interaction to pass that magic date into the prompt? Or I missed something
As BO_chief pointed to a thread where Nick says to pass a ‘0’ to the prompt before scheduling the report, are these two different approaches?
The user has to pick or enter the date; there is no “magic” to make the date appear if no date is provided. In our case, the date logic does not require a user to select from a list of values, so they simply enter the date during the schedule process.
This is important to me at this time so I need to get a complete understanding. When you say
…I don’t follow. I cannot enter a date during the schedule process. It makes me choose from a list of values and 1900-01-01 is certainly no one of them. I understand the code syntax but the default ‘magic date’ is not being recognized at the default date for the prompt and thus I cannot get the current_date -1 to be used for the prompt value in the schedule. Could you elaborate? Thanks. [/quote]
If the prompt is “free” then a user can enter whatever date they want. If the prompt is “constrained” then the user has to pick from the list of values. In that case, you have to ensure that the “magic date” is a part of the LOV, probably by using this technique from the Designer FAQ.
FWIW, I don’t subscribe to the idea that date prompts should have a list of values… dates are not like other data in that everyone knows what the values are. The exception is if the data has some restriction, like you can only select “Saturday” dates or something like that.
(BOB member since 2004-01-20)
(BOB member since 2005-07-18)
At my current project we have what we call a “magic date” that is converted to the current date during a schedule. The date is 1900-01-01 and the logic (pseudo-code) looks like this:
[/quote]