QuickBooks

QuickBooks Online Reports with QODBC

Cloud Database
Written by Charlie Russell

QuickBooks Online Reports with QODBCOne of the shortcomings that you hear when people discuss QuickBooks Online, and perhaps any of the online accounting products,  is the lack of flexible tools for creating custom reports. There are several special purpose tools available that can create excellent reports for the topics that they cover, but what if you want a report that covers information that these tools don’t provide? In this article I’ll take a look at a product that lets you get to a wide range of QuickBooks Online data, QODBC.

If you want to get down and dirty with the QuickBooks Online database and use some Windows desktop reporting tools that you may be familiar with, QODBC provides a driver that can do the job.

Databases, ODBC and QODBC

First, a little background discussion.

QuickBooks Online stores its data in a database that can only be accessed by reporting programs in one specific way – through the QuickBooks API. You have to be a programmer to use this API, and that isn’t something that most users or accounting professionals are going to try. It is complicated!

Every database has its own way of communicating with programs such as reporting tools, and there is a huge variation in how they work. It would be very difficult for a reporting tool to work with all the disparate database API’s  that exist. To help resolve this for Windows programs, Microsoft developed the ODBC specification to provide a standard database programming language that people can use to access all these databases in a standard way. An “ODBC driver” will be created as a translation layer between the database and the programming tool, so that the programmer doesn’t have to learn the intricacies of the specific programming interface for each product.

If you have an ODBC-compliant tool then you should be able to work with any database that has an ODBC driver that you can connect to. Some commonly used ODBC compliant tools are Microsoft Excel, Microsoft Access and Crystal Reports, and there are many others.

So, QODBC is an ODBC driver that works with QuickBooks. FLEXquarters has several versions, some that work with the desktop versions of QuickBooks as well as the new version for QuickBooks Online. The original desktop version was released by FLEXquarters in 2002. In 2003 Intuit decided to include a copy of the QODBC driver in QuickBooks Enterprise, and it is still included to this day. Note that the bundled product is a read-only version, you can purchase a read/write version from FLEXquarters. In addition, the purchased version can work with QuickBooks Pro, Premier or Enterprise.

FLEXquarters introduced the new  QODBC driver for QuickBooks Online in August of 2014. This product lets you connect any ODBC-compliant desktop reporting tool to your QuickBooks Online database.

Let’s take a quick look at how this works. This is not a tutorial, I’m just providing you with a quick overview.

Connecting QODBC with QuickBooks Online

Installation is fairly simple, you download and run an installer on your Windows computer. This will install a number of components in your system.

QODBC components

The complicated part is connecting your QODBC driver with your QuickBooks Online database. It took me a number of tries, and I found that I needed help from the FLEXquarters support staff. I did find them to be very responsive, and all of the issues did work out. I will note that I test a lot of software in my computer system, so it is fairly “dirty” with the remains of different products scattered about. I often get comments from software companies that say that they’ve not seen people have as much trouble with setup as I do (it’s easier to blame my computer than it is to blame myself).

A big part of the problem seemed to be with Intuit themselves. At one point I was getting errors that indicated that the QuickBooks API services from Intuit were not responding quickly enough, and the connection process timed out. This is something that I’ve heard from some software developers, that periodically there seem to be connection issues. In my case the problem eventually cleared up without my changing anything on my system, which leads me to believe that the issues were on Intuit’s side of things.

To connect QODBC with your QuickBooks Online company you start by running a desktop application. There are quite a few options here for fine-tuning the setup, but for basic use all you need to do here is to click the Test Connection to QuickBooks Online button.

QODBC Driver Setup

This opens a browser window that provides you with some instructions on what to do, and what to look out for. You need to allow popup windows, which is pointed out in the instructions. Once you have enabled this you get a popup window with the standard Intuit login window that is needed for all QuickBooks add-on applications. Enter your login credentials for your QuickBooks Online account.

QuickBooks Online authorization

The next step is another place where things might get tricky, and again this isn’t QODBC’s fault. You may find that you have multiple QuickBooks Online accounts associated with your login. I’m a QuickBooks ProAdvisor, and this is the screen that was presented to me.

QuickBooks Online authorization

This is strange – why did this particular set of companies show up?

  • My Company – when I log in to my QuickBooks Online Accountant account I see a “My Company” link (I’m still on the older QBOA) and that is what I use to access my company data. However, in THIS case, you do NOT select this option. It is an empty file. Why does it get listed here by Intuit?
  • CCR Sales and Service – this is where my company data resides, it is the one I want to pick.
  • parent – what is this and why does it show? Well, this is a company file that is normally hidden, it was created when I enrolled in the QuickBooks Online wholesale pricing program as I’ve talked about before, and it shouldn’t be listed.
  • Vandy International – a cancelled subscription, so why does it show up?

Keep in mind that this confusion is due to Intuit and how they handle this authorization feature, not the application that uses it.

Crazy. I wish Intuit would clean this up.

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.

20 Comments

  • I was excited seeing that monthly fee, assuming that FlexQuarters would be leveraging the v3 dataAPI, but it appears that it’s just using the standard non-subscription QBO v3 API which only provides the table views of data.

    Basically, when can we expect summary reports much like APIs present in the desktop QODBC sp_report/s? I heard that these are currently available through QBO’s REST interface.

    • Isaac, its not clear to me what you are trying to say here. At this time there is only one QBO V3 API for the accounting information (there is another for payment systems), and it does not involve a fee to the developer.

      • Last time I checked 4/5 months ago, the “Customer Account Data API” was the only way to run, not the flat-files/schemas, but reports like balance sheets, trial balances, etc..

        I may have been running from the wrong assumption… Apologies!

    • Issac, a number of reports (sp_report format) recently became available in QuickBooks online SDK, we will be adding all of them in the next month as well as newly arrived tables, we always support all data provided by Intuit products. Watch our news section or subscribe for updates! thanks.
      brad

        • issac – we now support all QuickBooks Online tables AND reports with the new version of QODBC for QuickBooks Online due for release next week but available for customers who request it immediately via our support board. thanks again!
          brad

    • Do you mean the desktop version of QuickBooks for Mac? If that is what you refer to, then, no, and it is unlikely that there ever will be. There is no third-party SDK or API (programming interface) for QuickBooks for Mac, so no add-on products can be created for that.

        • There isn’t a Mac product of any sort like this that I’m aware of, for ODBC drivers for desktop use. I wouldn’t expect much development in that area. Apps.intuit.com has several special purpose data exchange utilities, cloud based, that can be helpful sometimes.

        • russ – so you are asking if there is an ODBC connection for QuickBooks Online that can be used on an Apple computer – correct? the answer is yes but you have to load some tools to use it – namely Parallels or a similar program to allow Windows apps to run under Mac OS – or install Windows alongside MacOS to run on the same machine.
          details: http://www.howtogeek.com/187359/5-ways-to-run-windows-software-on-a-mac/
          As far as i’m aware, not many Mac apps support ODBC as a data source, so you will need to use Windows app for that purpose but they can run on a mac with the tools listed above. We have been thinking of a mac/linux client for QuickBooks Online because of it’s flexibility but don’t know how the market will be, our main focus is supporting Linux servers but Mac would work with that version as well. hope that helps – thanks!
          brad
          qodbc.com

          • Thanks, Brad! I would love to know if you do develop something. I’ll visit your site and leave my contact info if I can. I’ve been looking into the Parallels/Windows option. While it isn’t a huge expense, it adds up if you have multiple people who need to pull a few simple reports that QB Online doesn’t offer. Mainly, the benefit we’re looking for is the ability to group time activities by more options like Month, then Employee, then Customer. The ability to edit/organize report formats would be nice as well. There is a QBO table available to developers that would provide this information, but I don’t have the programming expertise to draw the data down from the server. If I could get it to a local database, I could manipulate it with queries, etc.

  • I stumbled across another great article by you in an effort to determine how I could mass update a bunch of transaction dates in a credit card account in QBO. Wondering how easily this can be done. In another life, I used to program in .NET against MS SQL Server, so I have a pretty technical background. Looking for the easiest way to accomplish that, any suggestions? Doesn’t seem like q2q or Bay State has utilities for that like for QBD.

    • Baystate Consulting has an import utility for QuickBooks Online. Programmatically, you can use QODBC, or try the QuickBooks API if you wish. There may be other options, I haven’t explored them in detail. Intuit is just now starting to open up more of the QBO product to outside vendors, so it is taking awhile for things to settle out.

      • Hi Charlie, Thanks for your reply, but your response doesn’t seem to match my question, I need to change the dates of a lot of existing transactions in QBO in a credit card account. Do you know of any utilities that could accomplish that easily? If not,l wonder how easily in can be accomplished in QODBC (if the update SQL command is accepted)? Any thoughts.

        • You would have to check with the QODBC folks on that. When you say “in a credit card account”, if you mean the credit card expiration date (as opposed to the transaction date of a purchase using a credit card) you may have an issue, as credit card information is harder to deal with due to security.

          I don’t work with QODBC enough to have all the details at hand, and their website has a wealth of detailed information.

Leave a Comment