Power BI with your accounting data

BIPurchaseOrderLines - Sage 50 Accounts Purchase Orders Lines table

Posted by Hugh Johnson on 19-Apr-2018 14:15:41
Hugh Johnson
Find me on:

Sage 50 Accounts OData feed for Power BI

This article describes the BIPurchaseOrderLines table in the Suntico Sage 50 Accounts OData feed for tools like Microsoft Power BI.   This table is derived from the underlying Sage 50 Accounts POP_ITEM ODBC table. 

This table is essential if you want to analyse your Sage 50 Purchase Orders at the product line item level.

BIPurchaseOrderLines table

 

 
Column Type Description
PurchaseOrderItemID Text Unique internal ID for the record
PurchaseOrderID Text Unique ID to link to BIPurchaseOrders table
NetAmount Decimal Number Net purchase amount for this line item.  This is the [UnitPrice] * [QtyOrder]
QtyAllocated Decimal Number Quantity placed on order with the supplier but not yet delivered for this line item.  Note that if the Purchase Order has not yet been placed with the Supplier then the QtyAllocated will be zero.  Also if the goods have been received in full against the Purchase Order then the QtyAllocated will also be zero.
QtyDelivered Decimal Number Quantity delivered against this line
QtyDespatch Decimal Number Quantity to form next despatch from the supplier against this line item.  This will typically be the same as QtyAllocated
QtyOrder Decimal Number Total quantity in the PO for this line
UnitPrice Decimal Number Unit cost price on the PO
StockID Whole Number Link to the BIStocks table

BIPurchaseOrderLines relationships

The BISalesOrderLines table in the OData feed is related to the BIStocks (Products) table via the [StockID] and the BIPurchaseOrders table via the [PurchaseOrderID]. 

BIPurchaseOrderLines relationships Sage 50 Accounts

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

Create Suntico BI Trial Account 

 

Topics: ODBC, Power BI Desktop, OData, purchase orders, Sage 50 Accounts

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