Oracle Transactional Business Intelligence

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Add division parameter to OTBI Report

Received Response
383
Views
14
Comments

Summary:

I am trying to create an OTBI report under the expense report templates to show approvers of given POs. I am able to add fields for company, department, and account but I need a field for division as well. Is there any field that exists for division?

Content (required):

Expenses - Expense Transaction Real Time report template

Version (include the version you are using, if applicable):


Code Snippet (add any code snippets that support your topic, if applicable):


«1

Answers

  • Sovitendu Jalli-Oracle
    Sovitendu Jalli-Oracle Rank 2 - Community Beginner

    Hi Trent,

    I am not an OTBI Finance expert but with my very limited knowledge what I know is that in the Chart of Account concatenated segments the Division ID/Code should be present if it is defined in your COA. Maybe you can extract it by writing a substring/Instring function expression.

    I am not 100 % sure about the info I have shared, but you can give it a try.


    Regards,

    Sovi

  • Trent M
    Trent M Rank 3 - Community Apprentice

    Thank you Sovitendu! This will be a good workaround to use for now. Appreciate your help!

    Regards,

    Trent

  • Sovitendu Jalli-Oracle
    Sovitendu Jalli-Oracle Rank 2 - Community Beginner

    You are welcome Trent. Let me know if you need any help in extracting the Division ID/Code from the Concatenated Segment. Happy to help you.

    Regards,

    Sovi

  • Nicolas_Trojack
    Nicolas_Trojack Rank 1 - Community Starter

    How would you extract just segment 4?

  • Sovitendu Jalli-Oracle
    Sovitendu Jalli-Oracle Rank 2 - Community Beginner

    Hi Nicolas_Trojack,

    You can use Substring/Instring (and maybe some other pre-defined ) function to extract Segment 4 from COA.

    Let me know if you have any samples, maybe I can come up with an expression.


    Regards,

    Sovi

  • Trent M
    Trent M Rank 3 - Community Apprentice

    I have a sample data set here. Is it possible to extract segment 2 from COA?

    Thanks,

    Trent


  • Sovitendu Jalli-Oracle
    Sovitendu Jalli-Oracle Rank 2 - Community Beginner

    Hi Trent,

    Is Segment 2 = 100 in this COA -->

    Please confirm.

  • Trent M
    Trent M Rank 3 - Community Apprentice

    Yes, segment 2 would be 100 in the example above.

    Thanks,

    Trent

  • Sovitendu Jalli-Oracle
    Sovitendu Jalli-Oracle Rank 2 - Community Beginner

    Hi Trent,

    If your Segment 1 & Segment 2 lengths are 3 (as per the samples provided) then you can use the below expression in an analysis column

    SUBSTRING('450.100.16300.7500002.000.00000' FROM 5 FOR 3)

    **Replace '450.100.16300.7500002.000.00000' with the actual column name.

    If the Segment 1 & Segment 2 lengths are different than 3 then it has to be handled in a different expression. I will share the expression later.


    Let me know if you have any questions.

    Regards,

    Sovi

  • Trent M
    Trent M Rank 3 - Community Apprentice

    Hi Sovi,

    Thank you. I do have one question. How do I add that analysis column on the report? Segments 1 & 2 will always be lengths of 3.

    Thanks,

    Trent