Support Portal

Reporting basics using PowerBI or Excel

Owned by P Sawkins (Unlicensed)
Last updated: 18, Mar, 2024 by Paul Sawkins
3 min read

Table of Contents

Overview

Every night we refresh the data into a complete copy of the live database.   So reporting via powerbi has the latest information as yesterdays information.

Once you have connected PowerBI to the reporting tables - these are the tables to consider.

Direct Access to Diagrams

There are diagrams in the database  to help understand the relationships - use the Microsoft free tool SSMS.  https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

To view the relationships install the standard SQL server management studio and connect to the reporting server.

vbi_xxxxx

Tables that begin with vbi_ have been created specifically for reporting.  This means that the information in them has been converted to readable form.  So if a supplier on a SKU is number 123 in the system - the vbi_ table will show the supplier name (rather than what is stored in the table - the number 123)

vbi_ tables do have relationships - use SQL server management studio (described above) to view the relationships.

Non vbi tables

Genie Enumerations > central table for all lookups in the system 

Stock Transaction > Order Headers (for lots of different types of orders)

Stock Trans Item > Order lines (for lots of different types of orders)

Journal Detail > All Journal Lines

PowerBI Diagrams

Here are some relationship diagrams that may help 

 Click here to expand...Stock Diagram

 Click here to expand... entities and relationships

 Click here to expand... core tables

 Click here to expand... Orders - sales, purchase, repair, etc

 Click here to expand...Journals

Journals

vbi_article - this is all stock items and is the Product table

  • COA_ID - chart of accounts GL account - will only have a value if not using the default values
  • MANUFACTURER_ID - link to business_entity to get supplier details
  • ITEMFORSALE_ID - link to item_for_sale (1:1 and service also links to item_for_sale)
  • SEARCH-CODE = SKU
  • ARTICLE_CATEGORY_ID  link to table article_category
  • STATUS - what is 10 / 20 / 99 ? internal numbers; there should be a StatusText column that is decoded
  • brandid - link to table item_brand
  • GSTModeText - are values always stored ex GST and this is just whether to add GST or not? 
  • Price_list (filtered by host company) is Average Cost table

vbi_itemforsale = Abstraction layer for goods (articles) vs services

vbi_salesorder

vbi_journal

 is the table that determines the nature of the transaction from the JournalTypeText field with journal_id being the related key field

  • JOURNAL_TYPE = Debtor invoice, CN, COGS etc
  • JOURNALBATCH_ID = link to journal_batch table
  • ORDER_ID = optional related order (Sales, Purchase, RA etc)

vbi_journal_line

    • Please explain this table relative to vbi_jourmal and vbi_journal_lines
    • COA_ID
    • debit / credit - only 1 of the 2 be populated per line
    • quantity = decorative, used for printing, not part of any calculation
    • jd_id = unique id for journal lines
    • status - 1,2 or 5 - what do these mean = Status of the line

vbi_journal_lines

    • Order_ID = optional related order (Sales, Purchase, RA etc)
    • control_type 
    • quantity = decorative, used for printing, not part of any calculation, often the number of items if it was a sale/purchase

vbi_purchaseorder

    • why a purchase table but no sales equivalent? 
    • Status - what do the codes mean ?
    • supplier_id = different to manufacturer_id  - link to business_entity to get supplier details
    • ORDER_ID =
    • SEARCH_CODE = purchase order search code
    • CONTACT = contact name on PO
    • log_surrogate_id ?

vbi_purchaseorderlines

    • LineStatus ? meaning of each number
    • XVALUE ?
    • PLAN_QUANTITY = quantity on the line sent to the supplier
 Clinics and Healthpoint Transactions

Clinic bookings and payments


Support Portal