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. 8)
Hi, I will give you the same advice here I gave on BOB: Break it down to simple parts and get them to work first. This really is not a “magic date” question so I would rather not have a duplicate discussion here.
I am using BOXIR3.1 and my database is oracle 10g i tried to create a prompt condition object with the your
magic dates dates start of last month and end of last month using the formula below
@Select(Pf Tim Rec Brm Summary\Load Time) BETWEEN decode(@Prompt(‘1A START DATE’,’D’,,mono,free),’1/1/1900′,add_months(trunc(sysdate,’MM’),-1 ),@Prompt(‘1A START DATE’,’D’,,mono,free)) AND decode(@Prompt(‘1B END DATE’,’D’,,mono,free,,),’1/1/1900′,add_months(last_day(trunc(sysdate)),-1),@Prompt(‘1B END DATE’,’D’,,mono,free))
i am able to run the report but i am not geting No dat to fetch,
can you guide me if the formula i am using is right
First step (which you probably have already done) is make sure you have proper data.
Next step is to test the condition by filling in some specific dates and make sure that works.
Next step from there is to test with the “magic date” of 1/1/1900 and see if that returns the expected values.
Where are you in this testing sequence, meaning what have you tested and where is it failing?
You can also test the prompts by creating simple objects (not condition objects) using the prompt syntax and verify the dates that are being sent to the database.
Hi Dave
I am trying to convert the date format from cmmddyyyy to mmddyyyy
date(SUBSTR(char(tablename.caldte),5,2) || ‘/’ || SUBSTR(char(tablename.caldte),7,2) || ‘/’ || SUBSTR(char(tablename.caldte),5,2) +19000000 )
its in universe Business Objects using db2
when i am trying to display the object it is giving sql error
“Exception: DBD, [IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=22023
State: 22023”
Hi Dave,
Your article is very useful. It is helping to solve many problems of scheduling easily.
But I have one problem for which I am not getting any way to resolve.
I have a prompt of Begin Date and End Date. This a Text prompt that is its type is ‘A’. I have default values of ‘Yesterday’ for both prompts.So when user select ‘Yesterday’ it will map to “sysdate -1”. But now the requirement we are given is: We should have the default value as ‘yesterday’ and also there should be a “Date picker” to allow users to select any Date.
Please help me how can I resolve this. Is there a way to combine “Date” (D) as well as “text”(A) prompt in one single prompt.
Hi Dave, very interesting post. My Database in SQL and I’m using the below code
@Select(Sales\Invoice Date)= Case When @Prompt(‘Enter shipping date’,’D’,,mono,free,,{‘1/1/1900′})=’1/1/1900’ then GetDate() else @Prompt(‘Enter shipping date’,’D’,,mono,free) end
This one is working perfectly fine, do i have to train my user’s that 1/1/1900 here refers to Today? or is there any way around?
Thanks,
Zaif
Reply to 25th Comment by Beige October 8th, 2009 at 2:27 pm
The following default current date prompt works in Oracle:
@Select(Class Name\Object Name) =
(CASE WHEN @prompt(‘Enter Date:’,’D’,{’01/01/1900′},Mono,Free,Not_Persistent,{’01/01/1900′},User:0,) = ’01-01-1900 00:00:00′ then trunc(sysdate)
ELSE
to_date(@prompt(‘Enter Date:’,’D’,{’01/01/1900′},Mono,Free,Not_Persistent,{’01/01/1900′},User:0,))
END)
Thanks,
-Dennis
I find it more user-friendly to just add another prompt:
“Choose Start Date:” (A) LOV: Today, Beginning of Month, Beginning of Year, Custom
“Custom Start Date:” (D) This is ignored unless prompt 1 is custom
Hi Dave
I have a Bex quey which has two prompts i)period and 2)week. Both has a default value for the perivious week . When i create a report through query analyser in BW i can see the default period and week.
When i create a Universe on top of this bex query and create a Webi report i can see the prompt in the report HOWEVER the prompt is not filled up with the default value as i see in the query analyser.
This is a issue for me as i can’t schedule the report without the current period and week prompt. I can’t change the Bex query either as user wants to create adhoc reports based on any week and period they want.
Is ther any way BO can show the same default value as bex ?
Regards
Sanjit
Hey Dave,
I’m looking to use an @prompt in a report, but take the value of the @prompt and use a between statement of that value and 7 days.
For example:
A user selects 2010-11-01 as the @prompt value. I then want to display data using that value and 7 days previous/or after.
Is that possible?
Thanks, great site.
Lloyd.
Hi, did you post the same question on BOB? I saw a number of topics with very similar questions recently. Such as this one.
I wanted the filter like in belo format
Today (Default)
Last week (Last 7 days)
Last 1 month
Last 3 months
Last 6 months
Last 12 months
and I got anster from your website thanks…see the below code to implements the above filter
@Select(Itrack Details\Event Date) = CASE @Prompt(‘Enter Date Value or Special Code’,’A’,{‘TODAY’,’Last week (Last 7 days)’,’Last 1 month’,’Last 3 month’,’Last 6 month’,’Last 12 month’},mono,free)
WHEN ’01/01/1900 12:00:00 AM’ THEN getdate()
WHEN ‘TODAY’ THEN getdate()
WHEN ‘Last week (Last 7 days)’ THEN dateadd(dd,-1,getdate())
WHEN ‘Last 1 month’ THEN dateadd(mm,-1,getdate())
WHEN ‘Last 3 month’ THEN dateadd(mm,-3,getdate())
WHEN ‘Last 6 month’ THEN dateadd(mm,-6,getdate())
WHEN ‘Last 12 month’ THEN dateadd(mm,-12,getdate())
ELSE ”
END
many thanks to all of you for such a nice discussion
Hi Dave,
you seem to have managed to cover two of my greatest customer trouble within an hour. I stand in awe.
I’m going to test this approach tomorrow (on XIR3) – and if it works, then come to Ostrava, Czech Republic, and I owe you a beer or two :).
Glad to have helped. 😎
Hi Dave,
I am new to Business objects and I am using BOXI2. I need today’s date needs to be selected by default. I gone through the above and i am unable to understand. I am using oracle database and X is table and y is column. I have set the prompt for column y but by defualt like this ‘Selec the Start Date: 31-Aug-2011’ needs to be seclected.
how can i achieve this.
Hi, that’s the point of this post. You cannot change the default value for a prompt via a formula, so creating a prompt that defaults to the date of today is not possible. The magic date is one possible option, and there are others presented in the comments on this post.
Hi Dave,
Thanks for nice blog…
My reqquirement is to show max value (year/quarter/month) from date field of table as the defalut value in my prompt.
Kindly let me know is that possible. Thanks
Hi, and welcome. Please see my other response above. You have to create a constant value (either a date, string, or number) and then in the logic of the prompt translate that constant into the maximum year/quarter/month as required.
Hi Dave,
Very important requirement and superb explanation by you.
I was able to achieve this earlier on oracle/db2; however, date prompts coming through BeX are behaving a little differently. (Not exactly, but close to what Sanjit explained above in his comments)
So here is my problem, I have these reports that needs to be scheduled daily (for group 1 users), weekly (for group 2 users), bi-weekly (for group 3 users). No other prompt value changes except date. I am trying to implement ‘magic date’, but it doesn’t pick the right date; it picks the last manually selected date prompt value.
Any directions or insight would be really helpful.
Cheers,
PT
How can i cut time from datetime prompt ?
When i use this:
WHERE MY_TIMESTAMP >= TRUNC( @Prompt( ‘Date:’,’D’,,mono,free ), ‘dd’ )
exception raised:
Exception: DBD, ORA-00932: inconsistent datatypes: expected DATE got NUMBER
State: N/A
I need use ‘D’ in prompt, because i want date picker in prompt.
Hi Dave
the explanation and helped me in many of my reports,
can we do with “IN” instead of “=” ? like
orders.ship_month = case when @prompt(‘Enter shipping date’,’A’,,multi,free) = ‘1900-01-01’ then DATE else @prompt(‘Enter shipping date’,’A’,,multi,free) end
is this possible,,i tried but got error
You can’t use IN in this case because it has to be a singular value only. This is because of the case statement.
Hi,
I an new to business objects. I have not understood why the case statement is needed and magic date?
Why not we take a prompt and enter the date every time?
There are two possible entry values for the “magic date” solution. Either the user can enter a specific date, in which case that value is used. Or the user can enter the “magic date” in which case the system date is substituted in the place of the entered value. The case statement is used to make this switch.
Hi Dave,
Can you please help on the following case:
1. There is date prompt in the query. If user will select Jan-2012, Report runs for 31-01-2012. If user select Feb-2012, Report runs between 01-01-2012 and 28-02-2012. Similarly, for Mar-2012, report should run for Jan till Mar and so on…
Regards,
Basu
Another Requirement,
If I select Jan – Report run for Jan
Feb – Jan & Feb
March- Jan, Feb & March – END OF Q1
Apr- Apr
May- Apr & May
June- Apr, May & June – End of Q2 & so on..
Hi Dave,
I always follow your blog for tips and tricks. 🙂
I have a question about the date prompt. I usually use the following universe prompt definition to manually enter the date from prompt.
(Class/Date) between to_date(@Prompt(‘Enter user-defined begin date (Enter N/A if using relative dates)’, ‘A’,,MONO,FREE, PERSISTENT,,User:1),’MM/DD/YYYY’) and enddate (similar prompt definition). It works fine until I wanted to use a calendar instead of manually entering the dates. The definition I use now looks like this: to_date(substr(@Prompt(‘Enter user-defined begin date (Enter N/A if using relative dates)’, ‘D’,,MONO,FREE, PERSISTENT,,User:1),1,10),’MM/DD/YYYY’). Now one of our UK clients who have the ‘Preferred Browser Locale’ as English (UK) is unable to refresh the data and I found that it is because of my definition which can accept only the date in ‘MM/DD/YYYY’ format. So my question is: Is there a way to address this issue so as the report can run in any LOCALE?
Your response is greatly appreciated.
Thanks
You might want to look at the series of “Time Slice” posts to see how you can accomplish this.
Here is a very simple way how to implement default yesterday\current month value :
http://www.thebobaba.blogspot.co.il/2013/09/default-dynamic-time-value.html
Hi Dave,
I just prompt the user to select the Successful Date (Stat) and Successful Date (End
However, I have a report requirement in which I need to schedule a report for the 1st day of the next month.
Thus, the user would like to see the below report:
1. Previous Month
2. Last 12 Month
Please guide me through this.
Regards,
Joseph
Hi, Joseph, there is a series of posts here on “time sliced measures” that might help. It’s a way to drive an entire selection of different time frames based on a single input date. There are three posts altogether.
Hi Dave,
Thanks for the blog.I have to use date propmt in my report.My requirement is I have to schedule the report for a max (snap shot) from my table or the user will give prompt(snapshot).but if I use the max function in condition it is not allowing me.Please help me on this .
CASE WHEN @Prompt(‘Please enter the reporting snapshot date dd/mm/yyyy OR *’,’D’,”,MONO,FREE)=’*’ THEN (max(SNAPSHOT_DT)) ELSE CAST(@Prompt(‘Please enter the reporting snapshot date dd/mm/yyyy OR *’,’D’,”,MONO,FREE) AS DATE FORMAT ‘DD/MM/YYYY’)END
* is used for scheduling
Create a view or derived table in your universe that selects the maximum snapshot date, and then join to that view dynamically based on whether the user enters a “*” or not. That way you eliminate the need to do the max() in the middle of your expression.
hi Dave,
is there any possibility that we create prompt that will as from date and to date, where from date will be ‘current date-365(varies from 7 to 365)’ and t date will be ‘current date’.
can you guide me on this? please
Hi Dave,
The first refernce for me while having issues is your website dave.. really helpful..
I am stuck with the format which i need to pass for the below scenario. I have coded the logic but the it did not work as intended. I created a filter object in the universe.
Prompt: ( SNAPSHOT_FACT_ASOF.CLAIM_DAILY_SNAPSHOT_DATE=TO_NUMBER(TO_CHAR(TO_DATE(@Prompt(‘Enter the As-Of Date (mm/dd/yyyy)’,’D’,,Mono,FREE),’DD-MM-YYYY HH24:MI:SS’),’YYYYMMDD’))
Filter Object definition:
( SNAPSHOT_FACT_ASOF.CLAIM_DAILY_SNAPSHOT_DATE= case when (TO_NUMBER(TO_CHAR(TO_DATE(@Prompt(‘Enter the As-Of Date (mm/dd/yyyy)’,’D’,,Mono,FREE),’DD-MM-YYYY HH24:MI:SS’),’YYYYMMDD’))=
to_number(to_char(to_date(’01/01/1900′,’mm/dd/yyyy’),’yyyymmdd’)))then
To_number(To_Char(to_date(trunc(last_day(add_months(sysdate,-1)))),’YYYYMMDD’)) end
)
)
DB Date Format: YYYYMMDD
Thanks
You can certainly create a date range using the “current date” as input. Simply use the same prompt language for both the begin and ending date values, and use the proper date functions to adjust the beginning date by 365 days.
Hi Dave, this article is awesome. However I think that for my user it would be more suitable something like a development in BusinessObjects SDK, but I don’t know where to begin with that. Where could I find an SDK example that allowed me to schedule my reports with dynamic date prompts?
I wanted a user entered dates or a default for YTD to the last completed month. I did not want 1/1/1900 there so I gave up the ‘D’ date validator and used this:
RQST_ITEM_DTL_NOTIFY_DATE.FULL_DATE
BETWEEN Case @prompt(‘Start Date mm/dd/yyyy (enter nothing for 1st of year default):’,’A’,,Mono,Free,Not_Persistent,{‘ ‘},User:0)
When ‘ ‘ Then trunc(sysdate – 30, ‘YEAR’)
Else To_Date(@prompt(‘Start Date mm/dd/yyyy (enter nothing for 1st of year default):’,’A’,,Mono,Free,Not_Persistent,{‘ ‘},User:0), ‘mm/dd/yyyy’)
End
AND Case @prompt(‘End Date mm/dd/yyyy (enter nothing for end of last month):’,’A’,,Mono,Free,Not_Persistent,{‘ ‘},User:1)
When ‘ ‘ Then Trunc(sysdate, ‘MONTH’) – 1
Else To_date(@prompt(‘End Date mm/dd/yyyy (enter nothing for end of last month):’,’A’,,Mono,Free,Not_Persistent,{‘ ‘},User:1), ‘mm/dd/yyyy’)
End
The sysdate – 30 for the start date will make it run the last full year in the month of January.
I had date object as character and gives the output as YYYY.MM.DD and the user requirement is when ever he refreshes the report it should prompt for from and To.
From prompt should be on this logic (Current Date-1 year) and To defaulted to (Getdate()-1) and user can able to enter values of his own.
So I had created 2 variables.
X=convert(varchar,DATEADD(YEAR,-1, GETDATE()) ,102)
Y=convert(varchar,DATEADD(DD,-1,GETDATE()),102)
and careated 2 more varaibles to get the prompt(A,B)
A=@Prompt(‘2.From Processing Date:’,’C’,’@Select(ClassName/X) ‘,mono,free,not_persistent,{‘convert(varchar,DATEADD(YEAR,-1, GETDATE()) ,102)’})
B=@Prompt(‘3.To Processing Date:’,’C’,’@Select(ClassName/Y)’,mono,free,not_persistent,{‘convert(varchar,DATEADD(DD,-1,GETDATE()),102)’})
and in report filter i had given as Date between A and B.
But here the Default values are not coming expect the prompted text.
Hopes clarifies,can any one help me.
DB is SQL server and using Deski 3.1
Hi Dave and other Experts,
Please advise me how to set the parameter default values to ALL (to retrieve all regions in my report when the user click on ALL) or just please point me to a link I can read. I found some posts and tried creating but it did not work.
I am using BO 4.2 and trying to create a Region LOV and Region Parameter in IDT.
Thank you very much in advance!
looking into a query in WebIntelligence, after running, the prompts are replaced by values provided by user. When I run the same query on Oracle (because this database I use for my universe) I’m getting error in terms of dates. Dates in query (in BO) are just strings, like StartDate = ’30-06-2020 00:00:00′. When I run the query generated in WebIntelligence on Oracle I’m getting error:
ORA-01843: not a valid month
01843. 00000 – ” not a valid month”
And to fix this I need to use for instance to_date function and then it’s working fine. My question is: how dates are parsed in WebIntelligence while running a query? so the mentioned error does not occur?
SAP BI 4.2
Prompt- need to enter the date. But the time should be constant
The way I would normally do that is allow a date prompt and concatenate the desired time then convert the entire thing to a timestamp (or date/time depending on what your database platform requires).