How to Schedule a BI Publisher Report with Dynamic time range in parameter Values

Rajesh Kumar Gunda

Issue Description:
We need to view the BIP Reports -'Execution History by User' and 'BIP Reports Execution Summary'. To accommodate this, we are utilizing one of Oracle's standard reports, the "BIP Report Audit Trail," located in the Audit Reports section.
The Client specific requirement is to filter the report data based on a dynamic time range, such as the last 2 or 4 hours, and to have these time range parameters be dynamically adjustable when scheduling the report.

Current Challenge:
In our initial attempt to meet this requirement, we utilized the following parameters to retrieve data for the past 24 hours: {$SYSDATE()-1$}
{$SYSDATE()$}.
However, this approach does not allow for the dynamic adjustment of the time range as per the requirement. Consequently, we are unable to provide a report that accurately reflects the desired time-specific data.

attempted to specify a 4-hour range by using the parameters {$SYSDATE()-.16$} or {$SYSDATE()-(4/24*4)$}, which theoretically represent the current time minus 4 hours. However, these expressions were not accepted by BI Publisher, and as a result, I did not obtain the desired outcomes. Refer the attachment In-detail for the same.

SR Reference:
We have previously raised a SR with Oracle Support (SR# 3-37375542081) regarding how to schedule a BI Publisher report with dynamic time range parameters.
Oracle Support recommended that we create a CCC request for further assistance.

Request for Solution:
Given the above, we kindly request your support in finding a solution that allows us to dynamically adjust time range parameters when scheduling BIP reports.
This functionality is crucial for our client to obtain the specific data they need within the desired time frames.

SR3-37375542081.docx

Raghavan p-Oracle

Hi,

Generally {$SYSDATE()-1$} means the previous day, I don't think hours can be deducted.

Let's see if anyone shares some info on this.

Regards,

Raghavan

Gaurav Bharadwaj-Oracle

Hello @Rajesh Kumar Gunda,
Thank you for posting this question about dates in the audit reports.
As Raghavan mentioned, your usage of the parameter or rather the understanding is incorrect.

You can try to modify the query in the data model to accept 2 dates, and then you can pass start date as 1 parameter and end date as another and get the data between these 2 dates.

This would require reviewing the SQL and its structure and changing it a bit so it accepts 2 date parameters instead of 1 and then mapping those 2 parameters to 2 separate bind variables.

Something similar is discussed here, you can review it :

https://community.oracle.com/products/oracleanalytics/discussion/15125/in-oracle-bi-report-publisher-i-need-to-set-querter-start-and-end-date-as-from-date-and-to-date-par

If my response has answered your question or assisted you with your concern, please click "yes" below to accept the answer or comment with any additional queries. You can also read the Cloud Customer Connect Guidelines for Accepted Answer

Thanks
Gaurav

Rajasekhar Reddy Adam

You should have to use

{$SYSDATE()-1/6$} or

{$SYSDATE()-4/24$}