Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Kamran Butt
    Syntax Error While Using Evaluate in OTBI Analysis
    Topic posted September 29, 2019 by Kamran ButtRed Ribbon: 250+ Points, tagged BI Publisher, Fusion Financial reporting, OBI Answers, OBIEE Answers, OTBI, Public Sector 
    29 Views, 2 Comments
    Title:
    Syntax Error While Using Evaluate in OTBI Analysis
    Summary:
    Evaluate function generates a syntax error when using REGXP_SUBSTR
    Content:

    I am attempting to trim the following column in the General Ledger - Journals Real Time subject area: "- Account"."Code Combination Description". By default, this column will result in a value that has all 7 segments concatenated together with a period (ex. General Fund.City Commission.Legislative.Equipment Rental and Lease.Default.Default.Default).

    From this concatenated string I want to parse out the values between the 3rd and 4th periods (ex. Equipment Rental and Lease). 

    I attempted to use the EVALUATE function with REGXP_SUBSTR to parse out only the necessary information but I continuously receive a syntax error even if I follow the example shown on the screen. I verified my account privileges and I have permission to use the EVALUATE function.

    Formula: EVALUATE('REGEXP_SUBSTR(%1, ''[^.]+,1,3'')', AS VARCHAR "- Account"."Code Combination Description"

    Any hints would be helpful. Thanks!

     

    Version:
    Oracle Cloud 19C (11.13.19.07.0)
    Code Snippet:

    Comment

     

    • Michael Cook

      Hi Have you looked at reporting structure in General Ledger - Transaction Real Time (OTBI) and General Ledger - Balance Real Time (Essbase) to see if the Seperate COA values have been brought across from the Essbase cube for reporting and you could use those instead??