Reporting isn’t really the strong point in QuickBooks Desktop. While QuickBooks has advanced in other areas over the years, the capabilities of the reporting feature just aren’t very well developed. To address this, Intuit is releasing an Advanced Reporting feature for QuickBooks Enterprise Solutions. This is a very flexible but somewhat complicated to use reporting function that will copy your database into a separate data store and let you generate all kinds of wonderful reports and graphs.
Significantly, Advanced Reporting is included in QuickBooks Enterprise Solutions at no additional charge! This is surprising because the other “Advanced” features (Advanced Inventory, Advanced Pricing) have been separately priced subscription add-ons, and I thought that Intuit would do the same thing with Advanced Reporting. Nope – they surprised me. All you need to do is use a current version of QuickBooks Enterprise Solutions and maintain a current Full Service Plan, and you can get Advanced Reporting.
The Case for QuickBooks 2015 Advanced Reporting
Why do you need this? As I mentioned, the basic reporting feature in QuickBooks is very limited and under developed. Try generating a commission report that pulls information from multiple tables, such as the customer, invoices, and invoice payments. Try filtering an inventory stock status by item report by a value in a custom field. Heck, try making just about any significant change to any of the inventory reports! There are hundreds of examples of what you cannot do with the basic reporting capabilities of QuickBooks.
What could we do to get around this? Until now you could:
- Export your reports to Excel and modify them there. David Ringstrom has an excellent series of articles on how to work with QuickBooks reports and Excel, but there are limitations on what you can do with this approach.
- Extract data with ODBC and create your own reports. Enterprise actually has two different ODBC drivers, but these require programming skills and the QuickBooks data tables that they expose are poorly organized.
- Use an add-on product to generate reports such as QQube (my favorite reporting tool), which reorganizes the QuickBooks data into a more logical format than you see in ODBC, or perhaps something like Biztools Analytics.
- Hire a reporting expert (such as FreedUp dataOptics) who will use any or all of these tools to create a customized report for you, so that you don’t have to learn how to use these complicated tools.
Intuit decided that good reporting services are a basic need of an advanced product like QuickBooks Enterprise Solutions, so they developed Advanced Reporting and are including it as a feature – as long as you keep your full service plan subscription current.
A Quick Look at Advanced Reporting
Update 9/24/2014: Since this article was published, Intuit has released an update to the program. Some of the screens, particularly the list of reports, have been changed from what I show here.
This won’t be a detailed analysis of the product – that is something that is going to take a long time. I’ll just give you a quick peek.
You will find the program under the Reports menu.
The first time you select this option you have to be in multi-user mode. Then you will get this setup screen:
Advanced Reporting creates its own separate database, outside of the QuickBooks database itself. All of the data in your current file is going to be copied over to the new separate reporting database as a part of the initial setup. I had a fairly small test file, this took less than five minutes. I don’t know how long it is going to take for a very large QuickBooks company file.
Some implications of this approach:
- You will need more space on your file system to hold this duplicate copy of the data.
- This is a “snapshot” of your data – that means that any changes you make (transactions added, lists changed, etc.) after this point will not show up in the reporting system unless you do an additional “sync” with the reporting database.
- By extracting the data Intuit can reorganize it and rename it so that it makes more sense. The internal structure of the QuickBooks database, and the names for fields, is crazy (as users of the “custom reporting” ODBC feature know).
- By having the reporting data in a separate database you don’t impact the performance of QuickBooks itself (other than when syncing data), so you don’t slow down other QuickBooks users when generating complicated reports.
- Note that since this is a read-only sync you don’t have to worry about making a backup of this data.
- And, since it is in a separate database, it doesn’t increase the sized of your QBW file.
After installation (you don’t have to do anything further but wait, so this is very simple) you get the main window as shown below. There are two tabs, My Custom Reports will start off blank as that is where you get a list of any report definitions that you create and save. Report Templates shows a number of existing reports that Intuit has created for you.
Here’s the rest of the report list (as it stands now), including a “start from scratch” option.
You can modify an existing report or “roll your own”.
Working with Reports
Let’s take a look at the “inventory stock status by item” built in report to see what options we have. Clicking on the report name in the template list will open that report in an editor window.
I do find it a bit annoying that you can’t (as far as I’ve found so far) “scale” the report to fit in the window, so that I can see the entire report (or, at least the width of the report) without scrolling.