One of my customers has reports scheduled to run every weekday. The rule is that all month long it should pull the dates for the current month. But once a month, at the beginning of the month, they want to run the entire previous month. So I wrote their selection formula to say “If today is the first of the month, then run LastFullMonth, otherwise run MonthToDate:

If Day (currentDate) = 1
then {DateField} in LastFullMonth
else {DateField} in MonthToDate

This worked fine for several months until we got to April.  Because the report only Continue Reading »
Calculating the first weekday of the month

Formula names can contain any characters you want, including numbers, symbols and spaces. But I just found a reason not to use a space as the first character of a formula name.

A customer recently tried to modify a formula in an old report, and was prompted to log into the repository. She sent me a screen shot of the messages, which said:

enter the name of your system (Central Management Server)

then:

Repository services are not available.

I figured that there was probably an old repository function in the formula.  So I asked to see the report and went through all the formulas.  There were no repository functions in any of the formulas.  And yet, when I right clicked on the first formula and tried to edit it – up popped the same message.  I was stumped until I noticed that the first formula name started with a space. So I switched to one of my own reports and renamed one formula so that it started with a space. When I right clicked on that formula and tried to edit it, voila!   I got the same messages. I ran the same test it in versions Continue Reading »
Don’t start your formula names with spaces.

I recently wrote about a post about controlling the color of multiple objects from one formula.   One of my newsletter readers took the idea and ran with it and designed a custom function for his repository:

========================================
Just got the ‘Underground News’. The tip on creating formulas for set colors is fantastic – never occurred to me either. Opens up all kinds of possibilities – kind of CSS for Crystal Reports. So now I can Continue Reading »
More on controlling colors from formulas

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 Continue Reading »
Stripping numbers off the end of a string

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.