Instructions When Designing Your Reports

1. Use filters in a 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 the QODBC Online Test Tool to determine your needed fields.

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 various results.

6. Expecting QODBC to operate similarly to other relational-based ODBC drivers will cause 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:
    FROM {oj 'table' 'table' LEFT OUTER JOIN 'table2' 'Table2' ON 'Table.'' TxnID'='Table2'.' TxnTxnID'}
within the Command option works ok.

8. Creating an outer join for a sp_report or to a table is not supported within a manual SQL statement. However, it is kept 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 Tables

   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 the 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, right-click on the command). Click OK

Create Reports

    The fields specified in your SQL statement are available for selection in your report.

Tags: QuickBooks Online, QBO, online reports, Crystal Reports