Forum Discussion
Quickbooks
I want to develope a dashboard of financial statement data pulled from Quickbooks desktop. What is the best way to pull in the data? I would welcome guidance from any other users who have developed dashboards from Quickbooks.
28 Replies
You can use the ODBC features in Enterprise. Not perfect by a long shot but it is a connection to give you some raw data if you want to fight thru getting the views connected (not obvious how to do it but possible). I have found the QODBC connector fails so if you want to get into reporting between PowerBi and QuickBook enterprise your best option is a 3rd party product called QQube which extracts the QB data into a local sybase Database. The database model is optomized for reporting so really is PERFECT for using with PowerBi. It comes with PowerBi Templates (Desktop files) that cover nearly every area you would want to report on.
I am certified on the product so do let me know if you need help getting QQube or getting it working.
Ernest
We are an accounting firm and new to Power BI. We are now venturing into working with Power BI and QB Desktop (Enterprise). Can you provide more information on what options are available to connect tothe ODBC of QB and how we can utilize Power BI
Hi Ernest, do you have an email to write you about help on connect QQube to BI? or do you have a website where I can use the professional services for that service?
Thanks
Thanks for reaching out. my web site is:
http://www.betterideagroup.com/qb-datawarehouse.html
A new version was just released over the Thanksgiving holiday that has LOTS of powerbi examples, I'd love to show them to you.
my office # is 916-774-2600
Is there a Power BI specific tutorial anywhere for this? I did all the thing son the QB end, and I can access QB data via Excel, but attempting to get at the data in POwer BI always leads to an error even though I can see all the tables. Very painful to be so close and yet so far.
I am using Quickbooks Enterprise 2017.
best,
Jesse
- Hey Jesse, It looks like you are using the QB Enterprise ODBC driver from QODBC. Does that sound right?
http://qodbc.com/support.htm
I would first confirm that you don;t have a mismatch between 32 bit and 64 bit. In the example I see you are using a 64 bit driver. Are you running a 64 bit powerbi?
Reports section in the native quickbooks connector doesn't allow you to do that. You will get aggregated data for the time period that you select through parameters.
I would recommend this connector instead: https://azuremarketplace.microsoft.com/en-us/marketplace/apps/vidicorpltd1717496723264.qbo-powerbi-connector?tab=Overview. All P&L data is combined in a single table. You can also drill down to transactions that make up value of every account, filter by day and see data on accrual vs cash basis. Same for Balance Sheet, Cash Flow, Account Receivables, Budget, etc.
Here are some dashboards that come free with this connector
Here is more info about it: https://vidi-corp.com/how-to-connect-power-bi-to-quickbooks-online-reports/Hi Veritas,
Currently, Power BI provide the feature to connect to QuickBooks Online in Power BI Desktop and Power BI Service. See:
QuickBooks Online content pack for Power BI
In your scenario, if you can publish financial statement data from QuickBooks desktop to QuickBooks online, then you can use above way to retrieve data. Also there is already a idea about QuickBooks Desktop data source, you can vote on it: Quickbooks Desktop Version
Best Regards,
Qiuyun YuI am not able to connect to my Quickbooks Online dashboard post 9/15/16? Are others having this same issue? See error below:
Data source errorThe credentials provided for the QuickBooks source are invalid. (Source at QuickBooks.)
Another option for financial planning and reporting integrated with Power BI (but also including an Excel Add-in) is a solution called Clear Jelly (www.clearjelly.net) It enables you to automatically integrate and consolidate one or multiple entities even from different accounting systems (Xero, Quickbooks, Sage etc.) and load them in a cloud based data repository optimized for analysis. This typically gives you much better performance, options to do proper consolidation as well as with Clear Jelly also
budgeting and planning features.You can trial it for free - maybe it covers what you are looking for. Of course more than happy to assist with questions.
Martin
Hi Veritas
Another option for financial planning and reporting integrated with Power BI (but also including an Excel Add-in) is a solution called Clear Jelly (www.clearjelly.net) It enables you to automatically integrate and consolidate one or multiple entities even from different accounting systems (Xero, Quickbooks, Sage etc.) and load them in a cloud based data repository optimized for analysis. This typically gives you much better performance, options to do proper consolidation as well as with Clear Jelly also
budgeting and planning features.You can trial it for free - maybe it covers what you are looking for. Of course more than happy to assist with questions.
Martin
Hi,
Thanks for sharing your QB Experience to us. I would also include some information it. When we transder all QuickBooks Online data to Quickbooks desktop this problem does'nt happens. In QuickBooks Online right side there is always a option Contact to our Support or Dial
1-888-771-0476 its a QuickBooks Official NumberAnonymous , the answer to your question depends entirely on how you did that in the cloud. If you are using the QBO application from Microsoft you will NOT be able to download the report they built. That said, if you want to connect to the data you can follow the instructions on this site.
If you would like me to look at it you can make an appointment on my booking site to reserve a time.
Ernest
QuickBooks Desktop to Power BI Dashboards by Data AnalytiX
Using the QODBC middleware provided in QuickBooks Enterprise, data is retrieved from QuickBooks and transferred to the database reporting program Alpha Anywhere/Alpha 5. Once transferred, the powerful functionality of Alpha 5 is used to transform the data into tabular custom reports that cannot be developed using QuickBooks standard memorized report customization. The transformed data is exported to CSV data files that are loaded into Power BI files to generate Power BI dashboards.
For more information—
Visit Data AnalytiX website, www.dataanalytix.net
Email, [email protected]
Call Keith Wilhoit 630-926-6868
Hi BetterIdeaGroup,
I am using QQube to pull in the Quickbooks data into Power BI. I sync'd the data without error, but now when I open the Power BI Example "financial_pandl_actual-budget-forecast_(qqube6.x).pbix", I get the following errors. I am pulling data from Quickbooks Desktop Pro 2018. Any help is appreciated!
OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E
Load was cancelled by an error in loading previous table
Thank you!
Hi MarissaGO,
First let me clarify the architecture. QQUBE extracts QuickBooks desktop information and stores it in a SQL Anywhere database. That database is what is used by PowerBI (and other ODBC compliant tools. With the information available it's hard to tell which of the 3 areas are problematic:
1. Environmental - i.e. You exact configuration of hardware / software is giving you trouble. For example the memory or CPU size of your computer could be "giving up". Can you access the same data on another computer without issue?
2. Data related - In some situations data can cause issues. - redoing the query with a sample data file usually roots out this issue
3. A software logic issue. Because of the various pieces of software involved in this solution, once you have ruled out the other 2 area's, the goal would be to sort out which product is having an issue; So to test for a Powerbi issue you would want to know if some other ODBC based data source of comparable size can be extracted on your version. Similarly, for a QQUBE issue, isolate just the QQUBE database by verifying that you can access the same data from the QQUBE Excel templates?
I apologize if all this seems complicated but since these topics live on the forum for awhile I wanted to give others tools for figuring out what might be preventing them from getting the information they need to run their business, If you would like to get some direct help can you book an appointment on:
https://calendly.com/ernestcook
Ernest- Also:
If you suspect an issue with QQUBE, you can open a new case at: https://clearify.com/cases/add-edit-case/0
if you suspect an issue with PowerBI that support request can be started at: https://powerbi.microsoft.com/en-us/support/pro/
Hi BetterIdeaGroup,
I am using QQube to pull in the Quickbooks data into Power BI. I sync'd the data without error, but now when I open the Power BI Example "financial_pandl_actual-budget-forecast_(qqube6.x).pbix", I get the following errors. I am pulling data from Quickbooks Desktop Pro 2018. Any help is appreciated!
OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E
Load was cancelled by an error in loading previous table
Thank you!
- Hi MarissaGO,
First let me clarify the architecture. QQUBE extracts QuickBooks desktop information and stores it in a SQL Anywhere database. That database is what is used by PowerBI (and other ODBC compliant tools. With the information available it's hard to tell which of the 3 areas are problematic:
1. Environmental - i.e. You exact configuration of hardware / software is giving you trouble. For example the memory or CPU size of your computer could be "giving up". Can you access the same data on another computer without issue?
2. Data related - In some situations data can cause issues. - redoing the query with a sample data file usually roots out this issue
3. A software logic issue. Because of the various pieces of software involved in this solution, once you have ruled out the other 2 area's, the goal would be to sort out which product is having an issue; So to test for a Powerbi issue you would want to know if some other ODBC based data source of comparable size can be extracted on your version. Similarly, for a QQUBE issue, isolate just the QQUBE database by verifying that you can access the same data from the QQUBE Excel templates?
I apologize if all this seems complicated but since these topics live on the forum for awhile I wanted to give others tools for figuring out what might be preventing them from getting the information they need to run their business, If you would like to get some direct help can you book an appointment on:
https://calendly.com/ernestcook
If you suspect an issue with QQUBE, you can open a new case at: https://clearify.com/cases/add-edit-case/0
if you suspect an issue with PowerBI that support request can be started at: https://powerbi.microsoft.com/en-us/support/pro/
Ernest
Click the "Reports" drop-down menu and select "Company & Financial." Click the report you want to prepare. You'll find more reports listed under "Accountant & Taxes." Customize the report by filling in the "From" and "To" date fields or choosing an option from the "Dates" drop-down menu. These options are also available in QuickBooks enterprise hosting.
Hello everyone!
I know this thread has been around for a while and many of you may have done work arounds but for those that land on this query after 2023...great news! QQUBE version 10 has a HUGE focus on Power BI !!
Today I recorded a video giving you a peek at one of the MANY templates provided by QQUBE.
Here is a brief tutorial on how to use QQUBE and Aha! to understand what product segments are profitable
https://app.weet.co/play/7fde128b/using-qqube-to-build-a-business-segment-margin-report
