Skip to main content

How to Use the As At Open Item Dashboard

Updated over 2 weeks ago

Prerequisites

Power BI Desktop is required to use the Coins standard dashboard.

To start using the "AsAtOpenItem" dashboard

  1. "AsAtOpenItem" dashboard is based on following standard Datamarts which both reside under the menu : "General Ledger >> Reports >> Period Processing Reports":

    • "AP/SC open Item Snapshot"

    • "SL/CS Open Item Snapshot"

    To start using this dashboard, you need to run both Datamarts for previous periods. Moving forward, these Datamarts should be scheduled for future periods so that they run automatically at the end of each period.


    ​

  2. Either before or during the process, the following entities must be generated, their corresponding SQL tables must be created, and their data must be extracted.

    • APOpenItemFact

    • APSCOpenItem

    • ARCSOpenItem

    • AROpenItemFact

    • CSOpenItemFact

    • SCOpenItemFact

  3. Create the required "Generic" views:

    1. Go to System >> Extracts >> Views.

    2. Filter the Schema for "Generic".

    3. Select all the rows.

    4. Run "Create Views".
      ​

    Generic.APInvoice, Generic.APVendor, Generic.APInvoicePayment, Generic.ARInvoice, Generic.ARCustomer, Generic.ARInvoicePayment, Generic.CSCertificate, Generic.CSCertificatePayment, Generic.SCCertificate, Generic.SCCertificatePayment, Generic.SCSubcontractor, Generic.Calendar, Generic.GLPeriod, Generic.JCJob, Generic.COCompany
    ​

  4. Create "AsAtOpenItem" views:

    1. Go to "System >> Extracts >> Views.

    2. Filter the Schema for "AsAtOpenItem".

    3. Select all the rows.

    4. Run "Create Views".

    Note: Views are created based on Coins SQL tables. This means all required SQL tables must already exist when you try to create the views.

  5. Download the "PBIT" template for "AsAtOpenItem" report from System >> Extracts >> Reports.

  6. Once the dashboard template is downloaded, open the file in Power BI Desktop, and enter the server name and database.

  7. At this point the dashboard will connect to SQL and if this is the first time you connect to SQL from this dashboard it will ask for credentials.

  8. You should be able to refresh the template with your data by clicking on the "Refresh" button under the Home tab.

  9. Finally, save the dashboard which will have a ".PBIX" extension.

Did this answer your question?