General OBIEE

Get Involved. Join the Conversation.


    Brenda Jacks
    Presentation Variable Prompt causes Error on Results tab,...Answered
    Topic posted April 10, 2017 by Brenda JacksSilver Medal: 2,000+ Points, tagged Analyses, Core HR, Customizing Reports, Fusion, OBI Answers, OBIEE Answers, OTBI, Report Output, Reports, SQL 
    317 Views, 6 Comments
    Presentation Variable Prompt causes Error on Results tab, but not in Open link
    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:

    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!


    Best Comment

    Brenda Jacks

    This will work to replace the hard-coded date:

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



    • Rachel Martorelli

      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


      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


    • Ramesh Nakka


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


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




    • Brenda Jacks

      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

      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.