If you need to pull a single character out of a character string, or a single item out of an array, you can use a subscript to identify the position you want like this:

{Customer.Customer Name} [3]

This example starts counting from the left to identify the third character or element.  Over the years I have had people tell me you could use a negative number Continue Reading »
Using negative subscripts

I recently was rereading some Tek-Tips.com FAQs and spotted a FAQ by synapsevampire that I didn’t remember.  It had to do with creating a combined list of detail values that can print as a single string.  I have a formula for this on my site, but it happens WhilePrintingRecords, which means you can only display the combined values at the bottom of the report.  With this method you populate an array during the WhileReadingRecords step of report processing.  Because this array is complete before printing starts it can be Continue Reading »
Building arrays WhileReadingRecords

If you are using a dual monitor or changing your monitor resolution, it is possible to ‘lose’ your Formula Editor.  This is because the window tries to remember where it was last positioned, and it is possible that the last coordinates are now outside your current monitor’s visible area.  You have to set the coordinates of the Formula Editor window to be within your current view.

To do this you will have to Continue Reading »
Formula editor window disappears

Several of my customers have had docking problems and I have always known that the solution was in the registry, but because of the way the registry entries are named, it has been hit or miss to figure out the solution. So when I found some articles in the SAP/BO KB for solving these problems I decided to share them. They are all very similar solutions but there are some differences in the details.

Versions 11 and 12:
If a window in the formula editor won’t dock, start with the simple option. First right-click in the middle of the window and make sure that “Allow Docking” is checked. Then double click on the window title bar and see if that puts it into place. If this doesn’t work you will have to go into the registry, which is Continue Reading »
Formula field tree that won’t dock

When you want to set the color of an object in a CR formula you can pick from one of the 15 default color words like CRRed or CRYellow. If you want an unlisted color you have to give the formula the three RGB values for the desired color.  For example Color ( 238 , 130 , 238) gives you violet.

One way find these values is using the color selector on the toolbar.  Drop down the selector and click “more’ to get what I call the “smear palette”.  This lets you click anywhere within a rainbow-like area and see the matching 3 numbers for the exact shade where you are clicking.  But what if you aren’t sure which flavor of ‘orange’ is really your basic orange?

A second approach is to go to a site like this one which maintains a color chart by NAME. That way, if you know that you want “dusty rose” you can just look up the name and get the numbers. This chart shows the  standard orange to be color (255 , 165 , 0).  Now you might think that color naming is subjective, but I checked several other charts and didn’t find any discrepancies.  For instance, all made the same exact distinction between “Bisque” (255/228/196) and “Blanched Almond” (255/235/205) although these colors are almost indistinguishable (at least to me).

Of the several sites that show colors with names, the list linked above has the longest list.  For other examples you can Google “RGB to color name”.

One of my customers recently needed to change a date into text for contract wording.  That meant changing 9/1/2009 into  “this 1st day of September, 2009″.  Most of it was simple but the tricky part was adding the “ordinal suffix” (as in 1st, 2nd, 3rd, etc).  So I did a bit of playing around and came up with this:

WhileReadingRecords;
NumberVar DayIn := Day (PrintDate);
"This "
& Totext (DayIn , 0 )
& (if DayIn in 4 to 20 then 'th' else
if remainder (DayIn , 10) = 1 then 'st' else
if remainder (DayIn , 10) = 2 then 'nd' else
if remainder (DayIn , 10) = 3 then 'rd' else 'th')
& " day of "
& Totext (PrintDate , "MMMM, yyyy")

Note that there are several formulas that can do ordinal calculations for larger numbers, but this simple one works for dates, since day numbers are never bigger than 31.

I found some clever SQL that allows you to strip the Time off of a DateTime in a SQL Server Command.  This has several uses but it makes it simple to use normal Date parameters with DateTime fields and not having to worry about losing the last day due to the times being after midnight.    The SQL would look like this:

DateAdd( day, datediff( day , 0 , YourDateTimeValue) , 0)

One link that describes this method also adds some common date calculations in SQL using similar calculations.

If you need some guidance when it comes to using SQL expressions or commands in your reports, you should get my Expert’t Guide to SQL Expressions, Options and Commands.

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.