QuickBooks' built-in reports are fine until you want something they do not offer: sales by customer across twelve months on one sheet, margin by product line next to web-shop traffic, or one dashboard across three company files. The usual workaround, exporting to Excel, works once and is stale by the next morning.
The durable pattern is QuickBooks → database → Excel or Power BI. A sync keeps a MySQL or SQL Server copy of the books current; Excel and Power BI connect to that copy and refresh themselves. This guide is the Excel and Power BI half. For the sync half see How to sync QuickBooks with MySQL or QuickBooks to SQL Server.
Why not report straight out of QuickBooks?
- Reports are fixed shapes. You can filter and add columns, not join to anything outside QuickBooks.
- One period at a time. Trend reports mean running the same report repeatedly and pasting.
- One company at a time. Consolidation is manual.
- Exports go stale. Every Excel file is a snapshot someone has to redo.
- Running a heavy report slows the people using QuickBooks while it runs.
Connecting Excel
- Install the database's connector on the PC: MySQL Connector/ODBC for MySQL, or nothing extra for SQL Server, which Excel talks to natively.
- Data → Get Data → From Database → From MySQL Database or From SQL Server Database. Enter the server and a read-only login.
- Pick the tables (or paste a SQL query) and load to a table or the data model.
- Data → Refresh All pulls current figures. Set Refresh data when opening the file in the connection properties so the workbook is current every time it opens.
For anything more than a flat table, load into the Power Pivot data model instead of a worksheet. It handles millions of rows, lets you relate invoice_lines to customers and items, and gives you DAX measures that Excel formulas cannot express.
Connecting Power BI
- In Power BI Desktop: Get Data → MySQL database (needs MySQL Connector/NET installed) or SQL Server database. Import mode is the right default for accounting data; DirectQuery is for very large or very volatile sources.
- Load the tables, set relationships (lines → invoices → customers; lines → items), and add a date table.
- Publish to the Power BI service.
- Install the on-premises data gateway on a machine that can reach the database, and schedule refresh (up to eight times a day on Pro). The gateway is the step people forget; without it the published report cannot reach a database on your network.
A starter data model
Keep it a simple star: facts in the middle, dimensions around them.
| Table | Role | Key columns |
|---|---|---|
invoice_lines | Fact: one row per line sold | invoice id, item id, quantity, amount, cost |
invoices | Fact: one row per invoice | customer id, txn date, due date, total, balance |
payments | Fact: cash received | customer id, date, amount |
customers | Dimension | id, name, type, sales rep, terms |
items | Dimension | id, name, type, income account, cost |
accounts | Dimension | id, name, type |
dates | Dimension (build it) | date, month, quarter, fiscal year |
The date table is not optional. Fiscal years that do not start in January, month-over-month comparisons and year-to-date all depend on it.
The first three measures
In DAX, assuming the model above:
Sales = SUM ( invoice_lines[amount] )
Sales LY = CALCULATE ( [Sales], SAMEPERIODLASTYEAR ( dates[date] ) )
Gross Margin % = DIVIDE ( [Sales] - SUM ( invoice_lines[cost] ), [Sales] )Sales by month with last year alongside, receivables aging from invoices[balance] bucketed by days past due, and margin by item are the three reports most businesses ask for first, and all three fall out of this model in minutes once the data is in a database.
Practicalities
- Read-only login. Create a database user that can only
SELECT. Reporting tools should never be able to change the copy. - Several companies. Either one database per company file and a combined view, or a
companycolumn on every table. Power BI handles either; decide before the first load. - Refresh timing. Schedule the report refresh after the sync, not before, or the dashboard is always one run behind.
- Reconcile once. Check a month's sales total in the dashboard against QuickBooks' own Profit & Loss before anyone relies on it. If they match, every later question is about the report logic, not the data.
If the database side does not exist yet, QBSync keeps a MySQL or SQL Server copy of a QuickBooks Desktop file current on a schedule, and the free demo is enough to build the first dashboard against your own numbers.
