I have a formula on my site that appends a series of string values from consecutive records and turns them into one long string. But if you are using version 8.5 there is a limit of 256 characters of output for any formula which limits the usefulness of this formula. But Shelly Petersen of Computer Arts, Inc. recently gave me a draft of a variation of my formula that allows v8.5 to get around this limit. It splits the string into several variables that are each under the 256 limit, and then she uses separate formulas to display each variable. She puts all of the formulas together in one large text object to show the long combined string (since text objects don’t have length limits). Here is the main formula: Continue Reading »
Apending Strings in v8.5 into one long string over 256 Chars

One of my customers asked if I could help them with a formula. The formula is found in article c2012665 in the Business Objects knowledge base and it converts a decimal number into fraction format. In other words it should convert .75 into 3/4 as a string. The formula used a clever loop but it had 3 problems:

1) If the input was an integer you would still get the fraction, but with a 0 on top.
2) If the input was between 0 and 1 (like .75) you would get the integer 0 in the output.
3) The formula was in BASIC syntax which makes it hard for many users to modify.

So I converted it to Crystal syntax Continue Reading »
Converting a decimal to a fraction

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

I find many formulas using the ToNumber() function in Crystal Reports formulas to convert numeric text into a true numeric value. Usually it is because they don’t know the advantages of using the Val() function. While ToNumber() works, it will generate an error if the string has any characters, punctuation or symbols in it. The usual solution is to test the value by saying:

If NumericText ({field}) then ToNumber ({field}) else 0

However, even this doesn’t work well if characters come after the numerics. For instance Continue Reading »
ToNumber() vs VAL():

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

One of my most used formulas is my “Business Days” formula which allows you to calculate the number of business days between any two dates. But to do this you have to store a list of the holidays for each year. One of my readers (Mike Cook of Shepherd Hardware Products) provided a formula to calculate the common US holidays - even going so far as to provide a formula to calculate Good Friday, a pretty amazing feat. Now one of my Canadian readers (Greg Fendall at net-linx Americas) has provided the Canadian version of the holiday formula. If anyone would like to provide the formula for another country I will be happy to post it.

Many of you have purchased one or both volumes in my Expert Techniques series. These contain my favorite tips and tricks in Crystal Reports, accumulated over the years. I have just put together Volume III with another 30 articles. Each is illustrated with an annotated report that demonstrates how to use the technique. Here are some examples:

Adjusting a server time to match the NEW Daylight Savings Time
Calculating Fiscal YTD and Fiscal Quarters
Two ways to do “Rolling Month” reports
How to create reports using spreadsheet data
How to be prompted to choose the reports ODBC connection (DSN) at runtime

Check out the full list of articles on my web site. Even if the article titles don’t seem to apply to your reports, I am confident that you will learn some tricks that you will find useful down the road. The price is only $19 per volume, which is a bargain even if only one article helps you solve a problem.

A customer recently sent me a report with an unusual requirement. The report had 2 groups and they needed a running total that reset at the end of each group 1. The unusual part was that the RT would be displayed only at the top and bottom of each page. The idea was to see where the total ended at the bottom of the page and then show it pick up again at that same point at the top of the next page. It sounded simple, but when we tried putting a normal running total field in both the Page Header and the Page Footer we got inconsistent results.

In most cases the value in the Page Header was not the same as the value in the Page Footer of the prior page. Continue Reading »
Side-by-side Running Totals show different results