Jul 21 2008

Using a “Magic Date” Value in Prompts

Categories: General,Prompts,Universe Design Dave Rathbun @ 7:21 am

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)

93 Responses to “Using a “Magic Date” Value in Prompts”

  1. Comment by Dave Rathbun

    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.

  2. Comment by antony

    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

  3. Comment by Dave Rathbun

    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.

  4. Comment by pathu

    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”

  5. Comment by Ani

    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.

  6. Comment by Zaif

    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

  7. Comment by Dennis

    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

  8. Comment by Ram

    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

  9. Comment by Sanjit

    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

  10. Comment by Lloyd

    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.

  11. Comment by Dave Rathbun

    Hi, did you post the same question on BOB? I saw a number of topics with very similar questions recently. Such as this one.

  12. Comment by Pawan Lodwal

    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

  13. Comment by ZicherCZ

    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 :).

  14. Comment by Dave Rathbun

    Glad to have helped. 😎

  15. Comment by stalin

    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.

  16. Comment by Dave Rathbun

    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.

  17. Comment by Hemad

    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

  18. Comment by Dave Rathbun

    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.

  19. Comment by Prakhar T

    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

  20. Comment by Aktivo

    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.

  21. Comment by Viswa

    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

  22. Comment by Dave Rathbun

    You can’t use IN in this case because it has to be a singular value only. This is because of the case statement.

  23. Comment by Dedipya

    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?

  24. Comment by Dave Rathbun

    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.

  25. Comment by basu

    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

  26. Comment by 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..

  27. Comment by Sravan

    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

  28. Comment by Dave Rathbun

    You might want to look at the series of “Time Slice” posts to see how you can accomplish this.

  29. Comment by yoav

    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

  30. Comment by Josephtys

    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

  31. Comment by Dave Rathbun

    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.

  32. Comment by VanithaSamuel

    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

  33. Comment by Dave Rathbun

    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.

  34. Comment by kumar

    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

  35. Comment by Shoaib

    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

  36. Comment by Dave Rathbun

    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.

  37. Comment by eyanina

    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?

  38. Comment by Greg

    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.

  39. Comment by Bala

    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

  40. Comment by Olga

    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!

  41. Comment by DropX

    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?

  42. Comment by Renu

    SAP BI 4.2
    Prompt- need to enter the date. But the time should be constant

  43. Comment by Dave Rathbun

    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).