QuickBooks

QuickBooks Reporting is Simple with QQube

Written by Charlie Russell

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 going to change because a product that I’m really excited about, QQube by Clearify.

Note: You may have seen my earlier review of this product, which was based on a beta test release. I’m pleased to say that QQube has been released to the public!.

QuickBooks Reporting Hassles

Intuit doesn’t make it easy for us to get reports out of QuickBooks 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.
  • 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.
  • 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 an excellent product but you need to be a programmer to understand how to use it. .
  • 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, and it isn’t available for Pro or Premier.
  • 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.

If you are a businessperson you just want to get your data out in a understandable form that you can manipulate the way you want. You don’t want to have to spend time figuring out how to just GET the data out, or working out complicated relations. You want the reports to be simple to retrieve and have the ability to easily adjust it to your needs.

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. This product really has me excited!

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 Summary, Sales, Purchases and Job Costing. You have “single purpose” subjects such as Open Sales Orders or Open Purchase Orders. There can be other kinds of subjects that are subsets of these, or combinations of these, such as Time Tracking, Vehicle Mileage 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.

QQube Subject List

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.

QQube Sales Detail

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.

QQube filter for item type

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.

QQube Job Cost

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.

QQube Pivot Table

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.

QQube Pivot Table

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.

image24

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.

QQube Pricing

The Single User Edition is available for $425.00. 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 QQube if you want to access new features (data fields, etc) that Intuit adds to QuickBooks.

If you want more than one concurrent user you can purchase the Multi-User 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 Add-on 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 them 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. Available now!

About the author

Charlie Russell

Charlie Russell has been involved with the small business software industry since the mid 70's, and remembers releasing his first commercial accounting software product when you had an 8-bit microcomputer with one 8 inch floppy disk drive. He has a special interest in inventory and manufacturing software for small businesses. Charlie is a Certified Advanced QuickBooks ProAdvisor with additional certifications for QuickBooks Online and QuickBooks Enterprise, as well as being a Xero Certified Partner. Charlie started blogging about QuickBooks in 2008 (Practical QuickBooks) and has been writing for the Accountex Report (formerly the Sleeter Report) since 2011. He retired from accounting and QuickBooks activities in early 2018.

Visit his CCRSoftware web site for information about his QuickBooks add-on products. He is also the author of the California Wildflower Hikes blog.

34 Comments

  • This looks fantastic – and you are right this is something that many of my clients are looking for. I am really enjoying this blog………………thank you for keeping us all informed.

  • This might be overkill for what two of my clients want to do. How would you go about getting them Monthly statements that include each invoice number and balance owed? One is a non-profit which can’t afford much more than free (of course!).
    tia
    Mindy

    • Mindy – are you talking about the info that you would see in the “Customer Balance Detail” report in QuickBooks?

      And when you say “getting them…” – are you managing their file as an accountant or consultant? You could get QQube yourself, and with their file you can set up a spreadsheet with their data, and then hand the spreadsheet over to them. They don’t need a copy of QQube. You get one yourself and use it to generate spreadsheets for your clients, if you have a copy of their file.

  • I need to be able to import an invoice and/or work order that we use for our auto repair biz.

    What can I use for this?

    Thanks very much.

    • Shannon, without going into a lot of technical detail – they buffer/cache the data. The first time you set it up, there is a lengthy time (depending on the size of your file) while it buffers things. After that, it is fast. NOTE that their buffering/reading of the data is faster than any other product that does this, from what I’ve seen (QODBC, Method, etc.). They use the SDK, so they have to live within those restrictions as we all do. As time goes on, if you use Enterprise, they will take advantage of the faster ODBC access that is found in Enterprise, as well.

  • Would QQube help a law firm to consolidate billing? I have a client that is unhappy with QB reporting because there’s no nice way to combine retainer info. and a/r balances together for each client onto a single statement.

    TIA!

  • Charlie,
    I am trying to link a cash management spreadsheet with open A/R and open A/P data from quickbooks. Currently we have to print the data then manually update the lists within the spreadsheet. I am working with the custom report writing to create the link, but cannot seem to find the right combination of tables to get what I want. Would QQube be a better option

  • This software looks like just what we need for more advanced inventory reporting. Do you know if it works with the Quickbooks UK – I can’t see anything on their website that confirms this?

  • I want a report that shows the unbilled time in dollars not just the hours. Will this software give me this kind of data?

  • If you have multiple entities set up as separate companies, could QQube help pull consolidated information across the various companies into one report?

  • Charlie, we are currently on QB Pro, 2015. We have a number of different company files (all with the same chart of accounts), and want to be able to see all the P & L’s on one sheet. It would be great to see all of them current month, and then YTD. Would this be the right software, maybe coming into Crystal Reports?

  • Hello Charlie, I’m using QQube with QB premier 2014 to generate reports. Is there a way to capture the date a sales order is created after it is closed to compare with the ship date? I need to calculate our order fulfillment times. I cannot find an order date for any SO that has been filled before running the report (I query QB every hour but some orders are filled as we get them).

    • You are better off asking the QQube folks about this, as they work with it a lot more than I do. I’m not clear, are you looking for the original date of creation, or are you looking for a date when the order is closed? The underlying QuickBooks SDK provides a creation date and a last date modified, but they don’t store a “closed” date.

      • Thank you for the reply. I am looking for the creation date of sales orders that are no longer open. I can only find creation dates for sales orders that still have an open quantity. I will check with QQube to see if they have a solution.

  • Does this work with Quickbooks Online Plus? We are looking to run an income statement broken down between R&D and G&A. Thank you.

  • Hi, its little out of context question. I have build a custom report using the QOBDC with crystal reports. But the problem is every time i have to open up a crystal report and refresh the data, i want a app from which i just select the reports range and TADA, like i have seen the batch invoice software you shown to us

  • Does QQube permit merging data from multiple QB “files” (old mainframe programmer talking here) Specifically, I need to link by sub-job, customer invoicing and A/R data budget info, invoice info (primarily summary info like last invoice amount, total invoiced to date, last customer payment date, total customer balance, total paid to date)

    A different report requirement would be to show information by vendor/job: invoice detail and payment information.

Leave a Comment