[QODBC-Desktop] How to use QODBC with Crystal Reports
Posted by brad waddell on 12 March 2009 05:21 PM
What to expect when using the QODBC Driver with Crystal Reports
Your QuickBooks Company File (.qbw) is a flat file database. Having an ODBC driver will not change it to a relational database. For this reason, relating more than several tables will begin to severely degrade performance as there are no native indexes.
QODBC acts as a 'wrapper' around the Quicken qbXML SDK so customers can finally get at their data using standard database tools like Crystal Reports without having to learn the SDK. For Crystal Reports this is the only method of retrieving QuickBooks data.
Instructions When Designing Your Reports
1. Use filters in particular date ranges whenever possible.
2. When grouping by a table field, use a formula to put the field within it. This allows Crystal to do the grouping in memory, rather than the driver, which will be much faster.
3. Use only the fields you need when using sp_reports
4. Use VB Demo to find out which fields you need.
5. Use sp_report (see: QODBC sp_report) to use the QuickBooks report engine that already “relates” the tables. You can relate an sp_report and a table to get a variety of results.
6. Expecting QODBC to operate similarly to other relational based ODBC drivers will cause some frustration because Quicken exposes its data only via XML. You will have to think around the box, but your efforts will be rewarded.
7. Using an outer join for tables is not supported from within Crystal; however creating an outer join with the correct syntax:
8. Creating an outer join for an sp_report or to a table is not supported within a manual SQL statement. However it is supported within Crystal Reports by visually linking the Command option and the table.
Note: Using QODBC within Crystal Reports is more art than science. We have produced a variety of custom reports and forms for clients that are nothing short of amazing. If time and learning curve are important factors, we may be able tweak one for you.
Create a New Report via Wizard or Manual Methods
Locate the DSN
1. Open Database Expert Window, Expand the Create New Connection Folder:
2. Expand the ODBC (RDO) Folder:
3. The following screen will be brought up, then select QuickBooksData.dsn and Click Next, finally click Finish on this screen:
Input UserID and password if there're:
Select Tables Needed
1. A list of QuickBooks tables now appears ready for selection:
2. Select several tables for your report (for sp_reports or manual SQL statements, see below):
Link your tables (For further linking information see Data Schema) :
Create Report as Normal
The table fields are now available for selection in your report. Run reports as normal from this point.
Creating a Report with SQL or an existing QuickBooks Report
Add Command to Report
1. For sp_reports (see: QODBC sp_report) or manual SQL statements, Highlight Add Command and move the right panel in Database Expert Window.
2. The following window will appear. Type or paste your SQL statement or sp_report command as shown, then click OK
3. Your SQL statement is now entered (to edit, just right click on Command). Click OK
The fields as specified in your SQL statement, are now available for selection in your report.