Crystal Reports: Not Equal Join

To paraphrase the slogan in “Animal Farm,” some joins are more equal than others. And some joins are not equal at all.

We had an interesting label report to write for a client. The table had a quantity field with the number of labels required in each record.

The first part of the solution was to create a table with a single integer field, and with values of 1,2,3,4,… in successive records. Make sure you have enough records to cover the largest value we expect in our quantity field.

We then used a “<=” (Less than or Equal to) join to the original table to the counter table. That created 6 records when the Quantity field was 6 and 10 records when we had 10 in the field. 

You can also use this technique with a table with a record containing every day this year, and the accounting period the date belongs to, and other period specific information. 

Since we're on the topic of joins, what about a "join and split" operation? Consider this formula:

Join ({?Country}, ", ") // array, delimiter

If the {?Country} parameter contains the values ["Australia", "New Zealand", "Hong Kong"] the result of this function will be "Australia, New Zealand, Hong Kong".

The split function can be used to perform the opposite result: taking "Australia, New Zealand, Hong Kong" and returning an array. This would be used to pass a multi value parameter to a subreport.

1) Create a formula to join the list together (as described earlier)

2) Link that formula to a single value string parameter in the subreport

3) Modify the record selection formula in the subreport (with the split function) to:

{Customer.Country} = split({?Pm-@Country List}, ", ")

Can you think of a few uses for this?

A somewhat related topic is "Keep Together." One of formatting options on objects and sections in your report is “Keep Together”. Normally this is ticked to say that you do not want the object split over a page break. For database fields, formulas and even charts and maps that is probably the way you want your report to behave, so leave the default tick as given.

However, there are two objects that you might need to have behave differently. A crosstab or a subreport can grow dramatically in size during the processing of a Crystal Report. There may be times when you want the entire object on its own page, but otherwise, you might want to see as much as you can on the current page, and then show the rest on subsequent pages. 

Untick “Keep Object Together” if you want to see some of the object fit on the current page. You will also need to untick “Keep Together” for that section in the section formatting. 

But, also consider how large objects might mess all this up. There are three objects that can change size dramatically inside a report. They are:

• Memo field

• Crosstab object

• Subreport.

 If you place these on your report and they become very large, the “Keep Together” property can result in the entire object being relocated to the next page.

Sometimes in these situations, you are happy to see some of the object on the current page and want it to continue over subsequent pages.

Remember to untick “Keep Together” on the formatting for the section the object is in. On subreports and memo fields you will also need to untick “Keep Together” on the object format.

This leads us, obviously, to the related subject of page breaks between groups. You can do this from the Format | Section menu. Crystal Reports: A Beginner’s Guide provides a step-by-step procedure.

Here's a short tutorial on that:

There are several situations which can be easy to control if you understand how the section expert works with "New Page Before" or New Page After." You can use either of these properties to set page breaks, but we find it is simpler to focus on using just one of these.

If you’ve just got one level of group in your report, set your "New Page Before" on Group Header 1. To get rid of the blank first page this creates, you also need to suppress the "Report Header" section.

If you have multiple groups in your report, and you want a page break for a lower level group (e.g. Group 3), suppress the higher level group headers, and set "New Page Before" on your selected group (in this case Group 3). Depending on how often you want to see the higher level group names, you can put the Group 1 Name and Group 2 Name objects in either the Page Header or the Group 3 Header sections.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.