Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Ujas Bhatt
    Extract Cost Center from "Code Combination...
    Topic posted December 13, 2017 by Ujas BhattSilver Trophy: 7,500+ Points, tagged BI Publisher, Fusion Financial reporting, Fusion Procurement reporting, Fusion Supply Chain Management reporting, OBI Answers, OBIEE Answers, OTBI, Reports, Sample Reports, Tip 
    276 Views, 5 Comments
    Title:
    Extract Cost Center from "Code Combination Description" field - (Procurement - Spend Real Time) subject area
    Summary:
    Extract Cost Center from "Code Combination Descpription" field
    Content:

    I am using "Code Combination Description" field as one of my select columns from (Procurement - Spend Real Time) subject area. It is available under Procurement - Spend Real Time >> Invoice details >> Distribution Combination >> Code Combination Description.   

    Primarily I would like to check the No.Of Invoices entered and No. of Invoices Paid under a specific Cost Center.  

    Currently, the value under "Code Combination Description Field" appears like  HOV.LOB.POP CENTER.EXPENSE-VISION.DEFAULT.DEFAULT.DEFAULT.DEFAULT).  Now, I would like to extract only "POP CENTER" which is the 3rd Segment in Code Combination Description field separated by "."

    I am looking for a formula to extract "POP CENTER" (which is a Cost Center segment) in Code combination. 

    Can anyone assist with the formula on how to extract a particular segment field value in OTBI analysis? 

    Thanks !!

    Comment

     

    • Chris Pinto

      Hi Ujas,

      Try adding the field "Cost center description" (Accounting -> Cost center -> Cost center description) alongwith the Code combination description field you already have. It should fulfill your requirement. I have attached a screenshot for your reference.  

    • Ujas Bhatt

      Hi Chris,

      I tried adding the cost center description field, Unfortunately, the same is not working for me. see the attached screenshot.

      Thanks !!

       

    • Ujas Bhatt
    • Sameh Rady Soliman

      Add GL job role and data access set to see the cost center desc.

       

      also this the formula you looking for

       

      SUBSTR('place your cost center column name here',INSTR('place your cost center column name here', '.', 1, 2)+1, INSTR('place your cost center column name here', '.', 1, 3)-1)

    • Ujas Bhatt

      Thank you all !!! i have been able to achieve the result.