OBIEE Prompts - Last Year, Last Month and Custom Dates

This post (done in OBIEE 11.1.1.7.1.140114) is about creating specific type of prompt for periods: First part is about fixed periods (Last Year and Last Month) and in second part I will add Custom Dates option.

I follow Adrian Ward (@Addidici) on Twitter. He often posts links to different blogs and articles related to OBIEE. This way I found 2 things:

1. The last post of bidirect.blogspot.com OBIEE 11g – Advanced Prompts for Financial and Other Data such as ‘Last Year’, Last Month’ or ‘Last 6 Month’

2. The annoying fact: the link to BI Direct I have on the side bar was not updated for a long time, despite some interesting new posts there. 

In this post Pravin Khadakkar describes a solution for a prompt when user can select from a list of period descriptions and the relevant dates are used in the analysis.

In this specific case, I wasn't happy about the solution. Since it's a very common case, I decided to write my version.

There were few things I didn't like:

• Pravin asks why Oracle doesn't have this option prepackaged. It might be a good idea, unfortunately, there is no end to this list. Only in the last year I've seen over 30 different requests for such list (Last Year, Last month, Last 3 days, Last week, Previous Month, MTD, Yesterday, Week before last....).
• The most common request is to add "Custom Dates". 
• In most cases there are few analysis in such dashboard and it might be better to calculate all the dates outside the analysis.

The first part will be about Fixed periods list. The second will add 'Custom Dates' option.

Fixed Periods List

Create a Dashboard Prompt. In this Prompt create a Variable Prompt with custom Values

 (in this case Last Month, Last Year and Custom Date). We will use Custom Date later. The default is 'Last Month'. I named the Presentation Variable PV_Date_Group.

A second Dashboard Prompt (I named Hidden Prompt) will have 2 dates and will be hidden. (Actually for Last Year ad Month one date is enough, since the second date is Current Date, but for the general case...)

In this prompt there are 2 Variable Prompts

1. Presentation Variable P_Act_From_Date. Date Data type and Default Value:

SELECT case when '@{PV_Date_Group}{Last Month}'='Last Year' then TIMESTAMPADD(SQL_TSI_Year, -1,CURRENT_DATE) else TIMESTAMPADD(SQL_TSI_Month, -1,CURRENT_DATE) end saw_0 FROM "Sample Sales Lite"

 2. Presentation Variable P_Act_Till_Date. Date Data type and Default Value:

SELECT CURRENT_DATE saw_0 FROM "Sample Sales Lite"

I can use the two Presentation Variables in any analysis for filtering.

The test Analysis is based on Sample Sales with Date, Revenue and Billed Quantity.
Since the data is somewhat obsolete, I added 4 years to each date.
I need the analysis to be "aware" of prompt changes, so I also have the column with the formula '@{PV_Date_Group}' (it's a string, don't forget the brackets. I always do.) I'll mark hide for it in Column Properties -> Column Format.  

As I said, the filter is between the two Presentation Variables, with some default dates, so I can test the analysis.

Next task is to hide the date prompt.
I covered it in the past here. (Edit Dashboard->dashboard properties->Filters and Variables, add the hidden prompt).

(yes, there is a spelling mistake in the name).

The result is working:

Next step is adding the Custom Date option.

Period List + Custom Date

We have 2 prompts, One hidden and one selects the period from list. When user selects there 'Custom Date' I want to be able to open additional prompt (third) and select dates there.
I'll create a third prompt with two Variable Prompts based on calendar and Data type Date.
(see here about forcing Variable Prompt to be Calendar input).

I want some date default for the Prompt, I don't mind what, as long it's a date. In this case, P_From_Date default is:SELECT  TIMESTAMPADD(SQL_TSI_Month, -1,CURRENT_DATE) saw_0 FROM "Sample Sales Lite"  (last month) and P_Till_Date default is: SELECT CURRENT_DATE saw_0 FROM "Sample Sales Lite". 

I want to hide this part when it's not custom date selected. So I created the following analysis used as condition for this prompt section:

(The column formula is '@{PV_Date_Group}{Custom Date}' and the filter tests if it's equal to the string 'Custom Date'. This returns value only when 'Custom Date' is selected in the prompt).

Last part is to change the formula of the Hidden Prompt, so it returns the P_From_Date and P_Till_Date values when 'Custom Date' is selected, and previous values when not.

The new default SQL of the P_Act_From_Date is:
SELECT 
CASE '@{PV_Date_Group}{Last Month}' 
WHEN 'Last Year' THEN TIMESTAMPADD(SQL_TSI_Year, -1,CURRENT_DATE) 
WHEN 'Last Month' THEN TIMESTAMPADD(SQL_TSI_Month, -1,CURRENT_DATE) 
ELSE DATE'@{P_From_Date}' END saw_0 
FROM "Sample Sales Lite"

(Meaning: when Last Month selected Current Date minus Month, When Last Year selected Current Date minus Year, Else return the P_From_Date of Custom Date).

In Similar Fashion, P_Act_Till_Date is:
SELECT CASE '@{PV_Date_Group}{Last Month}' 
WHEN 'Custom Date' then DATE'@{P_Till_Date}' 
ELSE CURRENT_DATE END 
saw_0 FROM "Sample Sales Lite"

(Meaning: When Custom Date selected return P_Till_Date, else return Current Date).

Now, it's working both for Custom Dates:

And Set Dates:

You are not happy with the two Apply buttons? See here.

ADDENDUM (19-Feb-2014)

I implemented the above at customer site. To save the trouble with two dashboard prompts for Custom Date, We added the 2 Prompt Variables of dates to the first Dashboard Prompt, assuming the users are intelligent enough to understand the dates are relevant only if you select the "Custom Date" option. (And you might add a note about it in the dashboard).
It looks like this. Now I don't need the conditional section. All the rest is the same.

ADDENDUM 2  (16-Apr-2014)

Currently there is a limitation of passing presentation and session variables values with "Analyze" link. On the other hand, "Is Prompted" works fine. 

In that case we still used hidden prompts, but connected them to 2 real columns. For example "Greater then" on "date1" and "Less then or equal" on TIMESTAMPADD(SQL_TSI_DAY, 0,date1) with the relevant defaults. In the analysis we used is prompted on each. 

ADDENDUM 3  (19-Dec-2015)

The solution described here uses the date level in date hierarchy. It might be problematic when using aggregation awareness. I wrote another post about OBIEE - Selecting Date range from a Prompt Without breaking aggregation awareness.