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

Percentages in Crystal behave slightly differently than they do in Excel.  Say you calculate 13/21 and get .619.   In Excel you can click the percent symbol on the toolbar and get the value to show as  61.9%.  And if you multiply another value by this you will still by multiplying by .619.

In Crystal, clicking on the % on the toolbar does not move the decimal over for display.  So if you have the same calculation of .619 and you click the percent symbol it will simply add the symbol, showing as .619%.   If you want the displayed value to shift the decimal you have to Continue Reading »
Percentages in Crystal Reports vs Excel

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