Power BI with your accounting data

BIProductSales - Sage 50 Accounts Sales Invoices table

Posted by Hugh Johnson on 18-Apr-2018 17:28:56
Hugh Johnson
Find me on:

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

 

Column Type Description
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

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.  

 BIProductSales table relationships

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, sales invoices, product sales, sales orders

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