Simplified Date Prompts

I was recently on a project that involved a lot of work on existing universes. One thing that I noticed with these universes was that they incorporated a lot of clever date prompts, inviting the user to select, or enter, a date range for the query. The List of Values for these date prompts included values such as Yesterday, PriorWeek, MTD, YTD, Prior Month, Prior Year, User Input, etc. If the user chose User Input, they would then enter the begin and end dates for the date range. On the back end, each of these selections was converted to the correct dates, and, using large CASE or DECODE statements, the required dates would be applied as a condition. It was very clever, but presented some problems.

The first problem was that, depending on which filter was used, the date calculations could be slightly different. Not all filters used all options, which is fine, but because the calculations weren’t the same from one object to another, the results could be different. Also, the options could have different names from one universe to the next. This can cause confusion among users.

The second problem was maintenance. If any options needed to be modified, or added, the universe developer would need to find every place in the universe where these date prompts appeared, and make the needed changes. There were often dozens of these prompts throughout a universe.

So, I spent a little time to develop a more elegant solution, that would provide a consistent experience for the users, as well as a much easier maintenance experience for the developers.

Step 1: Create a Derived Table

Ideally, this table would be created in the database, and updated every night through a scheduled script. However, if that isn’t possible, the table can easily be created in the universe as a Derived Table. I call the table DT_DATE_PROMPTS. The table has three columns:

  • USER_CHOICE – This is the value that the users will see in the LOV.
  • BEGIN_DATE – This is the beginning of the date range for the selected value.
  • END_DATE – This is the end of the date range for the selected value.

The table is simply a series of unions, one for each option. I have attached the complete SQL statements for the Derived Table, for both Oracle and Teradata. If you’re using a different database, you can easily modify this to suit your needs.

TD_Dates OR_Dates

If you want to test this SQL in a tool such as Teradata SQL Assistant, or TOAD, make sure that you comment out the final union, as that won’t run outside of BusinessObjects. However, you can also create the Derived Table in the universe, and use the View Table Values option to test the code as well. It will prompt you for a Begin Date and an End Date. These are for the User Input option, and whatever dates you enter will show on that row.

Feel free to add, or modify, the options in the table, as needed. Note that, when using this table, users will always be prompted for a Begin Date and an End Date, even if they aren’t selecting the User Input option. For this reason, you might want to set a default for those as 11/11/1111. The users can then ignore those prompts unless they want to use that option.

Step 2: Create Aliases and Add Joins

Next, you will create an alias of the Derived Table for each date column in the universe for which you wish to provide date prompts. Then, join the alias to the date, using a Theta Join. For example, let’s say that you want to create date prompts for the Sales Date column in the Sale table. Create an alias of the Derived Table. In this case, we’ll call the alias SALE_DATE_PROMPT. Then, join the alias to the Sale table, using a Theta Join as follows:


Step 3: Create Filter Objects

The filter objects are now pretty simple to create. You simply need to prompt for which option to choose from the alias table. For example, if we now want to prompt for a date range for the Sale Date, we create a filter object called Sale Date Range with the following code:

SALE_DATE_PROMPT.USER_CHOICE = @Prompt(‘Select Frequency’,’A’,{‘Prior Week (Mon to Sun)’, ‘Prior Month’,’User Input’}, MONO,CONSTRAINED)

Notice that, in this case, I have limited the LOV for this prompt to Prior Week, Prior Month and User Input. That’s one of the nice features of this trick. You can prompt for a limited list of values, or all values from the Derived Table. When a user uses this filter, they will be prompted for three values:

  • Begin Date
  • End Date
  • User Choice

Again, they only need to enter actual dates for the Begin Date and End Date prompts if they use the User Input option for the User Choice prompt. Otherwise, they enter dummy dates, such as 11/11/1111 for both.

There you go. You’re now ready to try it out. Drop your new filter into a query and see how it works.


One thing to keep in mind is that Theta joins always have a negative impact on performance. This impact may, or may not, be significant. Work with your DBA to tune the database for optimal performance.

You can create the same Derived Table in multiple universes, thus giving the users a consistent experience across all universes. If you ever need to modify the options available to the users, simple change the code in the Derived Table, and copy that change to all universes. All of the aliases will  pick up the same change, of course. So you only have to make one change per universe.

Simple, and elegant. Let me know what you think.

13 Responses to Simplified Date Prompts

  1. Mark Wald says:

    Great post. This reminds me of something I have seen with Microstrategy, which has a concept of “Fuzzy Date Filters”. The user picks the name of a filter such as “Last 14 weeks”, and the report is aware of the date range via a metadata look up. Ahead of time a scheduled script updates the metadata table.

  2. sri says:

    This post looks really good. We are using a lot of Prompts in Joins between tables to restrict data in Teradata environment. Our Universe is very huge and there are lot of such joins with @prompts having Begin and End Date. Very difficult to maintain them. I can look into this option for new independent universes.

  3. Krysztof von Murphy says:

    Thanks God (and lack of funding and respect for customer feedback) I did not need to add “Yesterday” or “Last month” very often. Putting all this in a single table or view makes sense, and I’ll try it in my next universe.
    Thank you!

  4. Jeff Stout says:

    Does this work in 4.0 and or 4.1?

    • I did this in 3.1, but can’t think of any reason why it wouldn’t work in 4.x.

      • Jeff Stout says:

        I originally did this in 3.1. Now that we are at 4.0 I get the prompt for begin data and end date, I enter values for each. Then I move to “Select Frequency” (it thinks for a moment) then gives the message “No values to display”. I know the logic is working as I can execute the logic in the universe for each LOV. I very much like what you created and it has been very helpful. I am beginning to think that I have stumbled into a bug. Thank you for the quick reply.

      • Jeff Stout says:

        Just a follow up. I removed the @Prompt logic from the SQL in the universe and the other values came through. Also I forgot to mention that I am using IDT and my universe is a type UNX.

  5. Paul Berden says:

    Hi, In case you’re using the Teradata code on a TD 14.0 or later version. You should replace the function day_of_year with td_day_of_year.

    Thanks for sharing!

  6. Dev says:

    When I try to create a derived table using the codes given by you, it throws an error saying “ORA-01790: Expression must have same datatype”. I am using Oracle. I tried to change the data type of last expression from ‘D’ to ‘C’, but still giving the same error..

  7. kiran says:

    Getting “Exception:DBD, ORA-01790:expression must have same datatype as corresponding expression State: N/A” error while creating the derived table in universe with the given ORACLE DB Scripts.Could you please check & advice.

  8. Erdem says:

    Thank you very much!

  9. Originall says:

    The last union should contain to_date operator, as BO read prompt as string bu default :

    ‘User Input’,
    to_date(@Prompt(‘Enter Begin Date’,’D’,,mono,free),’dd.MM.yyyy’),
    to_date(@Prompt(‘Enter End Date’,’D’,,mono,free) +1,’dd.MM.yyyy’)

    Then, all works good.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

<span>%d</span> bloggers like this: