Power BI with your accounting data

BITransSplits - Sage 50 Accounts Audit Header & Transaction Splits

Posted by Hugh Johnson on 17-Apr-2018 11:39:19
Hugh Johnson
Find me on:

Sage 50 Accounts OData feed for Power BI

This article describes the BITransSplits table in the Suntico Sage 50 Accounts OData feed for tools like Microsoft Power BI.  The table is a flattened combnation of the Sage 50 Accounts ODBC tables AUDIT_HEADER and TRANS_SPLITS.

This table provides all of the transaction splits of of all of your posted Sage 50 Accounts transactions going back over the last c. 800 days.  Excluded are any deleted transactions.

This table is essential if you want to analyse any of the following Sage 50 Accounts data:

  • Accounts payable
  • Accounts receivable
  • Nominal transactions
  • Purchase Invoices
  • Departmental Profit & Loss
  • Imported Sales transactions (via CSV)*
  • Sales transactions created via the Batch Input screens in Sage 50 Accounts*

*Sales transactions that are imported into Sage 50 Accounts via CSV file, or created through the Batch Invoices screen go straight to the Transactions audit log and do not appear in the Sage 50 Accounts Invoices list.

 

BITransSplits table

 

Column Type Description
NominalRecordID Text Key to BINominalRecords table
SupplierID Text Key to BISuppliers table
CustomerID Text Key to BICustomers table
AccountRef Text Account for the other side of the transaction
Type Text Sage 50 Transaction Type
Date Date/Time Transaction date
InvRef Text Reference (e.g. Invoice #)
Details Text Transaction line description
ExtraRef Text Extra reference field from Sage
NetAmount Decimal Net amount of the transaction line
GrossAmount Decimal Gross amount of the transaction line
Outstanding Decimal Outstanding amount of the transaction line
ForeignRate Decimal Exchange rate of the transaction line
TranSplitID Text Unique identifier for the record
DepartmentName Text Name of the Sage 50 Department
DeptNumber Text Number of the Sage 50 Department
TranNumber Whole Number Transaction Split number from Sage
DueDate Date/Time Due Date for the transaction

BITransSplits relationships

The BITransSplits table in the OData feed has relationships with a number of other tables as shown below.

BITransSplits table relationships

Note: the Dates table is not part of the data feed, but is easily created within your model.

To try our this OData feed for your Sage 50 Accounts data, why not create your own Suntico BI trial account?

Use free for 30 days 

 

Topics: ODBC, Power BI Desktop, OData, Transaction Splits

Power BI with your accounting data

Time-saving tips, tricks, templates and tutorials

Subscribe to this blog for practical shortcuts to driving real insights from your accounting data with Power BI.

Expect:

  • How-to videos
  • Example reports
  • Sample formulae
  • Data modeling tips
  • Free Power BI templates
  • Tips for optimising Sage 50cloud

Subscribe to Email Updates

Recent Posts