Universal Time (also known as UTC/Unix time/Posix time) is a DateTime value that stores the number of seconds since 1/1/1970 at the Royal Observatory in Greenwich, England.  The values for 2009 are around 1.2 billion. One of my readers did some work to convert these values into normal DateTime values and wanted to work on adjusting for her local time zones, as well as adjusting for Daylight Saving Time.   She contributed some logic and I polished it up a bit and Continue Reading »
Converting from Universal Time (UTC)

One of my customers sent me a report with an unusual “divide by zero” error. I was going to explain to her how you prevent this error with an IF-THEN statement.  You check to make sure that the field you are dividing by is not zero, like this:

If {FieldA} = 0 then 0 else
{FieldB} / {FieldA}

But when I looked at her formula I noticed that the first line did exactly what I would have done, and it was still generating a divide by zero Continue Reading »
Formula trouble using “Integer Divide” (the backslash)

Many of you have purchased one or more 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 4 with another 30 articles. Each is illustrated with an annotated report that demonstrates how to use the technique.  As always, they cover a wide range of topics.

Some are specific advanced methods like:
How to do a fixed length text export from a Crystal Report (#115)
How to do a multi-level Bill of Materials (#118)

Some are little formatting tricks like:
How to automatically size the dotted line between two columns – like a TOC (#105)
Lines and boxes that grow or disappear based on a condition (#107-108)

Some are general purpose lessons like:
Different ways to use “feeder” formulas (#94-95)
Tips for naming formulas (#91)

Check out the full list of articles that are in Volume 4. 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 – a bargain even if only one article helps you solve a problem.

One of my readers tipped me off to a web site that has a handy Crystal Reports DLL that you can download for free.  This DLL adds several custom functions to your formula editor that allow your formulas to do all sorts of new things (and some I don’t recommend that you try).

There are about 40 functions in the DLL that allow your formulas to interact with the operating system. Here are some examples: Continue Reading »
Handy custom functions in a free DLL

Here is a technique I have named the “wormhole”, because it lets Crystal go back in time.  It allows you to take one or several database values from the last record of a report, and then display them or use them in formulas that occur earlier in the report.  Likewise, it can take the last record of a group and print it’s values anywhere in the group, including the group header.  Variables can’t do this because they can only be used ‘downstream’, and subreports can slow things down significantly, so this method has some advantages.  Your report must be sorted, and the values you want to move have to be database values or formulas that can be totaled. Unfortunately, PrintTime formulas can’t be moved using this method. For that you would need a subreport.

Lets say that you have 300 orders sorted by a DateTime field in ascending order.  You are not allowed to change the sort of the report.  But, you need to start the first page of the report with the Date, Amount and Shipping method of the last order on the last page:

First you create the WormHole formula.  You do this by Continue Reading »
Moving values backward in a report

Crystal will not create a group in a report unless there is at least one record for that group in the report’s dataset.  This also affects Cross-tabs and Charts, where you won’t see a row, column or bar unless there is a corresponding record.  But, it is not unusual for users to request that missing groups show up with a zero.  There are several ways to do this and the approach to use depends on a number of factors. Everyone’s first thought is to use an outer join, but I have found that the outer join solution isn’t compatible with the filtering and grouping required in most reports.

One of my favorite workarounds is the “padding” method. Continue Reading »
Reporting on data that isn’t there Part 1 (Padding)

I recently reread a newsletter article I wrote a few years back and was reminded of a trick that I had forgotten. The article was about ranking groups using a formula that contains summary functions. It is simple to summarize most formulas in a report (sum, count, maximum, etc). And once you have a summary field it is usually a simple matter to use that summary field to rank the groups. Crystal calls this a Group Sort (or a TopN in older versions).

The challenge comes if the formula itself contains a summary operation. Then Crystal will not let you do a summary of that formula, and then there is no way to rank the groups based on that formula. However the article I wrote points out two very important exceptions to this rule:  You can do a summary of these formulas either inside a cross-tab or inside a chart.

So take a common formula that calculates a profit margin percentage: Continue Reading »
Ranking based on a ratio

Some of you may have played with a technique that I have written about in the past. It is where you use a variable to create running totals within a cross-tab. These running total values can then be used within the cross-tab or even outside the cross-tab.

But I recently heard from a user who was having trouble whenever the cross-tab went over a page break. His variables would reset to zero on the new page and he thought it might be a bug in v10. So I did some testing and found that this behavior occurs in all versions, right up to CR 2008. Whenever a cross-tab goes over a page break it resets any variables it is using, even though the variable is global. The reset goes back to the value of the variable at the beginning of the split section. Usually this means a reset to zero. However in cases where the variable already has a value before the cross-tab section starts, it resets to that value.

There must be a bug or design flaw in the way the split pieces of a cross-tab are processed. Unfortunately, I could find no way to prevent the reset.

Thanks to Peter Lapic for pointing out this bug to me.

Here is a little secret that not everyone knows. The Crystal Reports formula editor has an autocomplete function that allows you to type the beginning of a function or other reserved word, and let Crystal give you choices to pick from. To invoke the autocomplete you type CTRL – Spacebar and a window will open with all of the reserved words in it. As you type the list will narrow down to the functions that start with the characters that you have typed.

So say you want to use the function InRepeatedGroupHeader. You do your CTRL – Spacebar and type “IN” and you get a choice of 10 options. But if you add the “R” there is only one available choice so that choice is entered into your formula for you.

Or if you start typing a function you can hit the CTRL – Spacebar and the same window will open showing what matches what you have typed so far.

This is available in versions 10 and later.

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.