Recently a customer needed to group records using part of a code field. The code field had alpha characters followed by a series of numbers. They wanted to to strip off any numbers on the end and group using only the initial string portion of the value. In the past I have done things like this using a FOR loop to check each character. This time another idea popped into my head. If you reversed the string so it started with the numbers you could put it inside the VAL() function. This would convert the numeric portion to a number and ignore the string characters that followed. You could then check the length of that number and know how many characters to take off the end of the value. To get the length of the number you would have to turn it back into a string and then use the Length function. So the calculation would nest four functions like this:

Length( Totext( Val( strReverse( {table.field} ) ) ,0, "" ) )

The only glitch was if the string ended with a zero, like “XXX1230″. In those cases the VAL() would ignore the zero (true numbers don’t start with a zero) and then my length would be off by one. To fix that I appended a “1″ to the end of each string before I ran it through the calculation. That way the value would never end with a zero.

Last, I created a local variables so that a user would only have to put their field name in on place. The end result looks like this:

//add a one to deal with a final zero
Local StringVar x := {Customer.Postal Code} & '1';
//calc the # of digits to remove
Local NumberVar y := Length(Totext(Val(strReverse(x)),0,''));
//if it is all number characters return a blank.
if y = length(x) then ""
// otherwise reduce the length by y positions.
else x [ 1 to length(x)- y ]


I always mention case sensitivity in my Crystal Reports classes. My normal comment is that CR formula comparisons are case sensitive with the exception of the selection formula, which is usually NOT case sensitive.  I even wrote about this here, last month.  So I was surprised last week in my Advanced class when our formulas ignored all case differences. I thought this might be a recent change so I went home and tested reports back to v8.5. None of the comparisons were case sensitive.

So I did a quick Google search and found a 10 year old forum thread discussing case sensitivity. One short comment in that thread explained the difference. And guess who wrote that comment and promptly forgot about it (Doh!).

So the answer is that there is a setting in Continue Reading »
Case sensitivity in Crystal Reports – revisited.

For the past few days I have been helping a customer develop a GANTT style chart in Crystal Reports.  The challenge was that Crystal’s normal Gantt chart doesn’t allow multiple bar segments on the same row.  So I was showing them how to create a GANTT chart using formulas and specially formatted summary fields. This method requires formatting dozens of small fields with the same color condition.

I selected an initial color but was trying to think of the most efficient way to change the color in all the condition formulas on the fly. Before I had an answer the customer showed me a method I had not seen before.  He created a single formula that returned Continue Reading »
Controlling the color of many objects from one formula

Say you have several columns of numbers that get added together like Price, Tax and Shipping. At the end of a customer group you would have three subtotals. You might decide to write a formula that adds those three subtotals together to get the combined total for the customer. It would work fine but there is usually a more efficient way to do this. Instead of writing a formula that combines the subtotals I would write a formula that combines the fields at the detail level. It would look like this: Continue Reading »
A total of a formula instead of a formula of totals.

When you specify field values in Crystal Reports formulas do you have to match the case?  Well, it depends on where you are. Take this example:

{Name} = “Joe”

It would only be true when the first letter was upper case and the other two were lower case.    So what if you have values in your data that use different case patterns?  You could get around the problem Continue Reading »
Case sensitivity in Crystal Reports

Yesterday I was asked if there was a simple way to group records into Spring, Summer, Fall and Winter.  There are several ways but I think this is the simplest.  I convert the month and day into a number by multiplying the month by 100 and then adding the day number.  This gives every day of the year a sortable value from 101 to 1231. Then you can use that value in a formula like this:

Local NumberVar x := Month({Orders.Order Date}) *100 + Day ({Orders.Order Date});
If x < 320 then "Winter" else
If x < 621 then "Spring" else
if x < 923 then "Summer" else
if x < 1221 then "Fall" else "Winter"

Last month I wrote about a new feature of CUT Light that allows your report to calculate the distance between two points based on zip codes or lat./log. coordinates. The one challenge I found was that the conversion of zip codes to long./lat. relied on a web query and therefore had a daily quota limit. But since then Millet Software has added a local conversion table which allows you to bypass the web query.  This makes it much faster and removes the limit.  The included data is as of the 2010 census and is limited to 5 digit zip codes, but for most applications it is probably still a better option than the web query.

You can read this page for a more complete list of what CUT Light can do.

Here is a trap in Crystal when it comes to using the selection formula.  Say you have two fields A and B. You want to include all records where either A or B is equal to X. Your selection formula would look like this:

{A} = “X” or {B} = “X”

But what happens if A is null on a record where B is equal to X? Null values cause CR formulas to stop working, so CR would stop processing that formula before it ever got around to looking at B. CR would skip that record. But not if the the formula was Continue Reading »
Null values and the selection formula

Millet software has recently added some new features to the CUT Light UFL.  Cut Light is one of several DLLs that you can install to let your Crystal Reports formulas do all sorts of interesting things.  For example you can have formulas that carry a value from one report to another, launch an application, run a batch file or execute a SQL statement.   You can read this page for a more complete list of what they can do, and for more information about CUT Light.

A few months ago Millet Software released version 5.2 of CUT Light with a handful of new features.  The one that got my attention is the ability to calculate distances between 2 zip codes or between a pair of Latitude and Longitude coordinates.  It can even convert zip codes into coordinates(see note below).  The calculated distances are Continue Reading »
CUT Light UFL provides distance calculation

The Distinct Count summary function in Crystal Reports is pretty handy.  It allows you to count the number of unique values in a column.  It can eliminate duplicates from the count, even when the duplicates are NOT consecutive.

One of my customers wanted to do the same type of summary in an Excel spreadsheet.  We were both surprised that there was no function like this in Excel.  But after a few Google searches I came up with a workaround that lets an Excel formula do the same thing as the Distinct Count summary function in Crystal:

=SUMPRODUCT((A1:A99<>"")/COUNTIF(A1:A99,A1:A99&""))

You replace the three sample ranges “A1: A99″ with  whatever your data range is.  I am not sure I could explain why it works, but it does.