Power BI with your accounting data

BIDates - Suntico BI Dates Table for Sage 50 Accounts

Posted by Hugh Johnson on 28-Jul-2018 15:22:00

Sage 50 Accounts OData feed for Power BI

This article describes the BIDates table in the Suntico Sage 50 Accounts OData feed for tools like Microsoft Power BI.  

This table is essential if you want to analyse your various Sage 50 Accounts data tables by date or period.

Read More

Topics: ODBC, Power BI Desktop, OData

BICategories - Sage 50 Accounts Nominal Categories

Posted by Hugh Johnson on 19-Apr-2018 15:07:06

Sage 50 Accounts OData feed for Power BI

This article describes the BICategories table that forms part of the Suntico Sage 50 Accounts OData feed for Power BI.  This table provides a list of all of the nominal categories for each chart of accounts that you have defined in Sage 50 Accounts.

This table is essential if you want to perform profit & loss or balance sheet analysis at the nominal account (nominal code) category level.

Read More

Topics: Reporting, Sage 50, ODBC, Accounting, Power BI Desktop, Power BI Pro, accountants, OData, power bi datasources, nominal records, nominal categories

BIPurchaseOrderLines - Sage 50 Accounts Purchase Orders Lines table

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

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.

Read More

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

BIPurchaseOrders - Sage 50 Accounts Purchase Orders table

Posted by Hugh Johnson on 19-Apr-2018 11:18:34

Sage 50 Accounts OData feed for Power BI

This article describes the BIPurchaseOrders 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 PURCHASE_ORDER ODBC table. 

This table is essential if you want to analyse your Sage 50 Purchase Orders.

Read More

Topics: ODBC, Power BI Desktop, OData, purchase orders

BISuppliers - Sage 50 Accounts Suppliers table

Posted by Hugh Johnson on 18-Apr-2018 21:57:11

Sage 50 Accounts OData feed for Power BI

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

This table is essential if you want to analyse your Sage 50 purchases by supplier or currency.

Read More

Topics: ODBC, Power BI Desktop, OData, suppliers, Sage 50 Accounts

BISalesOrderLines - Sage 50 Accounts Sales Order Lines table

Posted by Hugh Johnson on 18-Apr-2018 21:29:25

Sage 50 Accounts OData feed for Power BI

This article describes the BISalesOrderLines 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 SOP_ITEM ODBC table. 

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

Read More

Topics: ODBC, Power BI Desktop, OData, sales orders

BISalesOrders - Sage 50 Accounts Sales Orders table

Posted by Hugh Johnson on 18-Apr-2018 21:11:17

Sage 50 Accounts OData feed for Power BI

The Suntico BI OData feed turns your Sage 50 Accounts system into an online business intelligence data source for tools like Microsoft Power BI.  This article describes the BISalesOrders table.  This table is derived from a number of underlying Sage 50 Accounts SALES_ORDER ODBC table. 

This table is essential if you want to analyse your Sage 50 Sales Orders.

Read More

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

BIProductSales - Sage 50 Accounts Product Sales table

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

Sage 50 Accounts OData feed for Power BI

This article describes the BIProductSales table in the Suntico Sage 50 Accounts OData feed for tools like Microsoft Power BI.  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 the INVOICE and INVOICE_ITEM tables from Sage 50 with 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.

Read More

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

BICustomers - Sage 50 Accounts Customer Records

Posted by Hugh Johnson on 17-Apr-2018 15:11:36

Sage 50 Accounts OData feed for Power BI

This article describes the BICustomers table that forms part of the Suntico Sage 50 Accounts OData feed for Microsoft Power BI.  Use this table to add any customer reference information to your reports.  

Read More

Topics: Sage 50, ODBC, Power BI Desktop, OData, customers, sales ledger

BITransSplits - Sage 50 Accounts Audit Header & Transaction Splits

Posted by Hugh Johnson on 17-Apr-2018 11:39:19

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.

Read More

Topics: ODBC, Power BI Desktop, OData, Transaction Splits

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