Dyn Filter; Trying to Retrieve 13 Weeks of Data from EPM Dataset Using BETWEEN Clause

User_WM6I1

Hello Oracle Community,

I am working on a project where I am utilizing a dataset from Oracle Enterprise Performance Management (EPM) Cloud. I am using a direct connection to access the data, hence I don't have the privilege to alter the data at source. In the dataset, time information is stored as text/string, which necessitates a conversion to a date or timestamp format (I've tried date format as well) for my analysis. My objective is to retrieve 13 weeks of data based on a selected date.

Here's the formula I am using to filter the data:

CAST("Period"."Period Name Gen 6" AS TIMESTAMP)

BETWEEN

TIMESTAMPADD(SQL_TSI_DAY, -91, CAST(@parameter("SelectedDate")('2022-12-31') AS TIMESTAMP))

AND CAST(@parameter("SelectedDate")('2022-12-31') AS TIMESTAMP)

However, when executing this query, I am encountering a syntax error near the <BETWEEN> clause with error code [nQSError: 26012]. I have triple-checked the syntax but can't seem to pinpoint the issue.

Could anyone provide insights or suggestions on what might be wrong with the query, or how to correctly perform this operation given the constraints?

Thank you in advance for your assistance!

Mallikarjuna Kuppauru-Oracle

Hi user_WM6I1 ,

Seems to be an issue with below function conversion.

TIMESTAMPADD(SQL_TSI_DAY, -91, CAST(@parameter("SelectedDate")('2022-12-31') AS TIMESTAMP))

You can try below format

Syntax -

cast(TIMESTAMPADD(SQL_TSI_HOUR,-6 ,CURRENT_TIMESTAMP ) as date)

Regards,

Arjun

User_UXDSV

Hi,

Thanks Arjun, So you are saying it should look more like this:

CAST("Period"."Period Name Gen 6" AS DATE)

BETWEEN @parameter("SelectedDate")('2022-12-31') AND CAST(TIMESTAMPADD(SQL_TSI_DAY, -91, CURRENT_TIMESTAMP) AS DATE) 

If so....still getting this:

Validation of parameter values failed. Near <BETWEEN>: Syntax error [nQSError: 26012] .

Mallikarjuna Kuppauru-Oracle

Hi,

Seems like values are not calculating correctly. Your expression should like below so that it will validate.

'SYSDATE' BETWEEN (@parameter("Date")('05/08/1951')) AND 'sysdate'

Take one by one and calculate the values, After that you can put everything in Expression filter.

Regards,

Arjun

User_UXDSV

Hi,

It definitely works in the Expression Filter...thanks.

CAST("Period"."Period Name Gen 6" AS DATE) BETWEEN CAST(@parameter("SelectedDate")('2022-12-31')AS DATE) AND CAST(TIMESTAMPADD(SQL_TSI_DAY, -91, CURRENT_TIMESTAMP) AS DATE) 

Added it to my filter section, and now I can see values in my parameters. The Period Name is breaking the report now. I need to troubleshoot that....but at least that works....thank you!

Mallikarjuna Kuppauru-Oracle

HI User_UXDSV ,

Good to see your update , Let me know if you still need some help on this formula i can try in my local system and get back to you with updated expression .

Regards,

Arjun

Mallikarjuna Kuppauru-Oracle

Hi @User_UXDSV

Are you able to resolve the issue?

To enhance your experience and foster meaningful interactions, we encourage you to personalize your profile by setting up a display name and uploading a profile picture. Your display name will be how others recognize and engage with you in discussions, while a profile picture adds a personal touch to your forum presence. Take a moment to update your profile with a display name and an image representing you. Let's create a vibrant and engaging community together!

Regards,

Arjun