by Hugh Johnson, Posted on 4/18/2018 3:25:22 PM
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.
|SalesID||Text||Unique ID for that record|
|DateID||Text||Internal reference to link to BIDates|
|Invoice_Number||Whole Number||Invoice # from Sage 50|
|Quantity||Decimal Number||Quantity sold|
|NetAmount||Decimal Number||Net sales amount for that line - including line-item discounts but excluding discounts applied to the Sales Invoice header.|
|Date||Date/Time||Sales invoice date|
|ExchangeRate||Decimal Number||Exchange rate for the sales invoice|
|InvoiceType||Text||Sales Invoice or Sales Credit|
|IsPosted||True/False||Invoice is posted to the ledgers|
|OrderNumber||Text||Related Sales Order number|
|TakenBy||Text||From Sage 50 Sales Invoice header|
|StockID||Whole Number||Unique ID to link to the BIStocks table|
|CustomerID||Text||Unique ID to linke to the BICustomers table|
|Alpha3||Text||Alpha-3 country for the delivery address|
|Address||Text||Concatenated delivery address|
|Sales||Decimal Number||Net line item sales amount after all discounts|
|DiscountFromList||Decimal Number||Net sales line discount from list price|
|OrderDate||Date/Time||Date of related Sales Order|
|FromSO||True/False||Invoice originates from a Sales Order|
|OrdertoInvoiceDays||Whole Number||# of days from order to invoice|
|UnitCost||Decimal Number||Last unit cost price at the time of the sale|
|Margin||Decimal Number||Sales line gross margin|
|ExtendedCost||Decimal Number||UnitCost x Quantity|
|Currency_Code||Text||3-alpha original currency code for the line|
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?