OBIEE Dashboard prompts

Question

Hi

I have a requirement in the calendar prompt default selection. The start date should be from 01-Nov-(year should change after every year i.e., the current year it should show as 01-Nov-2018 and next year after November it should change as 01-Nov-2019) and the second default selection should be the current date. Anyone has suggestions to this ?

Regards

Vyshak P

Venkata Yaswanth Ramisetty · Answer

Hi,

Ideally November 1st is the 305th day of the year (306th in leap years) in the Gregorian calendar. So, in the RPD, create a variable 01_NOVEMBER and have the below query.

SELECT CASE WHEN TO_CHAR(LAST_DAY(TO_DATE( '01-FEB-'|| EXTRACT(YEAR FROM SYSDATE), 'DD-MON-YYYY')), 'DD') = 28

THEN TRUNC(SYSDATE,'YEAR') + 304

WHEN TO_CHAR(LAST_DAY(TO_DATE( '01-FEB-'|| EXTRACT(YEAR FROM SYSDATE), 'DD-MON-YYYY')), 'DD') = 29

THEN  TRUNC(SYSDATE,'YEAR') + 305

END AS "01_NOVEMBER" FROM DUAL;

For the second variable can be a SYSDATE.

Hope this helps.

Thanks,

Yaswanth

SonPat99 · Answer

Hello Vyshak,

You can create 2 Repository variables and assign them as default selection for the prompts.

I think that should work.

Robert Angel · Answer

Hi,

the optimal way to do this is by having a fiscal year (I assume that is what it is) hierarchy set up for your date dimension(s) and a repository variable, current_fiscal_year that you update using timed sql along with the rest of the repository variables.

You might want to see posts like this for the sub-optimal way of doing it; -

Fiscal Year Function

And you can use case logic to impose an alternative sort order on your months along the lines of; -

https://stackoverflow.com/questions/46425297/fiscal-month-from-date-in-obiee/46428022#46428022