Last run date parameter BI Report

Having the last run date is quite useful in reports particularly if you are interested in incremental reports. There is no straightforward way for doing this Oracle Cloud BI.


Below are the steps I have done this -

1. Define list of values SQL based.
Name last_run_time_lov
Query (Only one value is returned)
SELECT MAX(processend-30) last_end_time
FROM fusion.ess_request_history erh, fusion.ess_request_property erp
WHERE 1 = 1 AND erh.product = 'BI Publisher'
AND erh.requestid = erp.requestid
AND erp.name = 'report_url'
AND erp.VALUE LIKE '/Custom/Test/AP Rejections.xdo'
AND erh.state IN (4,12)

2. Create a menu based parameter and assign the LOV
3. Uncheck Select All and now the only value is defaulted when you run the report.