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)

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

  1. Comment by Dan Flanery

    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?

  2. Comment by Mike McErlain

    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!

  3. Comment by Dave Rathbun

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

  4. Comment by Andreas

    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.

  5. Comment by Dave Rathbun

    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?

  6. Comment by Andreas

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

  7. Comment by William Miller

    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

  8. Comment by Mike McErlain

    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

  9. Comment by Craig Regester

    Forgive my newness to this, but what you described in your post is exactly what I’m trying to achieve – the ability to have dynamic date prompts so that I can use them both in the InfoView Scheduler and DeskI app. What I do not know is how to implement this. Your code makes sense but I’m not sure where to create these prompt objects in Designer or where ever. Is it possible to provide some screenshots of where you inserted your code? I want to be able to use this magic date for numerous objects, not just one date object (Post Date, Create Date, etc).

    Kind of getting shoved into this because our vendor who supplied us with Business Objects just forced us to upgrade from 5.1.7 to BO XI R2 SP3. They had a custom scheduler they wrote for 5.1.7 that handled things like TODAY-1, MONTHLAST, etc but they have no idea how to do any of this in BO XI even though they were the ones forcing the upgrade. :)

  10. Comment by Dave Rathbun

    Hi, Craig, welcome and thank you for your comment. I can’t give you a screenshot right now as I am at a client site. But the logic is exactly as shown in the post. If you take this code:

    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

    … and paste it into a predefined condition in Designer, it should work. Of course you will change orders.ship_date to the table and column that you’re using. Then you include that condition object on your report. If you enter 1/1/1900 as the input date, it will be replaced by todays date. This syntax is for Teradata, for Oracle you would use sysdate instead.

  11. Comment by Craig Regester

    Thanks Dave – using your example and another I found with Oracle code, I was able to get it working with a singular field. My hope was for a bit more than that though – is it not possible to create such a filter/condition that can be used for multiple fields? In your example and what I created, we are tied to one field (orders.ship_date for instance) but the universe I am reporting off of has hundreds of date fields (its a large billing system). I guess what I need is for this to become a custom operand, if you will. From what I’ve seen thus far however, this appears to not be possible outside of using a SDK. Your thoughts?

  12. Comment by Craig Regester

    Hold that thought, I think I got it! In Designer, I created a new Class named ‘Prompt Objects’ (obviously this is just for eye-pleasing needs). Then I created an object inside of that class called ‘Date Prompt’ and the SELECT field was set to:

    CASE WHEN @Prompt(’Enter the date’,'A’,{’01/01/1900 12:00:00 AM’},mono,free) = ‘01/01/1900 12:00:00 AM’ THEN trunc(sysdate) – 1 ELSE to_date(@Prompt(’Enter the date’,'A’,,mono,free),’MM/DD/YYYY’) END

    Even though it doesn’t Parse according to BOXI, I was able to use this Object in my report as desired – as an operand – by using ‘Select an Object’. I pieced this together by pulling stuff from your blog and a few forumtopics.com/busobj posts (where I see you also post).

  13. Comment by Dave Rathbun

    Yes, Craig, that’s exactly the approach. In fact I provided it here:

    http://www.dagira.com/2007/08/22/dynamic-dates-part-i-yesterday-and-today/

    Here’s one of the images reproduced from that post:

    But it sounds like you rediscovered the technique on your own. :)

  14. Comment by Craig Regester

    Excellent, yes – if only I found that first :P I just wasn’t very good at explaining what I needed. If interested, I expanded on it a bit (similar to what Andreas did above). Here is the code for you or whomever else is interested (oracle version) – with credit to you for the month1st/monthlast calculations :)


    CASE @Prompt('1.) Enter Date Value or Special Code','A',{'TODAY','TODAY-1','TODAY-3','TODAY-7','TODAY-14','TODAY-21','TODAY-28','TODAY-30','TODAY-60','TODAY-90','MONTH1ST','MONTH1ST-1','MONTHLAST','MONTHLAST-1'},mono,free)
    WHEN '01/01/1900 12:00:00 AM' THEN trunc(sysdate)
    WHEN 'TODAY' THEN trunc(sysdate)
    WHEN 'TODAY-1' THEN trunc(sysdate)-1
    WHEN 'TODAY-3' THEN trunc(sysdate)-3
    WHEN 'TODAY-7' THEN trunc(sysdate)-7
    WHEN 'TODAY-14' THEN trunc(sysdate)-14
    WHEN 'TODAY-21' THEN trunc(sysdate)-21
    WHEN 'TODAY-28' THEN trunc(sysdate)-28
    WHEN 'TODAY-30' THEN trunc(sysdate)-30
    WHEN 'TODAY-60' THEN trunc(sysdate)-60
    WHEN 'TODAY-90' THEN trunc(sysdate)-90
    WHEN 'MONTH1ST' THEN trunc(sysdate,'MM')
    WHEN 'MONTH1ST-1' THEN add_months(trunc(sysdate,'MM'), -1)
    WHEN 'MONTHLAST' THEN last_day(trunc(sysdate))
    WHEN 'MONTHLAST-1' THEN last_day(add_months(trunc(sysdate,'MM'), -1))
    ELSE to_date(@Prompt('1.) Enter Date Value or Special Code','A',,mono,free),'MM/DD/YYYY') END

  15. Comment by Rem-8

    Hi Craig. I tried to copy/paste your code into some.table BETWEEN “date” AND “date” clause where your code is put instead of those “date” bits but report isn’t created, it doesn’t even show prompts of those.


    WI_NODES_BUFFER.TA_PERIOD BETWEEN CASE @Prompt('1.) Enter Date Value or Special Code','A',{'TODAY','TODAY-1','TODAY-3','TODAY-7','TODAY-14','TODAY-21','TODAY-28','TODAY-30','TODAY-60','TODAY-90','MONTH1ST','MONTH1ST-1','MONTHLAST','MONTHLAST-1'},mono,free)
    WHEN '01/01/1900 12:00:00 AM' THEN trunc(sysdate)
    WHEN 'TODAY' THEN trunc(sysdate)
    WHEN 'TODAY-1' THEN trunc(sysdate)-1
    WHEN 'TODAY-3' THEN trunc(sysdate)-3
    WHEN 'TODAY-7' THEN trunc(sysdate)-7
    WHEN 'TODAY-14' THEN trunc(sysdate)-14
    WHEN 'TODAY-21' THEN trunc(sysdate)-21
    WHEN 'TODAY-28' THEN trunc(sysdate)-28
    WHEN 'TODAY-30' THEN trunc(sysdate)-30
    WHEN 'TODAY-60' THEN trunc(sysdate)-60
    WHEN 'TODAY-90' THEN trunc(sysdate)-90
    WHEN 'MONTH1ST' THEN trunc(sysdate,'MM')
    WHEN 'MONTH1ST-1' THEN add_months(trunc(sysdate,'MM'), -1)
    WHEN 'MONTHLAST' THEN last_day(trunc(sysdate))
    WHEN 'MONTHLAST-1' THEN last_day(add_months(trunc(sysdate,'MM'), -1))
    ELSE to_date(@Prompt('1.) Enter Date Value or Special Code','A',,mono,free),'MM/DD/YYYY') END AND CASE @Prompt('1.) Enter Date Value or Special Code','A',{'TODAY','TODAY-1','TODAY-3','TODAY-7','TODAY-14','TODAY-21','TODAY-28','TODAY-30','TODAY-60','TODAY-90','MONTH1ST','MONTH1ST-1','MONTHLAST','MONTHLAST-1'},mono,free)
    WHEN '01/01/1900 12:00:00 AM' THEN trunc(sysdate)
    WHEN 'TODAY' THEN trunc(sysdate)
    WHEN 'TODAY-1' THEN trunc(sysdate)-1
    WHEN 'TODAY-3' THEN trunc(sysdate)-3
    WHEN 'TODAY-7' THEN trunc(sysdate)-7
    WHEN 'TODAY-14' THEN trunc(sysdate)-14
    WHEN 'TODAY-21' THEN trunc(sysdate)-21
    WHEN 'TODAY-28' THEN trunc(sysdate)-28
    WHEN 'TODAY-30' THEN trunc(sysdate)-30
    WHEN 'TODAY-60' THEN trunc(sysdate)-60
    WHEN 'TODAY-90' THEN trunc(sysdate)-90
    WHEN 'MONTH1ST' THEN trunc(sysdate,'MM')
    WHEN 'MONTH1ST-1' THEN add_months(trunc(sysdate,'MM'), -1)
    WHEN 'MONTHLAST' THEN last_day(trunc(sysdate))
    WHEN 'MONTHLAST-1' THEN last_day(add_months(trunc(sysdate,'MM'), -1))
    ELSE to_date(@Prompt('1.) Enter Date Value or Special Code','A',,mono,free),'MM/DD/YYYY') END

    Is this a proper solution? I’m a BO beginner.

  16. Comment by Charlene

    Thanks for the thought. My customers are key stroked and date conscience so I needed something that would satisfy both so I implemented the following is a SQl Server enviroment:
    ” Case File Date >=
    (Case when @prompt(’Find cases filed yesterday(Y) or filed after date(mm/dd/yyyy)’,'C’,,mono,free) = ‘Y’ then (GetDate() – 2)
    else (@prompt(’Find cases filed yesterday(Y) or filed after date(mm/dd/yyyy)’,'C’,,mono,free))
    end) ”

    I had to use -2 for yesterday due to the load of our data and the time being include to get information for yesterday.

  17. Comment by Priya

    We had a requirement to display default date in prompts. To meet the requirement we displayed the sysdate in a List of Value on the left hand side which was selected into a date cell on the right hand side. If sysdate is not required user can select a date from the calendar picker present on the right side. Note that the LOV has just vallue in it – today’s date.

    Cheers

  18. Comment by Dave Rathbun

    Hi, Priya, and thank you for your comment. If I am reading and understanding it correctly, what you did was provide the current date as the only value from the LOV, making it easier for folks to pick it. Is that correct? That’s an interesting (and valid) approach, but it doesn’t completely solve the issue of automatically providing “today” as the default value for a prompt. The user still has to pick the value, and it won’t change from day to day so they’ll have to pick it again.

  19. Comment by Shiva

    Hi Dave,

    Thanks for the post. I’ve some questions:

    My understanding says..When we create a report which has prompt in it and each time we refresh this report it asks us for a value as a response to the prompt we created.

    Now as per this above post I understand that we need not respond to the prompt daily rather it’ll automatically take today’s value for the prompt in order to refresh the report. Then in that case why at all we need this prompt at all? Why dont we use an object which uses a where clause with statement xyz_date = sysdate().

    Secondly How is it taking the current date value.. As your object definition (in select clause) says:

    case when @prompt(’Enter shipping date’,'D’,,mono,free) = ‘1900-01-01′ then DATE else @prompt(’Enter shipping date’,'D’,,mono,free) end

    Which means i fill in a date “#@$#$%” and if this date is equal to ‘1900-01-01′ then report picks up the DATE(or sysdate or currentdate whatever you say) else what i entered.

    I fill a date which is neither current date nor ‘1900-01-01′ then in that case it will not pick up currentdate or sysdate or DATErather it’ll take a date which i’ve entered. So how this report is getting refreshed with today’s Date?

    Thirdly If i schedule this report either with a new value or with an previous value

    Previous value: I can use the previous value by making use of the persistent option in @prompt() or by providing the value for prompt while scheduling the report and get my report scheduled without any issues… but in that case again my report is getting refreshed by the value which i entered last time and This value is not similar to today’s date.

    And if i dont use persistent then the report uses the above formula
    case when @prompt(’Enter shipping date’,'D’,,mono,free) = ‘1900-01-01′ then DATE else @prompt(’Enter shipping date’,'D’,,mono,free) end
    which takes today’s value only if i enter the current date or ‘1900-01-01′.
    So where exactly it is accepting the value dynamically.

    What will happen if i use the following:

    case when 1 = 2 then @prompt(’Enter shipping date’,'D’,,mono,free) else DATE end

    In this case whatever I enter it’ll take only current date.. whether its a current date or a future date or past date… etc…I know here i’m loosing the validation part as you’ve replied to Andreas… whatever may be the case i’m using a valid date field and currentdate is getting used for my prompt.

    and what if your record is having a valid value ‘1900-01-01′?

    Request you to kindly clarify.

    Thanks a bunch.

    Regards,
    Shiva

    P.S: I’m too cool to be bothered while asking questions… nomatter how silly they are!

  20. Comment by Dave Rathbun

    Now as per this above post I understand that we need not respond to the prompt daily rather it’ll automatically take today’s value for the prompt in order to refresh the report

    No, that’s not correct. It does not take the value for “today” it converts the magic date 1/1/1900 into today’s date using case logic. It’s subtle, but it is a difference.

    Then you asked:

    I fill a date which is neither current date nor ‘1900-01-01? then in that case it will not pick up currentdate or sysdate or DATErather it’ll take a date which i’ve entered. So how this report is getting refreshed with today’s Date?

    It’s not getting refreshed with today’s date, it’s getting refreshed with the date you requested. Which is what should happen.

    You also asked:

    and what if your record is having a valid value ‘1900-01-01??

    … which is covered in the post itself, where I said:

    is the first important point about this concept: I must pick a date that will never appear as part of my normal data.

    The point of the magic date is to allow reports to have a prompt, and therefore be more flexible when used interactively, but the same report can also be scheduled and the date prompt will be filled with the “magic date” which will then be replaced by the current date at the time the report is actually refreshed. To summarize its use:

    1. A user enters any desired date interactively: report runs for that date.
    2. A user enters any desired date during a schedule: report runs for that specific date for every iteration of the schedule, which is not the most likely choice.
    3. A user enters the “magic date” interactively: report runs for the current date for that instance.
    4. A user enters the “magic date” during a schedule: report runs for the current date for every iteration of the schedule. This is the entire reason for this technique, to be honest.

  21. Comment by Shiva

    oh Gotcha….

    you mean you need to enter the 1900-01-01 in your prompt… to let the magic work… so in that case it’ll take the current date..i was trying to understand with a perspective that .. if i eneter any date it’ll take today’s date to refresh….

    Thanks for taking out your valuable time to respond to this.

    by the way does this logic :

    case when 1 = 2 then [any_date] else DATE end

    allows me to do the same?? and what are your observations on this?

    Regards,
    Shiva

  22. Comment by Dave Rathbun

    You have already answered that question in your earlier comment.

    In this case whatever I enter it’ll take only current date

    Does that meeting the requirements? :) In my scenario, no. In any scenario what you posted is not useful as the 1 = 2 will never be true and therefore it will only ever return the current date from the case statement. You might as well reference the current date directly and save all the complications.

  23. Comment by Shiva

    Thanks Dave. :D

  24. Comment by Meena

    Hi Dave,
    Thanks for the informative post.
    I have a report with prompts for the start and end of month. The requiremnts are
    a. Prompt for a data range, where the user can enter start and end dates of choice

    b.Schedule a daily refresh. The beginning of each month it should take the start and end of that month

    I thought the Magic date was a perfect soultion for the adhoc and scheduled refresh requirements. So I created the following condition in the universe.

    @Select(SFDC Data\Close Date) between
    case when @Prompt(’Enter Begin Date (mm/dd/yyyy): ‘,’D',,mono,free,persistent) =’01/01/1900′ then @Select(Custom Objects-SFDC\Current Month-First Day) else @Prompt(’Enter Begin Date (mm/dd/yyyy): ‘,’D',,mono,free,persistent) end
    and
    case when @Prompt(’Enter End Date (mm/dd/yyyy): ‘,’D',,mono,free,persistent) =’01/31/1900′ then @Select(Custom Objects-SFDC\Current Month-Last Day) else @Prompt(’Enter End Date (mm/dd/yyyy): ‘,’D',,mono,free,persistent) end

    The parse is a suucess but the magic date does not work. What I want is to pick up the 1st and last day of the month if the user enters ‘01/01/1900′ and ‘01/31/1900′ but no data is fetched. There is data for the current month. I’ve checked to ensure Current Month-First Day and Current Month-Last Day objects are working correctly
    Any thoughts on what I maybe missing
    Thaks in advance
    Meena

  25. Comment by Beige

    I am working on XI 3.1, Database is Oracle
    I tried most of approach, but not working
    Using Dave’s magic date, create a condition object in Universe
    orders.ship_date = case when @prompt(’Enter shipping date’,'D’,,mono,free) = ‘1900-01-01′ then trunc(sysdate) else @prompt(’Enter shipping date’,'D’,,mono,free) end
    but got “Inconsistent datatypes: expected DATE got CHAR”
    Tried Craig approach:
    create a Date Prompt object, the SELECT field was set to:
    CASE WHEN @Prompt(’Enter the date’,’A’,{’01/01/1900 12:00:00 AM’},mono,free) = ‘01/01/1900 12:00:00 AM’ THEN trunc(sysdate) – 1 ELSE to_date(@Prompt(’Enter the date’,’A’,,mono,free),’MM/DD/YYYY’) END
    Got “Start Date contain multiple attributes. This syntax is no longer supported in this version”
    Any good sample in XI 3.1

    Thanks a lot

  26. Comment by Dave Rathbun

    Hi, Beige, and thank you for your comment.

    ‘1900-01-01′ was the syntax we used for Teradata. You will need to find out what date syntax to use for your Oracle database. It could be ‘01-JAN-1900′ or ‘01/01/1900′ or something like that. Check the NLS_DATE_FORMAT setting for your session.

  27. Comment by Barbara

    I’m having a hard time understanding how this works. I’m using a SQL Server database. I created an object like this in my universe:

    case when @prompt(’Enter Meeting Date (Start):’,'D’,,Mono,Free,Persistent,{’01/01/1900 00:0:0′},User:0) = ‘01/01/1900 00:0:0′ then ‘01/01/2009′ end

    As required, this shows the date as 1/1/2009, but it shows every single record, not selecting based on date. Then I created this a filter:

    case when @prompt(’Enter Meeting Date (Start):’,'D’,,Mono,Free,Persistent,,User:0) = ‘01/01/1900 00:0:0′ then ‘01/01/2009′ end

    I’m getting incorrect syntax near ‘)’. How exactly does this work, and can you really get the prompt to default to anything but a hard-coded date?

  28. Comment by Barbara

    Ok, I think I got it by changing the filter to:

    dbo.meeting.from_date >= (case when @prompt(’Enter Meeting Date (Start):’,'D’,,Mono,Free,Persistent,{’01/01/1900′},User:0) = ‘01/01/1900 00:0:0′ then ‘01/01/2009′ else @prompt(’Enter Meeting Date (Start):’,'D’,,Mono,Free,Persistent,,User:0) end)

    But now it’s showing the weird 1900 dates in my prompts. Is there any way to not show the strange dates in the prompts?

  29. Comment by Joe

    Hi Dave, I have a different problem to work on. I cannot do any changes to my universe . I can create any no of report variables required. The requirement is that the report needs to be scheduled to run weekly ( say Monday) , the data in the report should be split weekly for the past 8 weeks. Can you suggest anything on this? we are on XI R2 with oracle db. Thanks,Joe

  30. Comment by twofivepie

    Dave,
    Lots of very useful stuff here and like others, I have sort of discovered some of these techniques in parallel. However, I have one (what I am sure will seem very naive) question? What is the benefit of the user entering a magic date, if they want todays date? Surely they can just enter the date??

  31. Comment by Dave Rathbun

    You’re correct, if the user runs the report they can enter any date that they want. The “magic” of this technique is primarily appropriate for scheduled reports. When you schedule a report you have to answer any / all prompts. If you prompt for a date, and you enter a specific date, the report always runs for that date. This way the report date ranges will be dynamic, even with a static parameter value.

  32. Comment by twofivepie

    OK, I have been playing with this all day and I have seen a way that gets very close to having a default. In my case I wanted to have a date used in a report that is normally 1 month ago, but as its timed to a workday, in may not always be. So I have created an Class\Object called Dates\Month Ago. It contains getdate – 31 (essentially…) I then place this in the list of values parameter in the @prompt ie @prompt(’date of last meeting’,'D’,'Dates\Month Ago’,mono,free,not_persistent) When the report runs the user gets a prompt which has the Month Ago date on the left and an empty box on the right. If the user clicks the right arrow they get the default, if the select the date picker from the right, they can select a different date.

  33. Comment by twofivepie

    now just read your reply to me – I agree that my solution just above is for interactive reports.

  34. Comment by Lilly

    Hi, Dave:

    I tried a few time to get this work, but couldn’t. The steps are:
    1). Create prompt in the universe – :
    DATE_CNTL.DT2_PRCS = CASE WHEN @Prompt(’Please Select Business Date:’,'D’,,mono,free) =’1900-01-01′ THEN DATE ELSE @Prompt(’Please Select Business Date:’,'D’,,mono,free) END

    2). Bring in this prompt into Webi report. When I ran the report, I get only “No Data” either by entering 1/1/1900 or andy valid date.

    I wonder why this happens. My purpose is: Current Date is the default value for the prompt. Whenever users click the report, the first value they see for this date column is Today’s date. However, users want to be able to select from the list. Will that work using the Magic Date?

    Thanks a lot.

    Lilly

  35. Comment by Dave Rathbun

    Hi, Lilly, thanks for your question. The first item to consider: are you using Teradata? The functions and date formats could be different on different databases. Second, the prompt as defined does not offer a list of values, but users should still be able to select from the calendar widget.

  36. Comment by Sanjoy

    Hi Dave,

    I am new to this blog,Is this code also valid for SAP BW Database?

  37. Comment by Lilly

    Hi, Dave:

    We use Teradata. I created the prompt using your code. When I entered 1/1/1900, yesterday’s data was not returned. Is it true that I can expect to see the default value will be Today’s Date after I save the report and run it again?

    Thank you very much.

    Lilly

  38. Comment by Dave Rathbun

    No, the default date will not be “yesterday” with this technique. The way the condition gets interpreted the 1/1/1900 is converted to yesterday (or whatever date is derived from your logic) but it doesn’t change the entered prompt value. The code as provided returns “Today” and not “Yesterday” anyway. The Teradata constant DATE returns the current system date.

    Make sure you are comparing to a date column and not a datetime column. You might try this:

    cast(DATE_CNTL.DT2_PRCS as date) = CASE WHEN @Prompt(’Please Select Business Date:’,'D’,,mono,free) =’1900-01-01' THEN DATE ELSE @Prompt(’Please Select Business Date:’,'D’,,mono,free) END

  39. Comment by Chaitanya

    Hi Dave..

    This is a interesting Blog…i hope some of ur expertise might help me with a business requirement. We have got a cascading prompt for Territory/Region and Division and these are assigned to diferent level of users. So, when the prompt displays in the Webi Report the value that displays in the prompt should be defaulted to whatever value is assigned to the user (let it be Terr/Division/Region).

    The user security is all handled at the DB(Oracle). Can you help me how to default the values in the prompt for this scenario?

  40. Comment by Dave Rathbun

    Hi, Chaitanya. It sounds like in your requirement you are trying to provide a different default value based on a user profile. As discussed in this post, we don’t have any sort of scripting capability in prompts at this time. It would probably be best to leave the prompt blank (no default) and create a List of Values (LOV) query that invokes the security profile to provide a list of choices appropriate to the user. Mark it to “Refresh on use” so it gets invoked every time.

  41. Comment by Ben

    Hi there, you’ve got a great site and has been very helpful. I’ve got an issue that I’ve come across and can’t seem to get a clear answer. The database is MS SQL Server 2005. The database guys are giving me 3 fields for date (coming in as numeric values). I’m trying to combine them into a date format in 1 dimension. I get year 2010, month = 6 and day = 12 in 3 fields. I’ve tried bringing them in with “char()” function in three separate objects, but all I get is a square. It seems that the fields coming in as numbers won’t convert to character, and don’t know exactly what to do to get this to work. When I use convert, it is adding all of them together. Attempts I’ve tried..

    convert(char((time\convertedmonth) + (time\convertedday) + (time\convertedyr)),101)

    I’ve also tried using char in each…

    select convertedyr char(table.yr) (this one gives me an output of the unrecognizable symbol of a square.

    Any suggestions?

    Sincerely,

    Ben

  42. Comment by Dave Rathbun

    Ben, the char() command returns the ASCII symbol (letter, number, special character) that matches the input value. For example, character 32 in the ASCII table is the space. So char(32) returns ‘ ‘. The reason you’re getting a “box” is because your characters are outside of the printable range.

    You probably want to look at the cast() function. I would first convert the number values to string, then concatenate them with appropriate delimiters, then cast that result as date or datetime as needed.

  43. Comment by Ben

    Dave, Thanks for all your guidance on this. What I got to finally work was…

    Cast((convert(varchar(2),(dbo.step2_Rptd_Inv.RPTD_MTH))) +’-‘ + (convert(varchar(2),(dbo.step2_Rptd_Inv.RPTD_DT))) +’-‘ + (convert(varchar(4),(dbo.step2_Rptd_Inv.RPTD_YR))) + ‘ 00:00:00’ as smalldatetime)

    Thanks again for your help.

    Ben

  44. Comment by Dave Rathbun

    Ben, you are welcome. Glad to have helped. The char() function has tripped me up more than once. :)

  45. Comment by David F

    Does anyone have the proper syntax for this for XI 3.1 with an oracle database?

  46. Comment by vl

    Dave, I wonder if this works:

    orders.ship_date = case when @prompt('Please choose a shipping date','A',{'Today':'Other Dates'},mono,constrained) = 'Today' then DATE else @prompt('Enter shipping date','D',,mono,free) end

  47. Comment by Dave Rathbun

    You would get both prompts at once. In other words, the user would see the second prompt even if they answered “Today” for the first prompt.

  48. Comment by pathu

    The thing is date was stored inthe numbeer format and i changed to date format using
    date(SUBSTR(CHAR(columnname),5,2) || ‘/’ || SUBSTR(CHAR(columnname),7,2) || ‘/’ || SUBSTR(CHAR(columnname),1,4)) and it got converted to 10/10/2010. I have an other problem…there are actually two date fields which i changed using the above…one got changed and i could able to c the dates in the display..but the other field is giving me error sql execution with invalid daterange….

    i have to use a condition on one of the date field like when we choose the date it should give all the date -91days dates from the date we selected for that i am trying to put a condition which is not working ..i tried in the same way as u told…
    date(SUBSTR(CHAR(columnname),5,2) || ‘/’ || SUBSTR(CHAR(columnname),7,2) || ‘/’ || SUBSTR(CHAR(columnname),1,4)) between
    @prompt(’Enter value(s) for filed’,'D’,fieldname,mono,Free,Persistent,,User:0) – 91 days
    and
    @prompt(’Enter value(s) for filed’,'D’,fieldname,mono,Free,Persistent,,User:0)…its not working ….its giving me invalid defintion error…………….

  49. Comment by Dave Rathbun

    User:0 is not valid for a universe-designed prompt. You will either need to take it out, or save the universe with the parse error and test the prompt on a regular report to see if Web Intelligence will work with the syntax. You have also not specified which database you are using, so I can’t say if the syntax - 91 days is valid. It works for some but not all databases.

    When I am presented with a challenge like this I break everything down in steps. First make sure that your conversion from the numeric format is working. Then build a simple condition on that date and make sure it works. From that point you can try to implement the Magic Date functionality outlined in this post.

  50. Comment by pathu

    I am using db2 databse with bo r2…the date conversion is working fine and also the prompt condition on the date …if i use @select(tablename\columnname)BETWEEN current date -91 DAYS AND current date…this is working finee…but when iam trying to use teh same using prompt…@select(tablename\columnname)BETWEEN @prompt(’enter the date’,'D’,'tablename\columnname’,mono,free)-91 DAYS AND @prompt(’enter the date’,'D’,'tablename\columnname’,mono,free) its not working..

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

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

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

  54. 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″

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

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

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

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

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

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

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

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

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

  64. Comment by Dave Rathbun

    Glad to have helped. 8-)

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

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

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

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

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

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

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

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

  73. 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?

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

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

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

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

  78. Comment by Dave Rathbun

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

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

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

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

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

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

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

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

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

  87. 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?

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

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

Leave a Reply

If you want to include formulas or code in your comment, please read my Tips for formatting comments first. Tags you can use are listed below.

XHTML: You can use these tags: <a href="" title=""> <acronym title=""> <blockquote cite=""> <code> <em> <strike> <strong> <sup> <sub> <u>

Confirm submission by clicking only the marked checkbox:

                 [

Please remember that comments that are not related to this blog post may be ignored or deleted without notice. If you're looking for help on a topic you have already posted on BOB then please do not repost your question here.