views:

374

answers:

3

I am looking to build some reports for Quickbooks data, without using Quickbooks built-in reporting, or through a third party library (like QODBC).

Quickbooks 2008 and newer are built on a SQL backend.. In looking around there seems to be several products and angles to do this from.

Ideally I would like to be able to do direct SQL hits on the database myself, or use something like Crystal Reports.

I wanted to ask here to gather any things to look at closer, and what to potentially avoid to save myself grief.

Thanks in Advance!

Update: It seems Quickbooks encrypts it's sql database so you can't read it directly with a reporting tool.. There are a few products on the market that can help.. more to come.

+1  A: 

If its hosted on SQL 2005 or 2008 you can make use of Reporting Services (free) and the Report Builder that is bundled with it. If you use 2008 then I recommend downloading Report Builder 2.0

http://www.microsoft.com/downloads/details.aspx?familyid=9f783224-9871-4eea-b1d5-f3140a253db6

Here's some links to learn RS:

http://www.google.com/search?q=reporting+services+tutorial+video&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-US:official&client=firefox-a

SQLChicken
A: 

So are you on the 2008 edition (SQL Server)?

If so, you can use SSRS as SQLChicken posted, or you can use just about any other reporting tool, and connect via SQL Native Client (shipped with SQL Server): - Crystal Reports - Excel - insert your favorite reporting tool here

Aaron Alton
The issue seems to be that Quickbooks encrypts their database so it can't be read directly by reporting tools.
Jas Panesar
A: 

Your best bet is the QuickBooks SDK, it provides facilities to fetch data and also fetch reporting data directly from QuickBooks, formatted as XML for easy parsing and display however you want to display it.

You can see the data that's available using the qbXML/QBFC QuickBooks On-Screen reference (the requests you can send to QuickBooks are found in the 'Select Message' drop-down box at the top of the screen).

The QuickBooks SDK is a free download (registration required) from the Intuit Developer Network

If you're writing a desktop application, you can look into using QBFC, which is an API that talks to QuickBooks using COM/DCOM.

If you're using PHP, there's a popular QuickBooks PHP framework on the IDN forums.

If you're using ASP.NET, here's a QuickBooks ASP .NET framework too.

If you want direct SQL access, you should look at QODBC (which is an SQL wrapper which translates SQL queries to qbXML SDK requests) or AccessBooks (which uses the SDK to mirror QuickBooks data to an SQL database).

Keith Palmer
Thanks for your input. I am familair with most of those solutions and was hoping I could get around having to use QODBC or AccessBooks. Accessbooks looks to be interesting for my needs since it's a sql mirror.
Jas Panesar
Unfortunately, QuickBooks does not allow direct SQL access to the back-end database, so you're out of luck. If you want to access it like an SQL database, your only choices are QODBC (which maps SQL requests to qbXML requests), or AccessBooks or the code from the QuickBooks PHP Framework linked above, (which provide SQL-mirror like functionality). Outside of that, consider issuing qbXML requests, caching the data, and manipulating it into reports from there.
Keith Palmer