8 Replies Latest reply: Oct 23, 2012 9:08 AM by 964810 RSS

    gl_balance_F and Gl_other_F are not joined together for drill down????

    Danny
      Hi ,

      Isnt it a standard practice in organizations to see the summary/ balance sheet and wanting to drill down to the next level as to see the journal lines? I am not interested in knwing how to drill through/down from Journal lines till subledgers as we dont hv them here. Trying to find the source for the details though.

      So my main requirement is when the client checks the balance sheet, he sees the summary dollar amounts in the report. If he clicks on any one amount, he should be able to see how that amount was arrived to i.e. the details/journal line entries. IS that not delivered by default in OOTB Financial Analytics or am I missing something here? Are GL_BALANCE_F and GL_OTHER_F not joined internally?

      IF the above is the case, how do I achieve what the client requires? Just a valid real time idea required. I dont need step by step instructions.

      Just in case its not clear, an ATM transaction is the best example I can think of. If I see my balance increase/reduce from prev month, I take a mini transaction which shows where all I spent my money.

      Ahsan, any feedback on this?

      Thanks,
      Dan
        • 1. Re: gl_balance_F and Gl_other_F are not joined together for drill down????
          657306
          It is possible to drill from an account balance to see the journals for the account. You would build a separate report that gets journals and set up a navigation type drill from the source report (balance) to the target report (journals). The target report would have "isprompted" filters on all the dimension attributes that you want to pass from source report to target as filters - for example, time period, account number etc.
          • 2. Re: gl_balance_F and Gl_other_F are not joined together for drill down????
            Danny
            I came up with the same idea but my point is this functionality is a universally standard thing which people do in every organization. Why would Oracle not provide this by default? Is there no other way I can join balance and detail without creating a 2nd report(detail) and then build a guided navigation?

            Thanks,
            Dan
            • 3. Re: gl_balance_F and Gl_other_F are not joined together for drill down????
              641716
              Oracle extracts both the balance data GL_BALANCES -> W_GL_BALANCES_F (Actual) / -> W_ACCT_BUDGET_F (Budget) and the journal data GL_JE_LINES -> W_GL_OTHER_F but has them separated in the subject areas. Each subject area has a different level of detail.

              For the summary detail you want to build reports from Financials - GL Budget and Expenses selecting each of your reporting segments.

              For the transactions or journal detail you want to build reports from the Financials - GL Detail Transactions with the same segments.

              What you can do then is setup a drill from the Actual or Budget amount in the summary to journal transactions by having each segment prompted in the drilled report.

              You could essentially build the summary report by not adding any journal detail, but by just having your segments and a fact (Transactions Amount) in the Financials - GL Detail Transactions subject area and you should get the same numbers as the balances summed up for the same segments. From there you can add journal detail and then have subtotals appearing for whatever segments you want to subtotal by. This should achieve your goal.

              Most places I have worked at like to see a summary off the balances based on their reporting segments and then want to drill to the detail showing journal lines, and then from there drill to the sub-ledger details (POs, Invoices etc.)
              • 4. Re: gl_balance_F and Gl_other_F are not joined together for drill down????
                Ahsan Shah
                I believe in Financial Analytics you have 2 options to populate the W_GL_BALANCE_F..one is directly from the source system GL, and another is calculated in the ETL process based off the W_GL_OTHER_F based on specific DAC configuration tags. Essentially, the navigation links can allow you to "drill down" as needed from one level to another..but from a DW standpoint, these are 2 separate business processes..with 2 different granularities...which is why they are in 2 different star schemas. For example, W_GL_OTHER_F will have a lot more dimensional attributes than the W_GL_BALANCE_F table. Normally in dimensional modeling you do not want to "join" 2 fact tables (at least from my understanding of Kimball). You can however, have allocate attributes from a table of higher granularity to that of a lower granularity. Not sure if this helps answer your question.
                • 5. Re: gl_balance_F and Gl_other_F are not joined together for drill down????
                  Danny
                  Thanks Ahsan. Yup I am planning to implement it that way.

                  Birchy, "From there you can add journal detail and then have subtotals appearing for whatever segments you want to subtotal by. This should achieve your goal.". THis is exactly what I am asking how could I do it? How do I add subtotals to the totals report? Create another report having just subtotals right? Am I correct in understanding your point?

                  Thanks,
                  Dan
                  • 6. Re: gl_balance_F and Gl_other_F are not joined together for drill down????
                    641716
                    If you go to the table view, above each field you will see a little sum sign, this will allow you to sum by that specific field.
                    • 7. Re: gl_balance_F and Gl_other_F are not joined together for drill down????
                      user8024884
                      Hello,

                      Has anyone generated a design that allows for summary of the budget, actuals, encumbrances in one dashboard by GL account and then drill down from there to the respective details?

                      Due to the facts being in multiple fact tables we are having difficulty with this design (we are new to this).

                      Any comments?
                      • 8. Re: gl_balance_F and Gl_other_F are not joined together for drill down????
                        964810
                        I realise this post is not current, however this is fetched in search results and felt I'd add an additional detail that I just learnt.

                        http://docs.oracle.com/cd/E12104_01/books/AnyInstAdm/AnyImp_ConfigFinance6.html

                        Oracle BI Applications provides two ways to populate the GL balances (stored in the W_GL_BALANCE_F table), as follows:

                        By extracting the GL balances directly from Oracle General Ledger, as follows:
                        In DAC, for the Subject Area 'Financials - General Ledger', in the 'Configuration Tag' tab, make sure that the tag 'Oracle - Extract GL Balance' is unchecked for the 'Inactive' checkbox.
                        Make sure the tag 'Financials - Calculate GL Balance is checked in the 'Inactive' checkbox.
                        Click Assemble to redesign the subject area.
                        After the subject area has been redesigned, redesign the execution plans that contain this subject area.
                        By calculating the GL balances based on the records in the W_GL_OTHER_F table, which stores all journal lines, as follows:
                        In DAC, for the Subject Area 'Financials - General Ledger', in the 'Configuration Tag' tab, make sure that the tag 'Financials - Calculate GL Balance' is unchecked for the 'Inactive' checkbox.
                        Make sure the tag 'Oracle - Extract GL Balance' is checked in the 'Inactive' checkbox.
                        Click Assemble to redesign the subject area.
                        After the subject area has been redesigned, redesign the execution plans that contain this subject area.