OTBI Dashboard Prompt Timestampadd Formula for Default End Date Covering through EOD 11:59:59pm

Question

I needed to default the begin and end date dashboard prompt for First Day of Previous Month through Last Day of Previous month using the following timestamp formulas respectively:

First Day of Previous Month:

TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

Last Day of Previous Month:

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

My Time Zone is (- 5 GMT Eastern USA and Canada), I set the time zone in the prompt to default.

I get the following default values in the prompt:

The Begin Date is what I want: 12/01/2024 12:00:00 AM

The End Date goes only to 12/31/2024 12:00:00 AM.  I the value I want would be 12/31/2024 11:59:59 PM to pick up all the records through the last day of previous month.

I've tried putting in a - 1 second value into the End Date default formula above to no avail.

How can I accomplish my goal above please?

Bhaskar Konar · Answer

Hi @User_VGJDM,

PFB the catalog file built using OTBI SA.

FDoPM LDoPM - Prompt.catalog

Hope this help.

Cheers,

Bhaskar Konar · Answer

https://community.oracle.com/products/oracleanalytics/discussion/24445/otbi-dashboard-prompt-timestampadd-formula-for-default-end-date-covering-through-eod-11-59-59pm

Hi @User_VGJDM,

Please try the following Queries, it is working for me.

FDoPM:

SELECT

   CAST((CAST(TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) AS CHAR)) AS TIMESTAMP) s_1

FROM "Absence Management - Leave Donations Real Time"

LDoPM:

SELECT

   CAST((SUBSTRING(CAST(TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) AS CHAR) FROM 1 FOR 10) || ' ' || '23:59:59') AS TIMESTAMP) s_1

FROM "Absence Management - Leave Donations Real Time"

I'm getting the following results using the above:

Hope this help.

Cheers,