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.
When designing your reports:
 | Use filters; in particular date ranges whenever
possible. |
| 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.
|
| 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 you or the end user.
|
| 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. |
| 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. |
| Using an outer join for tables is not supported
from within Crystal; however creating an outer join with the correct
syntax:
FROM {oj 'table' 'table' LEFT OUTER JOIN '
table2' 'Table2' ON 'Table'.'TxnID'='Table2'.'TxnTxnID'}
within the Command option works ok. |
| Creating an outer join for an sp_report oj 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. |
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.
We also 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.
QDataViewer
can be used to run reports on other computers that have
QuickBooks and there's also a OEM version for developers to deploy
reports to other sites.
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 further linking information see
Data Schema)
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
QODBC sp_report) 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.
Announcement List
To be alerted to news about QODBC, product releases, beta
test cycles, new features and promotions subscribe to QODBC News Asia
Pacific.
