Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Wendy Ware
    Seeking Assistance Querying for Basic Transactional MetricsAnswered
    Topic posted October 18, 2018 by Wendy WareBronze Crown: 15,000+ Points, tagged BI Publisher, Fusion Financial reporting, OBI Answers, OBIEE Answers, OTBI, Reports 
    105 Views, 4 Comments
    Title:
    Seeking Assistance Querying for Basic Transactional Metrics
    Summary:
    How to get counts of journal lines, invoices, and payments per month?
    Content:

    I'm hoping someone can give me a head start on how to query for the following transactional metrics in Fusion Financials tables:

    Number of GL journal lines added per month
    Average number of lines per GL journal
    Maximum number of lines per GL journal
     
    Number of supplier invoices per month
    Average number of distribution lines per supplier invoice
    Maximum number of distribution lines per supplier invoice
     
    Number of supplier payments per month

    Any assistance appreciated.  Wendy

     

    Version:
    Rel 13 18A

    Best Comment

    Prasanna Reka

    Wendy - I believe you are planning to use BI data models then only tables come handy, if you are using subject areas you need to use aggregate functions.

    Anyways including table names, I will also attach complete list for all modules, you can search by module-

     

    GL Journals: GL_JE_BATCHES, GL_JE_HEADERS, GL_JE_LINES ****For Lines

    Supplier Invoices: AP_INVOICES_ALL, AP_INVOICE_DISTRIBUTIONS_ALL *** For Distributions

    Supplier Payments: AP_INVOICE_PAYMENTS_ALL,AP_PAYMENT_SCHEDULES_ALL

    Attachment: Fusion_ALL_TABLES Report.xlsx

     

    Comment

     

    • Prasanna Reka

      Create report with period parameter and on unique constraint columns use edit formula— Agregate function—count/average.

      Performance Tiles are good option if you are not using group by

      Let me know if you can’t figure it out i can send screenshots.

       

       

    • Wendy Ware

      Perhaps I should clarify that I'm looking for assistance with what tables/views to use: GL journal lines, supplier invoice distribution lines, supplier payments.

      • Prasanna Reka

        Wendy - I believe you are planning to use BI data models then only tables come handy, if you are using subject areas you need to use aggregate functions.

        Anyways including table names, I will also attach complete list for all modules, you can search by module-

         

        GL Journals: GL_JE_BATCHES, GL_JE_HEADERS, GL_JE_LINES ****For Lines

        Supplier Invoices: AP_INVOICES_ALL, AP_INVOICE_DISTRIBUTIONS_ALL *** For Distributions

        Supplier Payments: AP_INVOICE_PAYMENTS_ALL,AP_PAYMENT_SCHEDULES_ALL

        Attachment: Fusion_ALL_TABLES Report.xlsx