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.
|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|
|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|
The BITransSplits table in the OData feed has relationships with a number of other tables as shown below.
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?