Creating a report with QuickBooks Data

Create a New Report via Wizard or Manual Methods:

 Expand the “Create New Connection” Folder:

Expand the ODBC (RDO) Folder to bring up the following screen:

 Select “QuickBooksData.dsn” and Click “Next”, then click “Finish” on this screen:

 A list of QuickBooks tables now appears ready for selection:

 Select several tables for your report (for sp_reports or manual SQL statements, see below):

 Link your tables: (For linking information see the Data Layouts)  

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

For sp_reports (see the Data Layouts) or manual SQL statements, Highlight “Add Command” and move the right panel. 

The following window will appear.  Type or paste your SQL statement or sp_report command as shown, then click “OK”  

 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.

What to expect when using the QODBC Driver with Crystal Reports

• Use filters; in particular date ranges whenever possible

• Use only the fields you need when using sp_reports

• Use VB Demo to find out which fields you need; or if you desire an easier tool that creates the SQL for you, use the QTableGrabber or QReportGrabber tools which hide the SQL and programming from the end user.

• Use sp_report (see Data Layouts) 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.

• Using this driver 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.
• Expecting QODBC to operate similarly to other relational based ODBC drivers will cause some frustration because Intuit exposes its data only via XML. You will have to think around the box, but your efforts will be rewarded.

• 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; will be much faster.

• The QODBC Development team has created many Crystal Reports that you may tweak, or arrange to have modified for you. If time and learning curve are important factors, This will probably be a cheaper option. See Services.

• We have a Crystal Reports viewing tool, called QDataViewer which contains special report functions; includes a reports API for specific QDataViewer operations, and has the ability to refresh report parameters – something even Crystal Reports cannot do.