Oracle Transactional Business Intelligence

Products Banner

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

Received Response


AsOfDate prompt causes errors OPR4ONWY:U9IM8TAC:OI2DL65P


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:

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!



  • 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()}

  • 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


  • Hi,

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


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




  • 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"

  • 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.

  • This will work to replace the hard-coded date:

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

  • 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 = '@{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,

  • I have similar requirement, any luck with this?