Your financial data is crucial to your business. Getting timely reports from that data is imperative. QuickBooks includes many useful reports, BUT every business has some unique report that is needed that QuickBooks doesn’t include. Intuit recognizes this, and if you have QuickBooks Enterprise v 11.0, you have a new tool that can help.
Up until now your reporting options have been:
- Built in QuickBooks reports: Fast, but not always flexible enough to do what you need.
- Export your data via the IIF format: Limited information is available, the format is very obtuse and hard to use.
- Use an SDK based reporting product: The SDK is very slow in some cases, and cannot access all data in the file. There are many available, including the QODBC driver (www.qodbc.com) and XpandedReports.
- Use an application based on the IPP interface from the Intuit Workplace: At this time these are very limited as this is a new interface, and because not all QuickBooks data is currently available this way.
Each of these have their advantages and disadvantages, and many users have been asking for better reporting options.
QuickBooks Enterprise V11 adds another option to the mix – the Custom Reporting feature. Without getting into the detailed technical information, this is another ODBC access tool, but it doesn’t use any of the prior methods of accessing data. It accesses the QuickBooks SQL database directly. There are two major advantages here:
- Data access is very fast. You aren’t going through an online synchronization or slow SDK based data translation – you are getting through to the QuickBooks database manager directly.
- Other methods (SDK and IPP) hide many of the data tables and so you cannot get to every piece of data that you want. This tool lets you get to data that you cannot access by other methods. However, a qualification here – this new method does NOT let you get to ALL data at this time. It has the potential of being better, and it already includes some data you can’t get from other sources.
From what I’ve seen so far, this tool is in its infancy and will only be useful (initially) by people who are very technically savvy. If you are a software developer, this is the start of something great. If you are a business user of QuickBooks, this might not be all that useful to you (at this time).
I’ll start my overview (this is NOT a tutorial) by looking at how you can connect to the database, and then talk about some of the ramifications and restrictions of the tool.
Accessing this Tool
Look for the Custom Reporting option in the Reports menu. It is also found in File / Utilities / Custom Reporting. You will not find it in the Report Center.
The first time that you use the tool you will need to set up an ODBC “user”. This process differs from what you have done in the past if you used the QODBC driver that we had access to.
Create a user account with a password.
Next, you will create an ODBC connection. QuickBooks provides detailed information on this in the help file. I will not be going through each step, I’m just providing an overview.
QuickBooks has established a DSN file for you. You can access this from an ODBC compliant reporting tool (I’ll use Microsoft Access as an example). Log in using the user name and password that you set up above. Note that it shows that you are connecting to SQL Anywhere – that is the back end SQL database system that Intuit has been using since the 2006 release. Software developers and ProAdvisors have been asking for this kind of access for a long time!
When you have access to the database you will see a very long and imposing list of “tables”. Many of these will not be useful to you.
Once you make a connection to the proper table you will see this information in your application. This is a portion of a customer list, for example.
What Does This Mean To Us?
In an interview with a senior Intuit Product Manager, it is clear that the intent here is to provide fast access to as much of your QuickBooks data as is possible for reporting purposes. This is not a programming tool like the SDK – you won’t be able to update your QuickBooks data. Will you be able to in the future? Intuit representatives won’t say, but the door hasn’t been entirely closed. I don’t expect it to happen soon, though.
This is the first release of the product and you have to be a very technically oriented person to be able to use it. This isn’t a simple query tool that is oriented towards the casual user. I had my propeller beanie on when trying this, and I still had a lot of trouble getting things set up. It is tricky.
However, that is OK at this point. Intuit admits that this is going to be a very technical tool at first. As time goes on and they get feedback on what people want, the tool will evolve. I also wouldn’t be surprised to see some add-on developers coming up with tools that work on top of this to provide you with a higher level of access.
You aren’t seeing the internal tables in their “raw” format, instead you are seeing “views” that take the information and reformulate the information a bit. Not far yet, but a bit. The views are not well connected – you will have to “join” the various tables together in your ODBC tool to get meaningful reports in most cases. It is a lot of hard work to use this – but you will find that the data access speed is greatly improved over all other methods.
Initially you will not have access to all of the data in the company file. There are some things that will remain difficult to access – payroll data and credit card information for example. But there are things that you CAN get to here that you cannot get to by other means – budget information for example.
This is clearly a first round of the product, the foundation for future development. Intuit is very interested in getting feedback on what we would like to see added and improved.
It is a good start, I hope that they continue to develop this.
After looking at this for a short while, I have a number of questions:
- Will this tool ever become available to users of Pro and Premier? Users of these products need good data access just as much as Enterprise users. Limiting this to Enterprise makes the tool less interesting to many software developers.
- Will there be “higher level” views that will aggregate different kinds of data into simpler views? There are some things that you can’t get just from table views. For example – what is the quantity on hand of my inventory items on a specific date?
- Will we eventually get the ability to update data in QuickBooks with this tool? Direct access to the database is coveted by all developers, we really need that feature.
- How does this relate to the development of the QuickBooks SDK and IPP interfaces? It seems to me that we have several different competing data access methods being developed by Intuit. Will they all continue in the future? Will some survive and others not? There is a lot of concern over this. From what I understand, this new method is totally independent from the SDK and IPP interfaces:
- Until recently the SDK has been the only way to update data in QuickBooks reliably. Intuit has been very slow to update this and fix bugs recently, and many SDK programmers fear that this will be frozen in the same way that the older IIF interface was.
- The IPP interface is very complicated to use and works only through the Internet. Many users don’t want to have their data “in the cloud”, preferring to keep it on the desktop.
It is going to be very interesting to see how this develops in the future.
Many thanks to:
- Rich Walker of Intuit, for putting up with me through this process – as well as many other Intuit contacts who have answered my incessant questions.
- Chuck Vigeant of CLEARIFY for his time, and his wonderful article in the Sleeter Group Newsletter.
- Donovan Sachs of Alembic Computer Services, Inc for his blog article on Custom Reporting and QuickBooks Enterprise 2011.
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.
Sites That Link to this Post
- QuickBooks Reporting is Simple with QQube : QuickBooks and Beyond | September 29, 2011
- Truly Refreshable Excel Reports for QuickBooks : QuickBooks and Beyond | June 6, 2012