Dashboard Prompt Default to LAST_DAY(sysdate)

Ray Kelly

Is there anyway to default a dashboard prompt to the last day of the current month?

I tried using SQL RESULTS as "select last_day(sysdate) from dual" but this does not work.  The prompt defaults to "All Column Values".

The value needs to be a date in the format MM/DD/YYYY.

I want to avoid adding a variable to the repository.

Any help would be appreciated.

Raymond

Gianni Ceresa

OBIEE doesn't speak SQL but LSQL, so the prompt expect a LSQL query there.

You can get the last day of current month in various ways, either using your time dimensions (which could have a flag to identify that last day of month) or by using some arithmetic on dates starting from the current date.

Turn your query into an OBIEE query and it's going to work.

Christian Berg-0racle

Ray Kelly wrote:I want to avoid adding a variable to the repository.

Yes to everyhting what ianni said and from me: WHY do you want to avoid a variable in the RPD? Why do you want to avoid a centralized, controlled, reproducible, reusable and consistent way in favour of one-shot spaghetti code?

asim cholas

try below formula (untested)

Select timestampadd(sql_tsi_day,-dayofmonth(CURRENT_DATE),timestampadd(sql_tsi_month,1,CURRENT_DATE)) FROM "YOUR SUBJECT AREA"

Gianni Ceresa

Almost there, the formula isn't fully right but it's the "arithmetic of dates" approach suggested above.

The OP will easily be able to get the right one.

asim cholas

Best way is to use the time dimension and populate it in a field.but if there is no other go use substring function and concatenate to make desired format