Knowledgebase: Sp_commands & Reports
[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 Report.

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 Select Tables window. Please, select tables you want to extract data from. In this Example, I am selecting InvoiceLine table.

The table added into Crystal Report click "Next" for adding table fields in the report.

The Report Wizard is showing table & available fields.

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

Add group information of report we will group data by selecting TxnID field and clicking the ">" button then click "Finish".

Report designer will show selected fields

Now, We will add Customer details, Billing & Shipping details, TxnDate, RefNumber, Terms, DueDate in Group Header section. We will add InvoiceLine details (i.e. Item, Description, Quantity, Rate & Amount field) in detail section. We will add SubTotal in 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 below Text Objects.

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

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

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

I have drawn below line in "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 in the single page), then select "Section Expert" & click on the Group Footer enable "New Page After" drop-down arrow and click "OK".

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

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

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

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

Insert 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 drag and drop.

Now we need to link RefNumber parameter with the RefNumber value in the report.

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

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

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

The report available for particular RefNumber.

Please click here for downloading 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).