I have run into several situations recently where I needed to have one Page Header for most of the pages but a different page header at the end of the report. It may be that you have an object in the report footer that might add several pages after the regular report pages. This could be a subreport, a crosstab or a large block of legal boilerplate. When this happens the Page Header for those pages may need to be different. For instance you may need to turn off the column headings for those extra pages or switch to a different heading on the page. So you need some type of flag to tell the report when to switch.

Sometimes this is as simple as suppressing the Page Header with the function OnLastRecord. This probably works 97% of the time. But there are times when this approach misfires. For instance when the last record of the report is the only record on the last page. Your Page Header will be OnLastRecord but you will still want the headings on that page over the last record’s data. So here is a more complicated but more reliable method.

1. Insert a new subsection below your last Report Footer section
2. Suppress this new subsection of the Report Footer.
3. Create a formula field that says this:
   //{@Set RFPassed}
WhilePrintingRecords;
BooleanVar RFPassed := True
4. Place this formula in the suppressed Report Footer.
5. Use the following suppress formula for the Page Header to be suppressed:
   WhilePrintingRecords;
BooleanVar RFPassed

When the report gets to the suppressed Report Footer it triggers the formula which changes the variable from True to False. Every Page Header before that point will have seen the variable as False and won’t suppress the section. But after that point the Page Header will see the variable as True and will suppress the section.

Crystal Reports introduced custom functions back in version 9, but I have only recently started using them in production. To make them worthwhile you have to have reports that do the same type of calculation many times but with different fields. You also have to be willing to move the function from one report and add it to another whenever you need it in a different report.

But I have one very active customer who needs very complex reports with hundreds of formulas. In these reports we found ourselves doing the same two things over and over again:
1) Calculating an elapsed time number (seconds or minutes) and then converting that into a string (hh:mm).
2) Converting a time string (hh:mm) back into a number for use in other calculations.

After writing dozens of similar formulas (and sometimes forgetting an important piece of the logic) we decided to create two custom functions. So here are the two examples of the custom functions described above. You can paste them into your reports if you have a use for them:

//SecondsToTimeString:
Function (numberVar TotalSec)
NumberVar Hours := Truncate (Abs( TotalSec / 3600)) ;
NumberVar Minutes := Truncate (Remainder ( TotalSec , 3600) / 60) ;
(if Totalsec < 0 then '-' else '') +
Totext ( Hours , '00' ) + ':' +
Totext ( Abs(Minutes) , '00' )

//TimeStringToMinutes
Function (StringVar TimeString)
Val (TimeString ) *60
+ (Val (Right (TimeString,2)) * (if TimeString startswith '-' then -1 else 1) )

To use one of these functions you first have to add them as “Report Custom Functions”. You will find these within the formula editor, just above the list of formulas in the side panel. You right click on the category, select NEW and give the new function a name. Then you paste in one of the functions above, save and close. Once they are saved you can edit any formula field to confirm that it is in the function list. There should be a new category of functions called “Custom Functions” and the new function should be listed.

These new functions can be used in any formula. The first needs a number argument (in seconds). The second needs a string formatted as a positive or negative time string (hh:mm or -hh:mm).  For example:

TimeStringtoMinutes ( {String.Field} )

SecondsToTimeSTring ({Number.Field})

Just remember that when you add a custom function like this it adds the function to the one report that is open. If you want to use it in other reports you have to create the function in each of those reports. The only way to make a function available to all reports in your environment is to compile the function as a DLL and place it in one of Crystal’s library folders. These are called a UFLs or User Function Libraries. There are several of these libraries for sale and some that are free. These are listed and explained in my annual review of UFLs.

I recently wrote about a method for sharing an array between a main report and a subreport. But that method is only practical under certain conditions. Now, Ido Millet of Millet Software has given us another method by adding three functions to his “CUT Light” UFL. These functions allow you to store and retrieve “key-value pairs” in memory. You store them using one function and retrieve them using another. The key-value pairs can be stored and retrieved anywhere within a report including within any subreports.

The advantage of this method is that it eliminates the slow looping logic you need to search for a value in a large array. And while Crystal arrays can hold a maximum of 1000 elements, there is no limit to the number of key-value pairs you can store.

The functions are called:

• uflLookupAddEntry()
• uflLookupGetEntry()
• uflLookupResetEntries()

The first function adds a key pair, the second retrieves the value based on the key, and the third is used to clear the memory space at the beginning of each report run. I did a test with 2000 key-pairs and found it very fast.

I recently learned how to fool MAS 90 into running a custom report based on a SQL command. This might only apply to older versions but I thought it was worth sharing just in case.

I created a report for a customer based on a command. It ran fine within Crystal but when added as a custom report in the MAS custom reports folder it generated an error.  We learned that MAS does a conversion step with custom reports and has to verify that all the tables exist. Our command obviously wouldn’t be found as a table so the conversion would fail.

After some web searching I found an old forum thread that described a workaround for using external tables in a custom report.  A few experiments confirmed that the same technique works for reports based on a SQL command.

The steps were to:
1) Use a similar report that uses only standard tables, and let SAGE do the conversion step on that report.
2) Open the real report (the one that uses a command) and go to File > Summary Info.
3) Modify the “keywords” section to say “Converted to version 4.40” (or your version) and save the real report.
4) Replace the first report that Sage converted with the real one.

Apparently SAGE marks the converted reports in the keywords section and skips that step if it has been done. So by adding that phrase we cause SAGE to skip the conversion, and the report seems to run fine.

My customer is using an MAS 90 4.4 which was replaced with 4.5 in 2012, but maybe this will help someone else. And if this works for you in a newer version, please let me know.