It is usually pretty straightforward to move a report from one database to another.  You simply use the “Set Location” feature in the database menu.  However this can be a challenge when the table configuration is completely different.  And it gets very difficult if you are trying to set the location of the report from several tables to a single command, view or stored procedure.

This is because the set location feature was designed to keep all fields within their original table groupings.  This means that if you are Continue Reading »
Setting a Crystal Report to Any Datasource

Starting with v11.5 Crystal has made it easy to find all uses of a field within the formulas of a report.  Just right click on the field  (database, formula, parameter, etc) and select “Find in formulas”.   Even for those of you using older versions of Crystal there is a workaround in my Expert Techniques vol. 1.

But finding where a variable is used is a bit trickier.  Unlike fields, there is (currently) no central list of all the variables used in a report.  But I just noticed a feature that must have been in the formula editor for at least 2 years.  Open a formula and click on Continue Reading »
Finding all uses of a variable

One of my clients just sent me a formula that turns the numbers from 1 to 20 into superscript characters.    You could do the same thing with subscript characters if you wanted to, but you would need to look up all the Unicode values in a chart like this one.   Note that you must display this formula using a Unicode font (they usually say “Unicode” in the font name) or the characters will space and size inconsistently.

select  totext({Number.Field},0)
case "1" : ChrW (185)
case "2" : Chrw (178)
case "3" : Chrw (179)
case "4" : ChrW (8308)
case "5" : ChrW (8309)
case "6" : ChrW (8310)
case "7" : ChrW (8311)
case "8" : ChrW (8312)
case "9" : ChrW (8313)
case "10" : ChrW (185) + Chrw (8304)
case "11" : ChrW (185) + ChrW (185)
case "12" : Chrw (185) + Chrw (178)
case "13" : Chrw (185) + Chrw (179)
case "14" : ChrW (185) + ChrW (8308)
case "15" : ChrW (185) + ChrW (8309)
case "16" : ChrW (185) + ChrW (8310)
case "17" : ChrW (185) + ChrW (8311)
case "18" : ChrW (185) + ChrW (8312)
case "19" : ChrW (185) + ChrW (8313)
case "20" : ChrW (185) + Chrw (8304)

And thanks to Melody Mulligan of Crystal Clear Solutions for the tip.

Last week a customer called me after they did an upgrade of PeachTree Accounting.  It seems that the records in the report that used to sort were now appearing in random order.  As soon as I added the sort back in and refreshed the report, all the records disappeared.  Fortunately I had seen this before because I work quite a bit with PeachTree and PeachTree uses Btrieve as a database engine.

This week I had another Btrieve customer (not PeachTree) who wanted to add a parameter to select a part number.  We added the rule in the selection formula and, again, all the records disappeared.  Same problem same solution.

Apparently when Crystal sends a query to a Btrieve engine, Btrieve tries to ‘help out’ by Continue Reading »
Vanishing data in Btrieve

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.