How to Combine Multiple Crystal Reports Into One

One Report, So Many Views!

This is the third blog in a series of tips and tricks for using Crystal Reports with Sage 100, Sage 500, and Sage X3. If you missed them, the first article in the series showed you how to Use Joins to Pull Fields From Other Tables and the second shows you how to set up Conditional Joins.

Problem:  You have a fairly complicated report and the users want “flavors” of the same so you clone it X number of times and now have several to maintain each time a change is needed.

Solution:  Consolidate or combine them into a single report with intelligence built into:  labels, sections, titles, logos/images, and more.

»Need more help with Crystal Reports? Contact our team.

Here's how to do it:

Example:  You have a sales order form (Crystal Report) that is your standard.  Users need:

• a proforma invoice (sales order that has the title “Invoice” instead of “Sales Order”),
• a picklist with underlined blank fields for users to manually complete,
• a packlist that has the look and feel of the sales order but without any price/totals fields
• a second set of all 3 (above) that has a different logo and company name based on the given sales order customer division number returned.

Steps:

1. Instead of creating multiple forms/reports, create a controlled parameter within the report to prompt the user at run time for what document type they want: Proforma Invoice, Picklist, Packlist.

2. Create a formula that determines which customer division has been returned (e.g. “01” for Acme North-West Services and “02” for Acme South-East Services)

3. Use formulas and conditional suppression as needed.

a. Logos/images and fields/labels: Conditionally suppress as is appropriate and layer one image on top of the other

Each image should have its own suppression logic.

Keep in mind it’s easier to set logic and size before you stack one on top of the other.

Note you can stack as many images on top of each other as needed.

b. Titles and field labels: either build the title and field labels as formulas or create text fields with embedded formulas.

TIP:  Give your formula names the same as the parameter names – keeps it clear for use throughout the report!  Then you can embed the formulas into text fields or other formulas and never lose track.  You also have less to maintain whenever changes are needed.

BONUS TIP:   Ever create a date parameter for a report and want the user-entered date range in your report title??  Try this…

- Create 2 formulas to trap the lower end and upper end of the user-entered date range (not the lower and upper end of the data set returned, which may or may not include the parameter dates entered):

Mindate: minimum({?InvoiceDate})

Maxdate: maximum({?InvoiceDate})

- Create your title as a text and embed the mindate and maxdate formulas:

“Sales Totals by Division from {@mindate} to {@maxdate}”

If you missed it, the first blog in the series covers how to Pull Fields From Other Tables Without a Join.

The second blog in the series, Conditional joins in Crystal Reports, outlines how to join two or more tables when the data relationship is dependent upon variable values.

If you have questions or want guidance on how to be more effective with Crystal Reports in Sage ERP, click below to get in touch with a Crystal Reports expert at RKL eSolutions.

I Need Help with Crystal