How to use ODBC to the fullest?

  • 1
  • Question
  • Updated 4 years ago
  • Answered
I am aware that QB Enterprise has an ODBC functionality that allows me to create, slice and dice my own company database but I'm at a bit of a loss on how to get full use of this function and maximise it. I do a lot of ad hoc reports for management and our board likes the reports differently done and the Quickbooks standard reports just don't cut it for them. I need help with direction on where I can get more help or info in this area. Many thanks in advance guys!
Photo of Sara Kennedy

Sara Kennedy

  • 296 Points 250 badge 2x thumb

Posted 4 years ago

  • 1
to be able touse QODBC one need to have programming knowledge too, QODBC gives you all tables, AND one needs to know how to read those tables and then use programming language to create automated reports, also you can use MS Access, or even excel, still they require manipulation. Just try using Crystal reports same issue. Sara, you need to discuss what we can do.
Photo of Kevin Russell

Kevin Russell, Accredited Partner

  • 49,902 Points 20k badge 2x thumb
Sara what reporting tool are you using? Or are you just running ad hoc queries? Like Chandra said the odbc connector is useless without some basic sql knowledge. I think if you can decide what you want it might provide you with a path forward. Thank you for your time Kevin t : 0407744914
Yes, agree with Kevin, if you want some fixed reports say at least 3- 5 we can automate it, but if you want to keep changing reports ,well of course, great excel with macros knowledge will do it or MS Access , but you need programming knowledge like , however, send me some samples in excel , we can have a look at it.
Photo of Sara Kennedy

Sara Kennedy

  • 296 Points 250 badge 2x thumb

At the moment, excel is my reporting tool but the information is obtained straight from QB's standard reports eg. Budget Performance report etc.  However I have changed our reporting period to go from the last Sunday of the previous month to the last Saturday of the reporting month (unless the last Sat happens to fall on the last day of the month, which has been great for Jan & Feb 2015). 

QB automatically prorates the month which I don't want.  And, I am constantly having to churn out monthly reports and pasting it together because we don't use the calendar month for reporting purposes.  The budget figures I put in is based on my reporting period but it gets pro-rated when I run a Budget Performance report, which really skews my variance analysis.  Very annoying.

Also board members don't like the way the variances are showing in the reports.  The like it red for those that are highlighting a problem but not for those not a problem.  The way QB does it, it takes the Budget minus the Actual for both Income and Expenses, so when it comes to expenses, they'll have to switch their mind in identifying problem areas.

I am ok with excel manipulation and am rather well versed around excel and have been working around the QB limitations for a while now but I'm sure there are smarter ways to do this.  I want to be able to have the queries all ready for each type of standard board reports that I do and all I have to do is refresh the parameters and database and viola, my template reports are updated. 

Maybe it's a matter of refreshing my SQL on Excel and Access.  I haven't touched Access for over 10 years.   

well , yes, you have not touched for 10 years, same might goes for me , we have stopped using them they are not fully integrated and real time. what you need is to have an automated report tool , to give you the ability to filter they way you want it.and no manipulation required, we could do that. We will develop the application all you require is to enter the filters and press the enter button.
automating business is what i love doing now,
Photo of Sara Kennedy

Sara Kennedy

  • 296 Points 250 badge 2x thumb
Sounds awesome. So you will incorporate the application into QB software?  Will it work on QB hosted or will I have to still use the QB desktop version for the application to work?
For hosted no, need to wait may be for next 3- 4 months or more , but for desktop yes, once i see what you require , then the way it will work is that it integrateS in realtime the necessary info from RA , then we need to incorporate your requirement which will be all programmed at back end, and you need to use the filter button to get the report. this report than can be automatically opened in excel, and automatically attaches the report in ms outlook, etc. we also give you drill in, you are able to double click in the report tool and it will open the source in RA .But no need to open in excel as you have what you want you need to print it , thats all
Photo of Kevin Russell

Kevin Russell, Accredited Partner

  • 49,902 Points 20k badge 2x thumb
You know if you have Brio or Crystal Reports you could run your own ad hoc queries using the odbc link. Kevin t : 0407744914
Photo of Sara Kennedy

Sara Kennedy

  • 296 Points 250 badge 2x thumb

Thanks Kevin. I have heard about Crystal but have also heard from a few to stay away from it.  I have yet to explore it. 


Hi Kev, yes, i did mention great Crystal report, still have to work on it, my days Pastel.
Photo of Sara Kennedy

Sara Kennedy

  • 296 Points 250 badge 2x thumb

Chandra, the only problem is... that will solve my management reports, but not the board reports because it has a different look from the cut out RA (oops, still using QB) standard look reports that is minus the colours and pretty looks for the board. 

So maybe a Crystal type reporting tool like what Kevin is suggesting might be better to kill two birds with one stone.

Sara, without looking , at what reports look like i cant answer, if you send screen shots, we  can see, its not costing to any one , except my time lol
Photo of Sara Kennedy

Sara Kennedy

  • 296 Points 250 badge 2x thumb
Sending the template to your email.
Photo of Sara Kennedy

Sara Kennedy

  • 296 Points 250 badge 2x thumb
Thanks. Will do, just have to erase out information so will not be today.
Thank you Sara, for the reports that you require automated, as we have discussed, we can get that report tool no issues, but you are using hosted version. In hope fully, 3- 4 months we get this going for you.