Add division parameter to OTBI Report

Trent M

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):

Sovitendu Jalli-Oracle

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

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

Regards,

Trent

Sovitendu Jalli-Oracle

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

How would you extract just segment 4?

Sovitendu Jalli-Oracle

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

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

Thanks,

Trent

Sample Expense Data.xlsx

Sovitendu Jalli-Oracle

Hi Trent,

Is Segment 2 = 100 in this COA -->

Please confirm.

Trent M

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

Thanks,

Trent

Sovitendu Jalli-Oracle

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

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