BIProductSales - Sage 50 Accounts Sales Invoices table

by Hugh Johnson, Posted on 4/18/2018 3:25:22 PM

 ODBC, Power BI Desktop, OData, sales invoices, product sales, sales orders

BIProductSales - Sage 50 Accounts Sales Invoices table

Sage 50 Accounts OData feed for Power BI

This article describes the BIProductSales table in the Suntico Sage 50 Accounts OData feed in the Suntico Power BI Sage 50 Accounts integration.  This table is derived from a number of underlying Sage 50 Accounts ODBC tables and provides details of all of the sales transactions created through the Sales Orders or Invoices & Credits modules in Sage 50.  It is essentially Sage 50 Accounts Sales Invoices with all the line item information and some further enrichments, for example to include gross margin of a transaction line item.

This table is essential if you want to analyse sales and margin by product code over time.

BIProductSales table

SalesIDTextUnique ID for that record
DateIDTextInternal reference to link to BIDates
Invoice_NumberWhole NumberInvoice # from Sage 50
QuantityDecimal NumberQuantity sold
NetAmountDecimal NumberNet sales amount for that line - including line-item discounts but excluding discounts applied to the Sales Invoice header.
DateDate/TimeSales invoice date
ExchangeRateDecimal NumberExchange rate for the sales invoice
InvoiceTypeTextSales Invoice or Sales Credit
IsPostedTrue/FalseInvoice is posted to the ledgers
OrderNumberTextRelated Sales Order number
TakenByTextFrom Sage 50 Sales Invoice header
StockIDWhole NumberUnique ID to link to the BIStocks table
CustomerIDTextUnique ID to linke to the BICustomers table
Alpha3TextAlpha-3 country for the delivery address
AddressTextConcatenated delivery address
SalesDecimal NumberNet line item sales amount after all discounts
DiscountFromListDecimal NumberNet sales line discount from list price
OrderDateDate/TimeDate of related Sales Order
FromSOTrue/FalseInvoice originates from a Sales Order
OrdertoInvoiceDaysWhole Number# of days from order to invoice
UnitCostDecimal NumberLast unit cost price at the time of the sale
MarginDecimal NumberSales line gross margin
ExtendedCostDecimal NumberUnitCost x Quantity
Currency_CodeText3-alpha original currency code for the line

BIProductSales relationships

The BIProductSales table in the OData feed is related to the BICustomers table via the [CustomerID} and the BIStocks (products) table via the [StockID].  The Dates table shown in the diagram below is not part of the OData feed, but I would recommend that you create one and link it to the BIProductSales table.  

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



Copyright © 2019,
Accounting Insights Ltd.
All Rights Reserved.

Our Office :
Unit 4, Roof Garden Offices,
Clarehall Shopping Centre,

+353 1 567 4069

[email protected]