Set Yesterday or Some Date Value as default in OBIEE Dashboard Prompt

Aim is to set default value to yesterday or some other day that you desired in dashboard prompt. So, when we click on the dashboard page.

This can be done in many ways

Here I am going to introduce 3 ways of doing the same.

1. Using Advanced SQL in Prompt (From Answer side)
2. Using repository variable (From rpd side)
3. Using derived column in rpd (Same using repository variable)

We discuss this one by one

Using Advanced SQL in Prompt (From Answer side):

1. Create a prompt on date column as per your requirement
2. In Default to option drop-down, select SQL Result 

3.   Now click on eclipse button provided under selected SQL Result option

4.   Write the following query in the editor & click on OK.

(Here I am explaining to set default value to yesterday,)

 SELECT CASE WHEN 1=0 THEN Times.”Time Id” else TIMESTAMPADD(SQL_TSI_DAY, -1, CURRENT_DATE) END FROM SH

In general, query will be,

SELECT CASE WHEN 1=0 THEN “any date column”  else TIMESTAMPADD(SQL_TSI_DAY, -1, CURRENT_DATE) END FROM “SubjectAreaName”

5.   Now add this prompt to dashboard page and you will be able to see the prompt is filled with the default value for yesterday

Note: If you want to set some other date as default, you can do that by simply changing the else part of SQL written.

Using repository variable (From rpd side):

1.   Open rpd and go to Manage > Variables… and select new initialization block to creating dynamic repository variable. Select new initialization block option.

2.   Click on Edit Data Source… and write the query & choose the corresponding connection pool as shown below.

Query is:

SELECT CURRENT_DATE – 1 FROM DUAL

(or)

SELECT SYSDATE – 1 FROM DUAL

3.   Click on OK to confirm the query (You can test here the query o/p by clicking on Test button)

4.   Click on Edit Data Target… option and click New… to create new repository variable

5.   Give repository variable Name, here I am using YstrDay as Variable Name and click OK

6.   Click OK and again OK to confirm creation of initialization block.

7.   Save the rpd and go to answers and reload the server metadata.

8.   Go to the prompt you have created already, and this time select Server Variable from Default to option.

9.   Click on Eclipse button under the Server Variable option and mention the repository variable name in editor. i.e. “YstrDay” in my case.