Crystal Reports and QuickBooks

Tuesday 13 November 2012 @ 10:19 am

I recently got a chance to work on a project using Crystal Reports to read QuickBooks data. To read QB data from CR you have to use a special ODBC driver called QODBC put out by FLEXquarters. This an unusual driver in that when it queries the QB tables it displays a small counter panel for each table, showing the records returned from that table. If your report includes 6 tables and you will see a stack of 6 small windows pop open and close as QODBC assembles your data set.  There is a switch to hide them if you choose.

We found another quirk when tried to deploy the reports to users with a client side viewer program. The report worked fine in CR but would not return any data through the viewer. It took a while to find the problem.  Before QODBC can access QB data you have to run the first query while you are logged into QB as an administrator.  The instructions made this sound like a one time process, but we found that the CR designer could access the data and our CR viewer program could not.  The customer logged into QB as an administrator again, and then ran the reports using the viewer.  This resolved the problem.

Another quirk has to do with how the tables are presented.  As a test I tried to created a simple report that used only the Invoice Header table.  I was surprised to find that the little windows displayed records being read from the Invoice Details table.  It appears that certain fields that are listed as columns stored in the header table are actually roll-up values from the details table.

Also, if you plan on using the combination of QuickBooks/QODBC and Crystal, make sure you have plenty of horsepower.  The process of getting the data out of QuickBooks involves some serious overhead and can be slow on older hardware.

The good news is that the guys at QODBC put out a pretty good schema for the QuickBooks database.

If you have uncovered any other quirks when working with QODBC or QuickBooks, please post a comment or drop me a note.









Leave a Reply

Jeff-Net
Recrystallize Pro

The Expert Series