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.

The folks at SAP have recently created an interesting sample report.  It shows you how to calculate hourly, daily, weekly, monthly and annual summaries of just about any data.  And while it is a very complex report with 400+ formulas, it is written using a technique that I call  “feeder formulas”.  These allow you to apply the report to your local data by changing just a few key formulas.   The bulk of the other formulas never use any database fields directly, but refer only to the feeder formulas which define the key date field and the amount fields in your data.

There is a trick I teach my students to help them remember Boolean (true/false) syntax.  I have them enter criteria into the select expert and then click the “Show Formula”  button to see the same criteria written in Crystal formula syntax.  So if you forget how to write a formula that does “between”, “is one of” or “not equal to” just put that into the select expert and copy the formula that it generates.

And just last week I learned something new.  The Select Expert will let you mix ranges and single values when using the “is one of” comparison.   So say I want to return 62 specific order numbers.  I want all orders numbered from 1001 to 1030, all orders numbered from 2001 to 2030 and two other individual orders numbered 2035 and 2037.  In the select expert you can put all of this in one rule by Continue Reading »
Mixing single values and ranges in the select expert

One of my customers had a dozen or so formula fields used as headings throughout a long report.  He wanted to be able to adjust the color of the font assigned to all of these objects in one place.  So we created a formula that stored three numeric values like this and put it into the report header (suppressed):

WhilePrintingRecords;
NumberVar RGB1 := 0;
NumberVar RGB2 := 64;
NumberVar RGB3 := 128;

Then we used the following as the condition formula for the font color Continue Reading »
Setting the RGB color of multiple fields at the same time

I have struggled many times while helping customers create fixed length text files from Crystal Reports. These files are standardized files used to transfer data from remote systems into centralized systems. Banks, insurance companies and government agencies often require certain business to submit data in fixed length files so they can be imported into legacy applications. The challenge has always been Crystal Reports wanting to truncate the data based on the defined page width, even though the export doesn’t actually go to a physical printer.  I recently did days of research to write an article on how to get the widest possible text export from different versions of CR.  This is published in my Expert Techniques volume 4.  Exporting to text with this method is limited in CR 2008 to 367 or 377 characters depending on your version of the export dll.  Some older versions of CR can go up to 1500 characters if you get all the right pieces in place.

But I was talking with Ido Millet about scheduling these exports when I realized that another of his tools has a very simple solution to the problem. Millet Software produces a DLL called Continue Reading »
Fixed length text exports using Cut Light

I just heard from Roberto Brum from Brazil who found a way to simplify my Formula #19.  This is a formula that you can use to find the last Friday of any month (or any other weekday).  I liked his approach so I put it side by side with my original formula for testing.   For the most part they worked exactly the same, and the few differences I found were due to my own errors in the original formula.   So, not only is it more elegant, but it is also more accurate.  So I have now posted Roberto’s formula on the page above.  Many thanks, Roberto!

I have faced this twice this month with two different customers.  In both cases we design the report to use a numeric field and the report runs fine at design time.  But when the report is run within the application it generates a formula error saying  “a number is required here”.  What is happening is that the data type at runtime is changing so that fields that were numeric at design time are seen as strings at runtime.  The source of the problem for one customer was an Excel spreadsheet data source.  Excel is notoriously flaky when it comes to data types.  The other customer had XML data where the XSD was missing at runtime  so all fields became strings at runtime.  (See this article for more on XML and XSD Files).

In both of these cases the customer didn’t have the flexibility to change what was happening at runtime, so we needed a workaround.  Fortunately there is a trick I recently learned Continue Reading »
Dealing with data types that change at runtime