Quick Books / CX - Has anyone created a real-time dashboard connected to a QB data file?

[Former Member]

Can it be done? Have you done it successfully? What are the 2-3 things we should know about? Thanks!

[Former Member · Accepted solution]

<br><br><p>...and not easily.</p><p>There are numerous hurdles to doing this. First & foremost, there is no way to connect realtime to a QB database. </p><p>There are a few products that allow you to connect via ODBC to export the data out to Access/SQL Server, etc. There are issues with those, as most of them require the QB file to be open during access. Some are better than others. I found them through days of googling and evaluating demo products.</p><p>Once you get the tables out of QB into your own database, there is no documentation on the data schema to assist you in developing the SQL queries to build accurate reports.</p><p>There is <strong>no way</strong> to dynamically extract the budget information from QB via ODBC. Intuit has kept it completely proprietary. There was one company that had done it a few years ago, but they are no longer responding to emails.</p><p>Having said all that, I ran into this problem about 9 months ago with a customer. I used a third-party package to extract the tables into SQL Server, and dynamically schedule refreshes. To resolve the budget issue, I used the QB SDK to automate the export of the standard budget report from the Report Center with the necessary level of detail for our reporting purposed.</p><p>To create the SQL, we had to resort to the shotgun method. I reverse engineered the major reports (P&L, Balance Sheet, Income Statement, etc.) until we understood the debits & credits scenarios for both the accrual & cash basis, and could match any existing QB Report Center report with 100% accuracy. </p><p>It took the better part of 5 months/1200 labor hours, but we now have a realtime solution (except the budget data, which is updated nightly) using Xcelsius, Microsoft SQL Server 2005 & MS SQL Reporting Services, which integrates the client&#39;s logistics data with their financial data. The C-level executives are extremely happy with their dashboard, and Xcelsius is by far their favorite part of the solution.</p>