Dates as Filters

Date Filters in OBIEE

Filtering dates by default in your Answers is a fairly common theme.
You also have the option to replace the IN with greater than or less than.

Yesterday:

From Your Analysis
"ReportDate" IN (TIMESTAMPADD(SQL_TSI_DAY,-1,CURRENT_DATE))



Setting the Dashboard Prompt:
SELECT TIMESTAMPADD (SQL_TSI_DAY,-1,CURRENT_DATE) FROM "ReportDate"


I'll leave out setting the dashboard prompt with the next few examples as the syntax is similar and the changes from the filter can be worked out from the example above.


First Day of the Month:
"ReportDate" IN (TIMESTAMPADD(SQL_TSI_DAY, -1*(DAYOFMONTH(CURRENT_DATE )-1) , CURRENT_DATE ))


First Day of the Year:
"ReportDate" IN (TIMESTAMPADD(SQL_TSI_DAY, -1*(DAYOFYEAR(CURRENT_DATE )-1) , CURRENT_DATE ))


First Day of the Quarter:
"ReportDate" IN (TIMESTAMPADD(SQL_TSI_DAY, -1*(DAY_OF_QUARTER(CURRENT_DATE )-1) , CURRENT_DATE ))


This is not an exhaustive list of what is possible, but should get you started.

Auto Complete Functionality in Prompts

Available now in OBIEE 11.1.1.6 is the ability for prompts to suggest and highlight matching values as the user types in a value for the prompt.

 At the moment this functionality is limited to Choice lists only, and even then only when you allow the "Enable User to Type Value" checkbox. This you'll find in the prompt properties.

So how do we turn this functionality on, as it doesn't happen out the box.

Step 1.

Edit the instanceconfig.xml file in the \instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1 directory. Between the <serverinstance></serverinstance> tags add the following. 

<prompts>
<maxdropdownvalues>256</maxdropdownvalues>
<autoapplydashboardpromptvalues>true</autoapplydashboardpromptvalues>
<autosearchpromptdialogbox>true</autosearchpromptdialogbox>
<autocompletepromptdropdowns>
<supportautocomplete>true</supportautocomplete>
<caseinsensitive>true</caseinsensitive>
<matchinglevel>MatchAll</matchinglevel>
<resultslimit>50</resultslimit>
</autocompletepromptdropdowns>
</prompts>

This will allow autocomplete and ignore case, returning a list of 50 (at most) matches. 

Step 2.

Restart the BI component either through Enterprise Manager or opmnctl. 

Step 3. 

Now create a prompt ensuring that your settings are similar to those below.

 When you come to test the prompt, enter two or more letters and the dropdown below the prompt should fill. Note that the pattern of letters you enter can occur anywhere in the entries in the list returned, even in the middle of a word.