It is time for my annual comparison of formula function libraries. If you aren’t familiar with User Function Libraries (or UFLs) they are DLL files that add new formula functions to your Crystal Reports formula editor. With these functions your formulas can do some pretty amazing things like:

1) Carry values from today’s report to tomorrow’s report
2) Carry values from one report to another.
3) Append lines of text to an external text file.
4) Automatically copy a value to the clipboard.
5) Check the user name of the user running the report.
6) See if a file or folder exists (on your network or on the internet).
7) Rename/copy/delete a file on your hard drive or network drive.
8) Launch an application or run a batch file.
9) Execute a SQL statement (Select/Insert/Delete).
10) Send an Email using information in the report.
11) Create a table of contents or an index for your report.
12) Generate bar codes without having to install any fonts

If this sounds interesting you can read my complete comparison including a list of all the functions provided by each DLL. The five UFL providers are:

Bjarke Viksoe (U2lwin32)
Maginus Software (CRUFLMAG)
Millet Software (CUT Light)
Chelsea Tech (File Mgt, Text, Share and others)
CrystalKiwi (Export, Table of Contents)

The only product that has changed since last year is CUT Light, which can now convert numbers to Arabic text and provides more robust encoding for Barcode 128, along with some other enhancements to existing capabilities.

If you need help deploying one of these functions in a project let me know.

Crystal has a distinct count function but not a distinct sum. A distinct sum would be a sum that skips values in one field whenever there is a duplicate in a separate “key” field. It would work something like this: “only add each customer’s balance into the total once – even if a customer shows up in several different places in the report. The customer ID would be the “key” field while the balance would be the field you are totaling. It should only count the value on the first instance of the key field.

If you can group the duplicates together the solution is simpler. You can use a running total and set it to “evaluate on change of group” where the group is the key field. I teach this method in my advanced material. But a long ago student showed me a clever way to identify duplicates, even when they were scattered. I had never seen that method and couldn’t find it described anywhere else. I published it in my newsletter in 2004. When I needed to use it last week I decided to post it here.

Last week my challenge was a payroll report that showed employees grouped by department, showing pay and withholding. Pay was split by Dept but the withholding was combined. So when an employee worked in two different departments, his withholding would show up twice in the totals. I needed the totals to count the withholding once per employee, even if the employee showed up in several departments (on different pages).

To use this method you first create a running total field that is a distinct count of the “key” field. Then you create another running total, this time using variables, to total the numeric field. This formula has logic to only add the value when the first running total has changed from the record before. Any time there is a change in the first running total it must mean there is a new value for the “key” field. When the first running total doesn’t change it means that the “key” field value has appeared before. The formula I used looks like this:

WhilePrintingRecords;
NumberVar Prior;
NumberVar LYS;
if {#CustCount} = Prior + 1 //test if first record for this customer
then LYS := LYS + {Customer.Last Year's Sales};
Prior := {#CustCount}; //store current count to use for next record
LYS

If you need a Distinct Sum as a Subtotal you would reset the running total and both of the variables with each group.

I have seen other developers solve this problem by creating an array of all of the “key” values and checking each “key” value against the array before adding the numeric. This method lets Crystal handle that duplicate check so there is no reason to maintain an array.

I have written before about the challenges of reading data from an Excel, mainly because Excel doesn’t define data types for columns like you do in a data table. That article included a formula to convert a column of numbers into a column of equivalent strings.

Last week I had to do the opposite. A customer wanted to link a data table to a spreadsheet, but the linking field in the spreadsheet had numeric characters, while the data table had a true numeric column. There is nothing you can do in the Crystal linking window to get a numeric column to link to a character column, so we had to convert the Excel column from characters to numbers. Just like the previous article, you can’t fix this by formatting the column. The data type assigned to an Excel column isn’t affected by the format of the column, but only by the actual values stored in that column.

After a few experiments I discovered a solution. If you write a formula like this in Excel:

A1 + 0

The result will be a number even if the cell A1 is a character string with numeric characters. Even better, if there are any true numbers in the column, the formula will work the same for them. So we copied that formula for the entire column and that new column became our linking field.

My web site has a page for commonly used formulas.  Many have been there for years.  Formula page 9 has two versions of a formula that will convert a number of seconds into an elapsed time string. The long version has days, hours, minutes and seconds.  The shorter version is just hours and minutes.

I was using the short version in a customer’s report and we noticed that the minutes value was sometimes off by one.  After some testing we found that if the remaining seconds were between 30 and 60 the formula would always round down because the formula used a Truncate() function. The Truncate() works correctly in the long version of the formula, because you truncate down to the whole minutes and then display any remaining seconds. But since the shorter version doesn’t display seconds it is more accurate to Round () the seconds to the nearest minute.  So I have updated the short version formula on my site to use the Round () function for minutes.

So those of you who have used that formula should update your reports by changing the last Truncate to a Round, or just taking the updated formula from page 9.