Presentation Variable Prompt causes Error on Results tab, but not in Open link

Brenda Jacks-49721

Summary

AsOfDate prompt causes errors OPR4ONWY:U9IM8TAC:OI2DL65P

Content

I added the follow prefix to my analysis:  SET VARIABLE PARAM_EFFECTIVE_DATE='@{AsOfDate}';

Then I created a Presentation Variable prompt for AsOfDate with the following:

Default Selection: Variable Expression  @{system.currentTime}[MM/DD/YYYY]

 

This works when I run the analysis from the Open link, but when I am in Edit mode I get the following error on the Results tab:

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 77031] Error occurs while calling remote service ADFService11G. Details: ADFException-1004: Invalid/empty ADF query. (HY000)

 

Is this normal behavior?  Or is there something I can do so I can see the Results while I am working on the report?

 

Thank you!

Brenda

 

Rachel Martorelli-Oracle

I don't know what you are trying to do, but when you create a presentation variable if you want to set a default in a formula it would look like @{AsOfDate}{Now()}

Rick Brobbel

Hi

I am experiencing problems in using variable dates as well.

In my case the variable is not a presentation variable, but a repository variable.

I have an SR open still with Oracle Support about this.

Apparently it's not so obvious.

I would suggest you do the same: open an SR.

Kind regards

Rick

Ramesh Nakka

Hi,

For Any Date prompt  in MM/dd/yyyy Presentation variable can be defined as follows

 

@{pv_date}{DATE '1900-01-01'}

 

Regards,

Ramesh

Brenda Jacks-49721

I tried opening an SR and got the following response:  "When you have presentation variable with set variable in the report advance tab, you have to execute this report from BI catalog Open link"

Brenda Jacks-49721

I was able to return results in edit mode of the analysis when I use this Advanced SQL Prefix:  SET VARIABLE PARAM_EFFECTIVE_DATE = '@{AsOfDate}{2017-01-01}';

I've tried many variations to replace the hard-coded date with a keyword such as CURRENT_DATE in YYYY-MM-DD format, but I can't seem to get the syntax quite right.

This is definitely an improvement that allows you to return results when testing, but it doesn't interfere with the Presentation Variable Prompt that has been setup.

Brenda Jacks-49721

This will work to replace the hard-coded date:

SET VARIABLE PARAM_EFFECTIVE_DATE = '@{AsOfDate}{@{system.currentTime}[YYYY-MM-DD]}';

MaartenDIF

Hi Brenda,

Thanks for this code snippet, it is helping me get on the way but I'm not quite there yet for my own requirement. I want to run the report with 30 days (or 1 month, it does not have to be very accurate) added to the sysdate.

I find it very hard to find what the syntax is in this SQL prefix. Tried a number of things like:
SET VARIABLE PARAM_EFFECTIVE_DATE = 'TO_CHAR(TIMESTAMPADD(SQL_TSI_DAY, 30,@{system.currentTime}[YYYY-MM-DD]))';
SET VARIABLE PARAM_EFFECTIVE_DATE = '@{system.currentTime+30}[YYYY-MM-DD]';
SET VARIABLE PARAM_EFFECTIVE_DATE = '@{system.currentTime}[YYYY-MM-DD]+30';

Most of the times, the code gets past the validation when clicking 'Apply SQL' but when running the results, I still get the error
"State: HY000. Code: 77031. [nQSError: 77031] Error occurs while calling remote service ADFService11G. Details: ADFException-1004: Invalid/empty ADF query. (HY000)"

Any idea how to resolve this?

Kind regards,
Maarten

CB

I have similar requirement, any luck with this?