Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Angel Espadafor
    OTBI - Combining gl segments values with Suject areas
    Topic posted January 20, 2017 by Angel EspadaforGreen Ribbon: 100+ Points, tagged Fusion Financial reporting, OBIEE Answers, OTBI 
    523 Views, 6 Comments
    Title:
    OTBI - Combining gl segments values with Suject areas
    Summary:
    Problems whenusing the Real Time Subject Areas combining with the tree hierarchy for GL Values
    Content:

    My customer is trying to build an OTBI report  using the Real Time Subject Areas  combining with the tree hierarchy for GL Values (Parent /Child) , but they were not getting the "Values Description"

    There is an ER open  as it seems  "Description" of the values is not present in OTBI ( BUG 21188652 - SUPPORT GL SEGMENT DESCRIPTIONS)

    If you have found a workaround for this , your feedback appreciated. The BUG was raised in 2015 and is in roadmap but for R14.

    Regards

    Angel

    Comment

     

    • Andy Gough

      Hi Angel,

      The nearest I've come to a suggested workaround is to use a Union report to join the "General Ledger - Balances Real Time" and "General Ledger - Transactional Balances Real Time" subject areas. The latter has segment descriptions, but only for the posting-level values, not for the hierarchy levels.

      Andy

      • Angel Espadafor

        Hi thanks for your comment but the problem is that in our report we need to display the hierarchy columns (cost center and natural account) and this functionality is not allowed when using union between subject areas as you said.

        Angel

    • Andy Gough

      Yes, that is a problem. Not sure what else to suggest - use an FR report instead maybe?

      A

    • Linda Wong

      You may use Smart View or Financial Reporting Studio reports as a workaround.  Segment value descriptions display in Smart View and Financial Reports.

    • Ba Kwon

      Consider creating a BIP report. The users can then run the report. Here's a sample query for your data model (unit tested in R11) -- note : the query works "as is" by replacing tree name, tree version and value set name. It's a relatively simple report to build. Modify the query as you see fit.

      select tv.tree_code "Tree_Name"
      , tn.parent_pk1_value "Parent"
      , tn.pk1_start_value "Child"
      , fvp.description "Parent Value Description"
      , fvc.description "Child Value Description"
      from FND_TREE_NODE tn
      , FND_TREE_VERSION_TL tv
      , FND_FLEX_VALUES_VL fvp
      , FND_FLEX_VALUE_SETS fsp
      , FND_FLEX_VALUES_VL fvc
      , FND_FLEX_VALUE_SETS fsc
      where tn.tree_version_id = tv.tree_version_id
      and tv.tree_code = '<your tree name>'
      and tv.tree_version_name = '<your tree version>'
      and tv.language = 'US'
      and fvp.flex_value_set_id = fsp.flex_value_set_id
      and fvc.flex_value_set_id = fsc.flex_value_set_id
      and tn.parent_pk1_value = fvp.flex_value
      and tn.pk1_start_value = fvc.flex_value
      and fsp.flex_value_set_name = '<your value set name>'
      and fsc.flex_value_set_name = '<your value set name>'

    • Andy Gough
      Thanks! I'll give that a try.
      
    • Jairo Rojas Mendez

      Great!wink