Knowledgebase
[QODBC-ALL] How to export QuickBooks Invoices into Crystal Report format
Posted by Jack - QODBC Support on 15 June 2017 09:54 AM

How to export QuickBooks Invoices into Crystal Report format

Problem Description:

I would like to know if QODBC will convert QuickBooks Pro Desktop invoices into Crystal Reports.

The attached invoice is similar to what we need to export to Crystal Report. It doesn't have to be exact but needs to be able to show the descriptions, etc.

Solution:

You can export QuickBooks Invoices into Crystal Report format using QODBC.

The first step is to create a new report.

The Database Expert Window will be open. Expand the "Create New Connection" Folder:

Expand the "ODBC (RDO)" Folder:

The following screen will appear. Select "QuickBooks Data" DSN and Click "Next," finally click "Finish" on this screen:

There is no UserID and Password required for connecting QuickBooks Data using QODBC. So you can click "Finish" directly.

A list of QuickBooks tables will appear and is ready for selection:

Crystal Report will return a list of available tables in the Select Tables window. Please, select the tables you want to extract data from. In this example, I am selecting the InvoiceLine table.

For the table added to Crystal Report, click "Next" to add table fields in the report.

The Report Wizard shows showing table & available fields.

Now, Select the field which you want to show in the report by clicking the ">" button & click "Next."

Add group information to the report. We will group data by selecting the TxnID field and, clicking the ">" button, then clicking "Finish."

The report designer will show selected fields.

Now, We will add Customer details, Billing & Shipping details, TxnDate, RefNumber, Terms, and DueDate in the Group Header section. We will add InvoiceLine information (i.e., Item, Description, Quantity, Rate & Amount field) in the detail section. We will add SubTotal in the Group Footer section.

You can add the caption in report data using Text Object from Menu Insert >> Text Object.

In this example, I have added the below Text Objects.

You can draw the box in the report using Menu Insert >> Box.

In this example, I have drawn a box around the "Ship To" details

You can draw a line in the report using Line from Menu Insert >> Line.

I have drawn the below line in the "Ship To" Box.

Same way, I have drawn Box & Line on other report details.

If you want to print one invoice per page (i.e., Only one invoice on a single page), then select "Section Expert" & click on the Group Footer, enable the "New Page After" drop-down arrow, and click "OK."

You can view the report by clicking on the "Preview" tab.

The actual report will look like the below sample in Preview mode.

You can add the RefNumber parameter in the report. So you can see the invoice for a particular Invoice#. You can add the RefNumber parameter in the information to limit the records or view a single invoice.

You can add parameters by right-clicking on "Parameter Fields" & click "New."

Insert the parameter name, Choose "Static" as a list of values & choose "RefNumber" in the value field and click "OK."

Add a parameter to the report by dragging and dropping.

We need to link the RefNumber parameter with the RefNumber value in the report.

For linking, Right-click on "RefNumber" & click "Select Expert...".

In the select expert window, select "is equal to" & "{?RefNumber}" parameter & click "OK."

When you run the report, the crystal report will prompt for RefNumber, as shown in the sample below. Enter a RefNumber value and click "OK."

The report available for particular RefNumber.

Please click here to download Sample Report.

 

(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).