One of my customers asked if there was a way to do this. There isn’t a way to do this directly, but I played around and found a way to control the visibility of vertical lines, horizontal lines and boxes. Below are the steps for controlling all lines and boxes located between the Page Header and Page Footer. However the method can be adapted for other locations. Continue Reading »
Suppressing vertical lines on a page, based on a condition.

Many people want to have vertical lines that run through all of the detail records in a report and so they draw lines that cross over the details, going from header to footer. But a problem arises when the details carry over a page break. Lines that cross over the Details section may extend below the last Detail of the page, down to the page footer. This is especially likely to happen when you have a deep Details section. This is because a deep section may not fit at the bottom of the page, leaving a large white space. Lines that cross over the Details section will continue through this space.

The alternative approach is to have the lines contained completely within the Details section. Continue Reading »
Vertical lines hang down too low at the bottom of the page

Indexes in the database normally improve the performance of a report. But I had a situation with a customer this week where the index caused a report to misbehave. The user ran a simple report listing account numbers. The report had no selection criteria. One of the account numbers showed up in two different records. But when he added a selection criteria to select just that one account number, only one of the two records was returned. The account numbers were identical and there were no other report features (like select distinct or group on server) that would affect the number or records returned. He puzzled over it for hours before sending me a copy of the report with saved data.When I added the same criteria at my end (and used the saved data) I got both records. But when I sent it back to him and he clicked “Refresh” he got only one.

That told me the problem wasn’t in the report. Continue Reading »
The index that ate my data

The brand new Crystal Reports 12 (or 2008) no longer includes the sample MDB that has been provided ever since I started working with Crystal 12 year ago. You still have sample reports but they all read XML data that is on the BO web site. You can download the XML data but I still prefer the MS Access format, because it can be made to behave just like the normal SQL based data that everyone uses. It is what is used in all of my books and is also used in most of the BO Knowledgebase examples. If you don’t have a version of Crystal with the MDB you can download a copy from the SAP support site.

If you are going to use one of these with the sample reports in my guide books then you should set up an ODBC connection to the database by following these steps: Continue Reading »
CR 2008 doesn’t include the Xtreme.MDB

If you need to do a distinct count of records that meet a specific criteria you can do a Distinct Count of an IF-THEN statement. But you will run into an interesting problem. The records that don’t meet the criteria will all have an ELSE value, and that extra value also gets counted. The result is that your distinct count is usually one higher than it should be. Some users assume that if they simply skip the ELSE line of the formula, that CR will use a NULL value as the ELSE, and that the NULL won’t be counted. However, CR does not use NULL by default. If you skip the ELSE, Crystal will use the default value for that fields data type - usually an empty string (”") or a zero. But there is away to fool CR into returning a NULL value as your ELSE value. See my (recently improved) Formula 14 for the solution.

For more tips like this you should check out my Expert Techniques series.

(Thanks to Jacques Sauvageau of Wilson Banwell PROACT for suggesting the VAL / TONUMBER. That saves a couple of steps over my original method. )

Maybe instead of printing “YES” and “NO” in a report column you want to print a check box symbol - with or without the check mark. All you need to do this is the WingDing font, which is on most PCs, and a formula like the following:

If {Order.Shipped} = “T”
then Chr(254)
else Chr(168)

Place this formula on your report and assign it the WingDing font. You will see a box for each record, with check marks only on the shipped records (or whatever your condition). You can also use other graphic fonts like Continue Reading »
Printing check marks and other symbols from formulas

I recently worked with a customer who used the 5-4-4-accounting period schedule. This means that each quarter is made up of 13 weeks, with the first month of each quarter being 5 weeks long and the other 2 being 4 weeks long. This makes for a 364 day year, which means once every 7 years or so you need to have a 53 week year. The challenge is that the year always starts either a few days before or after January 1.

This customer had some fairly complex Crystal Reports formulas that were difficult to maintain each year and so I tried to come up with a simpler process. I came up with a set of 3 formulas, Continue Reading »
Calculating periods in 5-4-4

When you place a large cross-tab in the report header, page numbers won’t appear until the cross-tab is complete. This is because the page number is usually in the page header/footer and these sections have to wait until the Report Header has completely printed. So, if the cross-tab takes up 3 pages, you won’t see page numbers until page 4. Some folks have tried putting the page number in the Report Header with the Cross-tab, however this will only print the page number on the first page.

The workaround is to create what I call a “dummy” group. Continue Reading »
Multi-page cross-tabs missing page numbers

Crystal will not allow you to delete a formula or parameter field from a report unless that item is no longer used anywhere in the report. Unfortunately, there are several hundred places where a formula could be hidden. These include sorting, grouping, selecting, totaling and dozens of formatting formulas for every report object. Checking all of these hiding places is not practical, so here are two ways to have Crystal help you uncover where the field is being used.

My favorite method is to edit the formula or parameter in question and change it’s data type. Continue Reading »
Finding a stray formula or parameter field

The width of the design screen in Crystal Reports is taken from your default printer settings; including the paper size and orientation. These settings can be changed using the menu options “File - Printer Setup”?. The maximum width for most printers is about 13.5 inches, achieved by using legal size paper in landscape orientation.

But some reports are never sent to a printer. They are only exported or previewed to the screen. This can be frustrating because even these reports are limited to the width of that specific printer.

If you need a wider design area for an exported report, you need a to setup a printer driver that supports wider paper sizes Continue Reading »
Expanding the design area in Crystal Reports