Jul 21 2008
Using a “Magic Date” Value in Prompts
In the first post on designing universe prompts I talked about the idea of having a default date value present in a prompt. In case you don’t want to read the entire post, here is the executive summary:
You can’t do it, sorry.
At the end of that post I mentioned something called a “magic date” that I frequently use in my universes to get past this limitation. That’s my subject for today.
Problem Definition
I have a date prompt in my universe. I would like to be able to provide a default value of today’s date. This would allow my users to click-n-go on the report without having to update the date value each time. It is also essential for scheduled reports, which is something I didn’t really touch on last time. When I schedule a report, I have to provide values for every prompt. I have a long series of posts on how to create some dynamic date objects that can be used for scheduling, but those were not prompts.
In a nutshell: I need a way to accept a prompt value and convert it to today’s date for user convenience and for scheduling. Can I make that happen?
Defining the Magic Date
First, a quick recap. When I set up a prompt in the universe, one of the required arguments is the datatype. A ‘D’ designates a prompt as a date value. That means that anything entered in that date prompt field has to pass the basic validation that will be done by the application. I can’t put the word “Today” in this prompt, as “Today” is not anywhere close to being a valid date. That’s where the concept of a “magic date” comes from.
What I do is define a specific date as the magic date. Quite frequently I will use something like January 1, 1900, as it is highly unlikely that any of my records will include that date as a valid entry. That is the first important point about this concept: I must pick a date that will never appear as part of my normal data. That’s my magic date.
Defining the Prompt
Once I have selected my magic date I can now create my prompt using the basic five parameters. It might look something like this:
@prompt('Enter shipping date','D',,mono,free)
In this prompt I am asking for a shipping date, I am expecting a valid date value, there is no list of values, and the user will enter only one value. There’s nothing magical about that prompt. 
The magic comes in when I create the condition object that includes that prompt. Imagine that I have a report that is designed to show orders that shipped today. Suppose that my magic date is January 1, 1900, as I mentioned before. Here’s what the code looks like (I will explain it after):
orders.ship_date = case when @prompt('Enter shipping date','D',,mono,free) = '1900-01-01' then DATE else @prompt('Enter shipping date','D',,mono,free) end
I’ve used Teradata syntax here but the concept applies to any database. (Teradata offers several options to return the date for “today” and DATE is the example I selected here.) One of two things will happen in this condition. A user will either enter a standard date, or they will enter the magic date. If they enter a standard date the code resolves to this:
orders.ship_date = case when '2008-07-21' = '1900-01-01' then DATE else '2008-07-21' end
In this case the case logic eliminates the first option and takes the second option and the net logic becomes:
orders.ship_date = '2008-07-21'
What if the user enters the magic date? Using the same process the code first resolves to this:
orders.ship_date = case when '1900-01-01' = '1900-01-01' then DATE else '1900-01-01' end
Which is logically the same as this:
orders.ship_date = DATE
So, there I have it. By applying a case statement to the right-hand side of this database condition, I can react in two different ways. In one case I take the user parameter value exactly as entered. In the other case I convert a special date (magic date) into the system current date instead. Now if one of my users schedules a report with this prompt, all they have to do is enter 1900-01-01 as their date parameter and each time their scheduled report is executed the parameter value will change.
Conclusion
With some creative date logic I can convert the magic date to today, yesterday, end of this week, end of last week, end of the month… just about anything else that I’ve already covered in my dynamic date series of posts. I can use the concept of a “magic number” for numeric prompts and a “magic string” for text prompts as well, although those are generally not used as frequently.
Would it be nice if I could use a variable in a prompt to avoid this sort of extra work? Sure, I think it would. I listed a bunch of different prompt enhancements that I have been looking for, in some cases for years, in my last post on prompts. One of those enhancements that I (and a gazillion other folks) would like to see is proper cascading prompts. I plan to have that as my next post on prompts. 
Love your blog, Dave. You really cover a lot of the fundamentals that BusinessObjects leaves out of their documentation.
Why don’t they at least include examples of these workarounds in their product? Seems like it wouldn’t be too difficult to incorporate sample formulas into Designer, at least for the major database products like Oracle, SQL Server, DB2 and Teradata. That would get them around having to add complex new functionality to their product, while still allowing users to accomplish what should be obvious requirements.
Are they ever gonna upgrade or enhance Designer?
Dave
That is one way to use the flexible prompt approach but things look to change in XIR3. WHat I have read and not had a chance to test out on our “sandbox” deployment is the ability to bypass a prompt at execution time. It looks like a feature to let the user decide not to include a prompt in the selection criteria built into the SQL. This will of course diminish the need for a lot of the “Flexible filters” as I call them in my universes.
I think that its a much better approach to eliminate the statement from the query rather than examine each record and bypass based on the “keyword” or “magic date”.
The next step for the product would be to provide a checkbox that provides an alternative to the “Yes” / “No” approach and adds a “Use Default Value” feature in that dialog. Then we can code a default function or SQL string when building the query.
Please Dave no magic acts as part of your presentation at the Users Conference this year. Stay with stale jokes routine. They are your strong point and you should always go with your strengths!
Hi, Dan, thanks for your comment. Several years ago (2004, I believe) I was able to participate in a focus group on the Universe Designer product. There were changes that were talked about at that time, including some very vague concepts that might / might not ever make it into the product. Over the years there have been a couple of large acquisitions (Business Objects > Crystal, and then SAP > Business Objects) so I feel like either or both of those events probably had some impact on any scheduled major updates to Designer.
And Mike, thank you as well for your comment. The optional prompt settings that you mentioned are indeed available in XI 3.0 but at the moment they are not officially supported in Designer. As posted in a comment elsewhere on my blog you can build them in Designer, but the intent is that you get to define optional prompts only in the report.
As far as jokes versus magic tricks… I don’t think there is any danger of me turning into an amateur magician. I’ll stick with what I do worst.
I have actually used the keywords “Today” and “Yesterday” for a DATE LOV (Dates converted to CHARARCTER for the LOV only!), then I use a predefined universe condition to check against this LOV: if the keywords TODAY/YESTERDAY are found it is replaced with the DATE/SYSDATE/-1 function. etc. otherwise the @PROMPT is converted from ALPAHANUMERIC to data type DATE. This works quite well and is even nicer than using 1-1-1900, etc. Got this to work on Teradata.
Andreas, that does work… but by converting the date values to character strings you lost the calendar and the data validation that the prompt code does for you, correct? Meaning a user could enter Feb 31, 2009 and you would have to deal with the conversion failure yourself. If the @Prompt() uses a type of ‘A’ then anything is valid for entry. If it uses a type of ‘D’ then the only accepted inputs are dates.
Or did you use a list of values, and require the user to pick from the list? If that is the case, I’m not so much in favor of that either, just because of the sheer volume of values provided on the list.
I am sure that other folks would appreciate your suggestion, so thank you for posting it.
But it’s not a perfect solution as there are other issues to deal with once you go down that route. Agree? Disagree?
Dave, I agree, but the flexibility offered by allowing users to type in keywords such as: TODAY YESTERDAY, PREVIOUS MONTH, THIS MONTH, etc. outweighted the disadvantages
This also works only if you have a fixed data format, as one needs to convert the alpahnumeric @PROMPT to a DATE in the end (as you alread pointed out). So working with a preferred viewing locale (PVL) will not work (e.g. 12/31/2008 vs. 31.12.2008, etc.).
Wanting to take advantage of the semantic richness of objects, I created objects that encapsulates concepts like yesterday or last year etc.
As an example I created a Yesterday date object (Oracle).
Object Name= Yesterday Date
Object SQL = TRUNC(sysdate) -1
This sandbox SQL showed this approach worked.
These objects are implemented in a BO XI R2 universe designer derived table. Joining this table to your existing schema can be accomplished through artificial keys or database design. We used a weekend flag on a date dimension in a star schema design.
Further, these objects have been implemented as stored procedures for performance purposes.
So, when you need to select a date compare in your where clause you would select the object “Yesterday date” for the expression
reservation date = Yesterday date
using the magic approach it would look like the expression below.
orders.ship_date = case when @prompt(’Enter shipping date’,'D’,,mono,free) = ‘1900-01-01′ then Yesterday date else @prompt(’Enter shipping date’,'D’,,mono,free) end
Hey Andreas … how’s the performance of the query with respect to date based indexes. Are you losing the advantage of indexing when you use formalas in the selection criteria (where clause)? I have found in larger data marts which have date based data that giving up performance on the query results is not worth the simplicity of converting prompt values. Mike