OAC - OBIEE, custom dashboard prompt

Question

I am trying to create a custom SQL dashboard prompt in OAC-OBIEE.

The first SQL fetches Year as a value and sorts the dataset descending.  Works just fine.

The default SQL fetches the same but I added a WHERE clause filtering to current year.

e.g.

SELECT "DATASET NAME"."Target Year"

FROM XSA('MyLogin@somewhere.com'.'DATASET NAME')

WHERE "DATASET NAME"."Target Year" = YEAR(current_date)

This does not error however, when executing, the prompt has all Years select and the expected default selection is 2023 not all the years selected.

User_OZYJB · Answer

Perfect -thank you.  Worked as expected.  For whatever reason I was thinking the way I wrote it worked before.  Before being 5 years ago on OBI 12 prem.

Thanks again Federico.

Kris

Federico Venturin - Oracle · Answer

Hi @User_OZYJB ,

Replace the default SQL as follows and it should work as expected:

select DISTINCT YEAR(CURRENT_DATE) from XSA('MyLogin@somewhere.com'.'DATASET NAME')