[QODBC-Online] How to use QODBC Online with Crystal Reports
Posted by Jack - QODBC Support on 20 August 2015 10:04 AM
Instructions When Designing Your Reports
1. Use filters in particular date range whenever possible.
2. When grouping by a table field, use a formula to put the field on 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 Online sp_report) to use the QuickBooks report engine that already “relates” the tables. You can relate a 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 a 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.
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 QuickBooks Online Data and Click Next, finally click Finish on this screen:
Input UserID and password if there're:
Select Tables Needed
1. A list of QuickBooks Online 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 Online 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.