Program Updates QuickBooks 2011

QuickBooks 2011: Enterprise Custom Reporting

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.

image

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.

SNAGHTML1f46de70

Create a user account with a password.

SNAGHTML1f48b177

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.

image

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!

SNAGHTML1f4f4280

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.

SNAGHTML1f58d9a0

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.

image

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.

Questions

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:
    1. 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.
    2. 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.
  • Donovan Sachs of Alembic Computer Services, Inc for his blog article on Custom Reporting and QuickBooks Enterprise 2011.
Share This Article

20 Comments

  • Great article! I tried this out yesterday, after reading Chuck’s article in the Sleeter newsletter.

    I hope to see more data views to minimize the relational database skills needed.

    Maybe having “different competing data access methods” will push each team to improve their tools. Competition is good, right?

  • Thank you, Brian. Competition is great, even if it is between teams in their own company.

    I’m disappointed that this will only be an Enterprise feature.

    I’d be ecstatic if they eventually moved this to be a read/write application in some way…

  • Hi Charlie:

    I agree with you about this tool not being available to the Pro and Premier users. Not being available to those versions of Quickbooks fragments the quickbooks brand.

    Thanks for the information about Quickbooks 2011!

    Bruce

  • Bruce, they have many cases of offering better features in Enterprise than in Pro and Premier, so this isn’t surprising. They do have to justify the higher cost of the product. So it is already fragmented quite a bit – but this one really affects things because any report writing companies or consultants can’t offer their solutions to Pro and Premier installations if they base things on this feature.

  • Charlie

    Excellent article. Our company is also extremely excited about this new Custom Reporting feature, which we have been asking for since 2003. Speed of reading large tables of data via the SDK interface continues to be the number one problem everyone has running SDK products such as our QODBC Driver.

    Our driver will continue to be bundled with the QuickBooks Enterprise 2011 edition, so there will be no need for changes to existing code, and we will of course be integrating the new Custom Reporting tables into the driver to gain the speed this new feature offers, as well as merging those tables in with the SDK based tables and reports to compile the most complete set of QuickBooks data available from any source. We will also be correcting some data field formatting and adding missing data to tables to make this new feature even easier to use.

    We have always felt that the SDK was best suited for writing data into QuickBooks because of the many internal checks and balances, whereas reading should be done directly from the database system, so now our dream structure has been made complete. We agree with you that we hope this feature is added to all versions of QuickBooks so that speed no longer becomes a problem factor in keeping people from using their own accounting data in any way they wish to.

    Brad Waddell
    FLEXquarters.com Limited

  • Thank you, Brad. I didn’t have the time to talk to you or anyone at your company prior to writing this, and I’m very happy that you checked in here.

    Looking at the early editions of the new feature, the most obvious thing that came to mind is that we needed a higher level of organization such as what you provide in QODBC. If you are going to integrate your product with this new feature, that will help immensely.

    My concerns would be the difference between Enterprise and Pro/Premier (as stated) – and that you are going to have some read-only tables since the new method provides access to data that the SDK can’t write back.

    I’m also going to be interested to see what omissions the new feature has – there are fields we can’t get through the SDK (and therefore your product), will we get them in the new product? And then, does that make some read only FIELDS within a data record?

    It could be complex…

  • Charlie,

    There have been read-only fields in records since the beginning of the SDK (listid for example), we just leave them out of the transaction, so no code changes should be required.

    I want to merge the new data with the existing QODBC formatted SDK data and let the user decide if they need fast mode or complete mode, meaning only fields available will be showing unless they want us to back-fill the missing ones from the SDK database. There are many items missing in the new reporting interface that will never be there because they are calculated by the user interface (which the SDK uses) and are not in the database, like the predefined reports and isPaid flag and such.

    If the customer has Enterprise, they can turn on the new SQL mode, if not, they cannot, either way their programs will work the same, the customer decides if they need that Enterprise speed feature or not. The developer need not be concerned. It’s much better than the days where we had no choice in terms of speed, now at least we have one!

    Complex is what we do Charlie – thanks for the encouragement!

    Brad Waddell
    FLEXquarters.com Limited http://www.QODBC.com

  • I have played with this a bit and, if I am not wrong, the views do not include all of the data. I am desperately trying to get to the Bill To box on invoices and sales receipts and I cannot find that in any of the views available in Custom Reporting.

    • Carol, I don’t use this feature myself, so I can’t answer your question right now. I suspect that the information is there, but that the address may be in a separate table. You have to make the table links yourself. You may want to think about using the QODBC driver instead – they have much clearer documentation (www.qodbc.com) so it is usually easier to find where things are at.

  • Does anyone know how do we get to the reference that will let me know what tables are what and what the fields and relationships are to create a query?

  • Are multiple ODBC connections possible so that you could access (or query) different companies simultaneously. For example, if you have 10 different QB files and want to query all 10 to produce a consolidated report of the 10 companies. We presently use datablox.com OfficeQ to extract each company to a MS Access database and then run queries across the databases, but the data is only as fresh as the last extraction. In addition, it is time-consuming to extract all the data rather than just query the source for just the queried data.

  • Carl, the Intuit listing is at http://enterprisesuite.intuit.com/support/customreporting.jsp

    Michelle, I haven’t tried this, but I believe that you can have only one file open at a time in this driver. But I won’t swear to it.

    To be honest, I find this feature hard to use for a number of reasons. I don’t use it for reporting, instead I use a product called QQube, which takes away all the worries about complicated structures. It also has a nice feature that lets you generate reports from multiple QB company files. I wrote a review of this at http://www.sleeter.com/blog/2011/03/quickbooks-reporting-is-simple-with-qqube/ . The latest update will leverage the new ODBC driver for improved speed.

  • Charlie – Thank you for the information about QQube. I agree that this is a better option to explore than QuickBooks 2011: Enterprise Custom Reporting. Thank you so much for pointing me in this direction.

  • So, does this new feature required a running and logged-in instance of Quickbooks? I have tried the various ODBC connections and they all seem to require that QB is running. I’m tried to embed all needed info (connection string / Data Source Name, SQL query, etc) into an Excel macro, and then run that macro via a Windows Scheduled task to extract and report data – but it’s hit and miss so far.

  • Tim, to be honest, I don’t use this feature at all. I either use QQube (mentioned above) or just don’t worry about this. It isn’t well developed, it is limited to Enterprise, and I’m not sure what the future holds for it.

    I wouldn’t be surprised if you needed QB open – although it could just be an issue with having the database manager running. But I don’t have a specific answer for you, sorry.

  • […] Browse to the location where your QuickBooks company file resides, select the DSN file that matches the company file name, and click Open. Your file may be stored on a server, or it may be stored locally. If you are not sure where your file is located, from within QuickBooks, press your F2 button and refer to the “Location” field. This article assumes that you have already set up the ODBC user account. […]

  • Thanks for this article. I was able to get the ODBC driver working from MS SQL server as a linked server. Trouble is that I can’t find documentation on the table names that are exposed. The tech documents referenced in the link are correct for _v_lst type entries (like v_lst_customer) but not for _v_txn type entries. Also I noticed that you have to omit the group name from the table specification, so instead of querying for “QBReportAdminGroup_v_lst_customer”, for example, you have to query for v_lst_customer instead.

    Does anyone know a way to use the ODBC driver to query what tables/views it exposes?

    • Jason, support for this feature is very limited, and to be honest I don’t work with it directly at all. I don’t spend time on things that are limited to Enterprise.

      I suggest that you look at QQube as a reporting tool. It takes all of the crazy structures in QB and organizes it into a clear, understandable fashion. If you have Enterprise, it knows how to take data from the “custom reporting” feature when necessary, and supplements that with data from the SDK method, as well as some other means of gathering data, and combines that into one presentation.

      You can see info on this at http://www.sleeter.com/blog/2012/04/qqube-update/ – there are links in that article to the original review of the product as well.

  • We work with this almost every day and we know the structure and how it all works. QQube is a great product and we use it as well, with users of all different versions of QuickBooks. The nice thing about the custom reporting tool is that it is included with Enterprise and the only cost is in the report development. Please check out our blog for more info or to contact us…http://acsiaz.wordpress.com