Insight (OBIEE) Tips | How to create a prompt and add a default date range

Otilia Antipa-Oracle

For more Eloqua Insight Resources, please visit: https://community.oracle.com/topliners/discussion/4470627/eloqua-insight-resource-page

Prompts are a great way to provide your users the flexibility of pulling reports that are unique to them. They also help with performance for large robust reports.

In this post we’re going to focus on how to create a prompt and add a default date range with SQL.

What is a column prompt?

A column prompt enables users to select specific values to populate a column in a dashboard or analysis; it is the selection of data you make prior to running a report.

You’ll come across prompts in out-of-the-box  reports or you can also create your own in custom reports.

Here is an example of a prompt on an out-of-the-box report:

Part 1 - How to create a prompt:

1. Within the Analysis editor, select the third tab “Prompts”

2. Select New > select the column you would like to build a prompt for (for the purpose of demonstrating a default date range we are going to select a Date attribute)

3. You can now have a few options to configure, ie. add a description,  create a custom label or set the field as a required input. For the next few steps we’re going to show you how to configure the prompt to show a default date range of 1 week.

Part 2 - How to create a default date range

If you want to save your users the trouble of scrolling, consider having a default date range.  This means that when they open the report, they will automatically see a 1 week date range populated.

You can also customize the syntax for various other date ranges, we'll discuss that in more detail below.

4. Select operator “is between”

5. Select Choice list

6. Under default selections choose ‘SQL Results’

Now we get into SQL ..

7. In this example we want to set a default prompt that automatically displays last week as the prompt date range for Contact Created Date/Time.

In the first default selection box enter: SELECT TIMESTAMPADD(SQL_TSI_WEEK,-1,CURRENT_DATE) FROM "COLUMN  NAME"

Syntax breakdown:

SQL_TSI_WEEK is a time interval and can be configured for other specified intervals such as :

• SQL_TSI_DAY
• SQL_TSI_WEEK
• SQL_TSI_MONTH
• SQL_TSI_QUARTER
• SQL_TSI_YEAR

"COLUMN NAME"   will be the first part of your column name ie. if you are using the attribute  “Contact"."Contact Created Date/Time" enter “Contact”

In the second default selection box enter: SELECT TIMESTAMPADD(SQL_TSI_WEEK,0,CURRENT_DATE) FROM "COLUMN  NAME"

The syntax here is similar to the previous step, however you need to ensure that your time interval is 0 in order to pull in today's date.

Your prompt configuration should look like this:

Final result:

Hope this helps and you give this a try, leave any questions or comments below, thanks!

Otilia

User_NMJA2

Thank you for the article!

I am attempting to default the time portion of the timestamp as well as the date. I am creating a report for a user who wants to view a day's worth of data, but the starting time is 07:00:00 AM. How can I change the default time from 12:00:00 to something else? The user will be accessing the report at different times a day.

Thanks!