Filtering OTBI report off of the current system date?

Alexander George Demczak III

I am building a report that pulls counts of total Development goals from "Workforce Career Development - Development Goal Overview Real Time."

Looking to filter the population by both the "Start Date" column, and "Target Completion Date" column both found in the Development Goals Folder.

I would like it to be "Is less than or equal to Current System date" for Start Date. As well as "Greater then or equal to Current System Date" for Target Completion Date.

What is the best means to complete this? I assume ill have to leverage SQL which isn't a strength of mine, but if someone can break down a good example I am capable of following.

Nathan CCC

Hi,

Yes you can use variable in your where clause in your filter to reference function CURRENT_DATE.

select all 0 s_0

, "Time"."Report Start Date" s_day_date

, timestampadd(sql_tsi_day,-7,CURRENT_DATE) s_from

, timestampadd(sql_tsi_day,0,CURRENT_DATE) s_to

from "OTBI Usage Real Time"

where

("Time"."Report Start Date" >= timestampadd(sql_tsi_day,-7,CURRENT_DATE) 
and "Time"."Report Start Date" <= timestampadd(sql_tsi_day,0,CURRENT_DATE) )

For more information see
Oracle® Fusion Middleware
Logical SQL Reference Guide for Oracle Business Intelligence Enterprise Edition
Release 12c (12.2.1.3.0)
E80604-01
August 2017

Calendar Date/Time Functions

https://docs.oracle.com/middleware/12213/biee/BIESQ/index.html#GUID-BEC4AA93-04D4-4850-8E54-836AA219B8AC

Alexander George Demczak III

@Nathan CCC

This is definitely helpful. I am still learning, what does that look like entered into this screen?

My SQL is not strong but trying to take this in, i would have a start date filter like this:

select all

from "Workforce Career Development - Development Goal Overview Real Time"

where

("Development Goals"."Start Date" >= CURRENT_DATE)

And a Target Completion Date filter like this:

select all

from "Workforce Career Development - Development Goal Overview Real Time"

where

("Development Goals"."Target Completion Date" <= CURRENT_DATE)

This is not working so I clearly have something wrong.

I appreciate any additional guidance.

Nathan CCC

Hi, In your filter dialog Edit Filter. Delete SQL Expression. Put any value in Value. Then select "convert this filter to SQL". Then update the value with CURRENT_DATE.