QuickBooks Crystal Reports: Reverse Engineer Your Database

QuickBooks Crystal reports are a new trend in projects these days. For more understanding of QuickBooks data from crystal reports, you have to introduce a special ODBC driver called QODBC. QODBC is an exceptional driver in which when the queries are entered within the QB tables then it displays a small counter panel for each table.

It showcases the records returned from that table. Suppose the report is composed of six tables and you can view the pile of small windows crop up open and close as QODBC assembles your data set.

Common Errors Reported

In case you redistribute the reports to users with a client-side viewer program. The report worked fine in CR but would not return any data through the viewer. It took a while to find the problem. If you want QODBC to access data in QuickBooks, then first log into QB as an administrator.

After this, run the first query to access all the data present in QB. Now to successfully run reports using the CR viewer, you need to quit QuickBooks and log in again as an administrator. This should resolve these issues.
Another case is how the tables are presented.

Attempting to create a simple report that used the Invoice Header table resulted in Windows displaying records being read from the Invoice Details table. It shows that some fields that are listed as columns saved in the header table are the real values of the fetched from the details table.

In case you plan to amalgamate QuickBooks/QODBC and Crystal, you need to ensure that you are available with sufficient energy. The process of garnering data out of QuickBooks comprises overhead costs and also functions slow.

How to Create a Report with QuickBooks Data?

Intuit recommends steps to create a new report via wizard or manual methods.

They are as follows:

• Expand the folder called ‘Create New Connection’.
• Next, expand another folder called OBDC (RDO) to open a new window.
• Under this new screen locate and click on QuickBooksData.dsn, followed by the ‘Next’ tab, and lastly click on the ‘Finish’ button.
• The new set of tables of QuickBooks appears for further selection by you.
• Choose the appropriate tables required for your report (go through the following details for manual SQL statements and sp_reports).
• Link your tables (View the data layouts for linking information
• The table fields are now available for selection in your report. Run reports as normal from this point

How to Generate a New Report with SQL or Existing QB reports?

1. In the scenario of Manual SQL statements or sp_reports, you need to select the ‘Add Command’ and rotate to the right side of the panel.
2. A new screen will pop up.
3. Either copy & paste or enter manually your SQL statement or sp_report command as shown, then click "OK"
4. This will enter the SQL statement in order to edit, right-click on ‘Command’, and then select an OK tab.
5. The result will be an occurrence of fields labeled within the SQL statement for the selection process.

Anticipations when using the QODBC Driver with CR

• QuickBooks functions on a flat database and even after using the OBDC driver, the database does not convert to a relational database. This is the only reason why relating more than several tables will begin to severely affect the performance and there will be no indexes. The problem can be faced in the different versions in future versions of SDK and QODBC developed by Intuit.
• QODBC functions like a cover/shield around the Intuit SDK that helps customers to acquire their required information through regular database tools and attempts to learn the SDK. Thus it is the best way to garner QuickBooks Crystal Reports.
• The latest filters can be used for filtering particular date ranges whenever needed.
• Next, add the fields that you need in order to use sp_reports correctly.
• With the new VB Demo find out which fields you required
• Enable the sp_report to use the QuickBooks report engine that already "relates" the tables. You can relate a sp_report and a table to draw a list of results.
• Await for QODBC to function similarly to other relational-based ODBC drivers that will cause some annoyance as through XML only Intuit unveils its data.
• While grouping via a table field, you can use a formula to put the field within it. This authorizes  Crystal to do the grouping, in comparison to the driver which is much faster.
• Many crystal reports are developed by the QODBC Development team to arrange and have modified for you. If time and learning curve are considered imperative factors, it is probably a cheaper option.

If you get stuck anywhere in the process, feel free to get in touch with SMB QuickBooks Technical Support Team, Which is available 24/7 on smbaccountants.com.