Chelsea Technologies Ltd - Crystal techniques. Sample reports

This is the most popular sample request. Even now, years later it is still very popular. The report was developed with Crystal Reports 8.0 so can be viewed with that or any later version.

Running Totals in a Crosstab

In Issue 34 (March 2003) we explained how to display a running total field in a crosstab.

This uses one of the features in Crystal Reports 9, so requires that or a later version.

The sorting of the data becomes a critical issue prior to loading into a crosstab.

Wrapping Crosstab

When Howard Hammerman and Bruce saw Cathy Mitchisch demonstrate this technique at the CDUGNA New Orleans conference (May 2004) we were very impressed.

You can take a crosstab object and wrap the columns that would normally appear on the horizontal page into a subsequent section.

You need to use the SQL command object in Crystal Reports 9 (or a stored procedure/view that does something similar in an older version).

This was covered in Issue 44 (August 2004)

Calculate Age in a formula

From Crystal Clear issue no 23

In Issue 22 Sep-Oct 2001 we showed you several ways to calculate an accurate age from a date field. Charley Nash (from Nashville, TN) suggests some of us like to do things the hard way. Here is his formula

if Month ({table.datefield}) < Month (CurrentDate) or //birthday in prior month
(Month ({table.datefield}) = Month (CurrentDate) and //or birthday this month
   Day ({table.datefield}) <= Day (CurrentDate)) // and one day prior this month
then
    Year(CurrentDate) - Year({table.datefield}) // difference in years
else
    Year(CurrentDate) - Year({table.datefield})– 1 // difference less 1

Age as a custom function (Crystal Reports v9 onward)

From Crystal Clear issue no 41

In issue 23 of Crystal Clear back in November 2001 we published Charley Nash’s excellent formula for accurately calculating a person’s age as at any date. Charley has now sent us the formula as a Custom Function. Place this in the repository to have the function available in all your reports.

Function (datetimevar Birth, datetimevar Current)
if Month(Birth) < Month(Current) or
(Month(Birth) = Month(Current) and Day(Birth) <= Day(Current))
then year(Current) - year(Birth)
else year(Current) - year(Birth) - 1

If you copy & paste, remember to check that minus signs are copied correctly, as they may copy as a dash sign.

Multiple Summaries

SAP have an interesting example of a report which shows multiple summaries by several time periods based on a user entered end datetime.

http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/22485

Maps for use in Crystal Reports

File Name: MAPS_NZ.EXE Originally compatible with versions: 7.0 only 16 & 32 bit

Modified by Chelsea Technologies Ltd for use with later versions of Crystal Reports. Now compatible with Crystal Reports 8.0, 8.5, 9.0 and 10.0.

29 Various Map Layer Files

SAP / Business Objects have provided Crystal Reports users with the ability to add a map of New Zealand to a report using the mapping feature first introduced with Crystal Reports 7.

Crystal Reports ships with a geographic mapping feature developed by MapInfo called MapX. This feature is available on the main button bar as well as the insert menu and allows data values to be associated with geographic regions on a report.

The first release, 7.0.x.192, included a map of the world, maps for countries such as Canada, the USA, Japan, UK, and Australia as well as various maps at the regional level.

A map of New Zealand was not originally included in this first release but is now available by copying these files to your MapInfo MapX directory.

Follow the steps below to utlize the new map files:

Step 1. Close down Crystal Reports

Step 2. Copy all 28 New Zealand map files to: C:\Program Files\MapInfo MapX\Maps

Step 3. Run the MIGM30.EXE program in the c:\Program Files\MapInfo MapX\Program folder

Step 4. After registration is complete, you can now start Crystal Reports and create a map following the general rules for map creation found in Chapter 18 of the Seagate Crystal Reports 7 User's Guide. You should now be able to display a map of New Zealand.

In the future, additional maps are available for purchase from MapInfo directly at: www.mapinfo.com or in North America, you can contact MapInfo at: 1-800-Fastmap. http://www.businessobjects.com