You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register
Get Started: AI Resources for Oracle Cloud HCM – Go Here

Progress with Redwood: Redwood Resources for Oracle Cloud HCM -  Go Here

Need Help to fix Date parameter while integrating BI report into Dashboard

edited Nov 24, 2021 8:29AM in Reporting and Analytics for HCM 1 comment

Summary:

I have created a SQL with a date parameter that calculates effective date minus 5 days.

Below is the code which is working well while running normally with BI reports.

AND (: P_EFFECTIVE_DATE-5) BETWEEN A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE

The same code failed with the error ": ORA-01722: invalid number" while using the report in the dashboard.

Even I am using this code and getting the same error "ORA-01722: invalid number":

AND (:P_EFFECTIVE_DATE-5) BETWEEN to_char(trunc(A.EFFECTIVE_START_DATE), 'yyyy-MM-dd') AND to_char(trunc(A.EFFECTIVE_END_DATE), 'yyyy-MM-dd')

Please suggest a way to fix it. So, that I could use this bi report in Dashboard.

Howdy, Stranger!

Log In

To view full details, sign in.

Register

Don't have an account? Click here to get started!