I maintain a list of commonly used formulas on my site and some of these formulas go back nearly 10 years. I have started to notice that some of the older formulas can be done more simply using features in the newer versions of Crystal. So I will occasionally update a formula to simplify it.

For example, one of the first formulas I wrote converts a character date into a true date. The original worked for text stored in formats such as M/D/YY or MM/DD/YYYY. The challenge back then was determining if the month or day was 1 or 2 digits and the method I used was to look for the positions of the slashes. This job is made easier now because we can use the SPLIT () function. So I have finally updated the formula. I also added formulas that convert values in “YYYYMMDDHHMMSS” or “YYYYMMDD” formats.

If you group on a date field in Crystal Reports, the groups are always based on calendar periods. So a group by year will be by calendar year and a group by week will be by calendar week (Sunday to Saturday). If you want to group by a non-calendar period, like a fiscal year, then you have to write formulas. I have BLOG entries that show you how to group by fiscal year and fiscal quarter.

One of my former students asked me today how to group dates into weeks that aren’t calendar weeks, like their pay week that starts on a Monday and ends on a Sunday. Continue Reading »
Grouping by non-calendar week

The latest versions of Crystal Reports have a menu option in the “Report” menu called “Check Dependencies”. This is a handy little feature that allows you to see if there is anything that would prevent the report from running. For instance, I often find formulas that use a field or function that is no longer available.

How does a report end up with a missing dependency? Here are two examples: Continue Reading »
What does it mean to “Check Dependencies”

All of my students learn how to use the VAL() function, which converts numeric characters into true numerics. The VAL() function works by starting at the left of the string, reading the digits, and stopping when it hits the first alpha character. It then converts the numeric characters it has found into numbers. However, if it finds a dash before it finds an alpha character, it flips the sign of the numeric result to negative and keeps reading the digits.

The problem comes when the dash is among the numeric characters. Continue Reading »
The effect of dashes on the VAL() function

Say you have 2 group levels in your report Customer and Product. You have subtotals at the end of each group, so you have 2 levels of subtotals. What do you do if you want to have a page break after each product? Your first attempt would be to simply go into the section expert for GF2 and check “New Page After”. This works fine except for the last product in each Customer. The Customer’s subtotals would end up printing on the top of the next page, which would be a different Customer’s page. So you add Continue Reading »
Cascading page breaks (on Interior Groups)

Crystal will allow you to conditionally suppress a Page Footer. But even when the section is suppressed, the space it would have taken up is still there, reserved as blank space at the bottom of the page. This is because other features like ‘can grow’ and “keep group together” make the job of determining the page size very complex.

There is a related option for page footers called “reserve minimum space” but this only applies Continue Reading »
Conditional Page Footer

When you create a parameter field in Crystal Reports you can create a “pick list” of values so that the user can select a value rather than typing the value. If your pick list is a series of codes you can also add a second column of values to provide a description for each code. So your pick list might look like this:

A - Adjustments
C - Credits
D - Debits

The challenge comes when you want to display the user-selected values on the report. Continue Reading »
Displaying chosen paramater values with descriptions

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. )