Getting useful reports from QuickBooks, in the format that you want, can be a real chore. Your options are to use the built-in QuickBooks reports, struggle with a complicated data access tool, or use a complicated add-on product that has limitations. This is all about to change with the release of a product that I’m really excited about, QQube by Clearify.
QuickBooks Reporting Hassles
Intuit doesn’t make it easy for us to get reports the way we want them.
- You have the built in reports, which provide some limited customization options. There isn’t a lot of flexibility here. Some reports cannot be modified at all (such as most inventory reports), and if you want a combination that they haven’t already included, you are out of luck.
- You can use the QuickBooks SDK (software development kit) to access data, but this is a very complicated option even if you are an expert programmer. The information is presented in a raw format that is poorly organized.
- There are data extraction tools built on top of the SDK that can help, but again these tend to be complicated to use and often are very slow. The ODBC driver (www.qodbc.com) is an example of this – it is complicated to set up and you need to be a programmer to understand how to use it. Data is still presented in the poorly organized format that Intuit works with.
- Intuit recently added the Custom Reporting feature in Enterprise, but at this time it is extremely complicated to work with and very poorly documented. This is still a programmer’s tool.
- There are a number of higher level reporting tools available that can help. One example that I’ve reviewed is XpandedReports. This is a more user friendly tool, but it isn’t the right tool for everyone. When I reviewed it (there have been updates since then) it was slow, and the data was still organized in the way that QuickBooks presents it.
QQube Solves These Problems!
QQube (from Clearify) is a new reporting product that goes a long way towards resolving all of these issues. It extracts the data from QuickBooks and reorganizes it into logical groupings that are very easy to understand and manipulate. You don’t have to be an expert in the internal data structure of QuickBooks to understand how to get to your data.
Please note that this product has not been released to the market at the time that I’m writing this article. Look for it in February 2011, tentatively). My review is based on a beta-test copy. I don’t usually talk about pre-release products, but this one really has me excited.
I work with the QuickBooks database every day, and understanding it can be a struggle. It isn’t organized well, and making connections between the various portions can often be difficult. When you use the programming SDK, the ODBC driver or one of the reporting tools, you spend a huge amount of time just trying to figure out where the data is located, how it is organized, and how you can get it out in a useable form. For most end users these tools are obtuse and complicated to learn. Most business people want the reports to be simple to build, and often they need the information in Excel so that they can work on refining the reports.
QQube takes care of the tough part for you. It handles all of the complicated connections with your company file, and extracts the raw data. It is analyzed and rearranged into a separate database. You then can access this data from Excel or tools like Crystal Reports.
Rather than seeing raw tables of data, QQube organizes it using modern “data warehouse” concepts. The data is formatted into logical areas of information of various types, called “subjects”. You have broad subjects such as Financial, Sales, Purchases and Job Cost. You have “single purpose” subjects such as Open Accounts Payable or Open Purchase Orders. There can be other kinds of subjects that are subsets of these, or combinations of these, such as Time Tracking, Item Forecasting, Sales Tax and more.
Every piece of data that can be extracted from QuickBooks is presented here. QQube uses the QuickBooks SDK to pull out data. Some data isn’t found in the tables in the SDK, but is available through reports – QQube will extract this data from the QuickBooks reports and will include it where needed. In future versions, if you have Enterprise 11 or later, QQube will also use the new custom reporting feature to obtain any data that is available from that format that isn’t available through the SDK or reports.
Let’s take a look at a few examples, using Excel. I’ll use Enterprise 11 and Office 2010, and one of the QuickBooks sample company files. Please note that this is NOT a tutorial article, I’m just going to give you a brief overview so that you can get an idea of how it works.
QQube shows as an add-in in Excel. Click on it and you will see a list of the subjects that are available in the left column.
I’ll pick the Sales option, and then pick the Sales Detail Default Report, which is a simple list of your sales information. QQube presents you with a list of the fields that are available to include in your spreadsheet, and adds a number as columns by default. You can add new columns simply by placing checks next to the fields that you want to use.
If you are familiar with the QuickBooks database you should notice right away that there are fields available here that you don’t get directly from QuickBooks. For example, the SalesTxn Line Sales Profit Margin field. Profit margins for individual lines of an invoice can be tough to get out of QuickBooks through most reporting tools. QQube has a large number of these calculated fields that are put in places where you can easily find them. This is an excellent example of how QQube can analyze your data to present you with information that may be difficult to locate or calculate yourself.
Adding fields as columns in the report is as simple as putting a check mark next to the field.
Since we are working with Excel, we have all of the normal Excel features that we are used to using. You can click on column headings and set filters, for example. It is easy to filter for must inventory item parts in the Item Type column.
Another great example of the power found here is with the Job Costing subject. If you want to generate job costing reports from QuickBooks you have to pull information from many different tables, linking transactions of many types. It is a very time consuming process, and many people avoid doing this with any reporting tools. QQube does this integration for you.
Powerful Pivot Tables
Up to this point I’ve been showing basic list reports that are available. QQube also provides a powerful set of Pivot Table tools. If you love Excel and Pivot Tables, QQube is the tool for you. You can simply drag and drop the fields into the proper locations, and QQube will build the pivot table. Many different sample pivot tables are included to give you a starting point, or you can create one from scratch yourself.
In this example, I clicked on the convert to Pivot Table button in the Sales Detail report.
Now you can drag and drop fields into the various areas in the lower right to add them to your pivot table. I’ve quickly created a report where you can drill down to see the quantity of each item sold to each customer, simply by dragging the customer name and item name fields to the Row Labels area, and then the Sales Txn Line Sales Quantity field to the Values area. You can easily add filters for fields like the Sales Rep, or item type, and more.
Keep in mind that you don’t have to worry about how the data gets here. No complicated ODBC connection tools, no worrying about how the various tables relate to each other. QQube lets you focus on creating the report, rather than how to extract the data.
I don’t have space here to go into all of the details of how the product works. In just a few short days I’ve been able to use this tool to build reports that would take me days to build with the SDK or ODBC. In fact, I’ve built some reports that I wouldn’t have even attempted to build with the SDK. I am really excited about the possibilities that this product presents.
Other Exciting Features
One of the most significant features will be the ability to combine data from multiple QuickBooks company files. You will have to pay an additional fee for this ability (as explained below). With this option, all data from multiple files will be combined into one database. You can use the Company field to filter the data to see information from just one company. This feature will work with Pro, Premier and Enterprise.
Once you set up a template in Excel, you can send that template to other QQube users to share your report design.
Perhaps more interesting – you can send your spreadsheet to other people including the data, so that others can examine the data using your Excel spreadsheet even if they don’t have QuickBooks installed.
Please note that this is preliminary information and it may change when the product is released.
Retail pricing will be $425.00 for a Single User Edition. This is a one-time fee, not an annual subscription. It is my understanding that when a new QuickBooks upgrade comes out (such as the 2012 release) you won’t have to buy an upgrade to QQube to be able to continue using the reports you have created – you only have to upgrade to get access to new features (data fields, etc) that Intuit adds to QuickBooks.
If you want more than one concurrent user you can purchase the Server Edition for $795.00, which provides you with two users. Additional users can be added for $165.00 per user.
If you want the ability to combine multiple company files into one set of data, you will need to purchase the Multi-Company Addon for $195.00.
A Product From People We Know and Trust
QQube (from Clearify) works with QuickBooks Pro, Premier and Enterprise, 2008 and later, US editions only.
This is the brainchild of Chuck Vigeant and the folks at Clearify. Chuck has been working on QuickBooks reporting and data extraction for longer than anyone in the industry that I’ve met. I don’t now of anyone outside of Intuit who understands the QuickBooks database structure better than he does – and I suspect he knows it better than most people INSIDE of Intuit as well. He was instrumental in developing the first ODBC driver that worked with QuickBooks, has taught countless ODBC classes to ProAdvisors and other accounting professionals, and has been heavily involved with Intuit on numerous projects.
QQube is the result of Chuck’s vast experience with QuickBooks database management, Data Warehousing expertise, and his vision of what users need. In an interview he said “I want people to be able to get to their data without having to be a rocket scientist”.
If you are frustrated with trying to build reports from your QuickBooks data, I highly recommend that you try QQube when it becomes available.
About the Author (Author Profile)
Charlie Russell is the founder of CCRSoftware. He’s been involved with the small business software industry since the mid 70’s, focusing on inventory and accounting software for small businesses. Charlie is a Certified Advanced QuickBooks ProAdvisor. Look for Charlie’s articles in the Accountex Report blog, as well as his California Wildflower Hikes blog.