OAC - OBIEE, custom dashboard prompt

User_OZYJB

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.

Federico Venturin - Oracle

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')

User_OZYJB

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