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

This is a follow-up to an earlier post about reading XML File with Crystal Reports.  One of the main things I mentioned at the time was the lack of documentation.  I have since found the following articles that have helped a great deal:

Structuring XML Data for the Crystal Reports XI Native XML Driver

Using ODBC XML Data with Crystal Reports (version 10 and prior)

If these articles are ever moved you can search the SAP Support site and it should bring them up:

Another thing I mentioned was that out of three XML connection methods offered by CR, only one could read both the XML and XSD files.  If you don’t have both the XML and the XSD then you get either:

1) the data types but no data, or

2) the data with all fields being character data.

But what I missed is that instead of a separate XSD file you can embed the entire XSD schema inside the XML file.  One of my readers, Guy Francoeur, was nice enough to point this out and even sent me an example.  I have since found that this is called an “Inline” schema.  So I used Guy’s example to create my own example which you can view or download if needed.  Crystal can connect to this XML file using either the “ADO.NET (XML)” connection method or the “Database Files” connection method.  And either way you get both the records and the defined data type of each field.

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

It is very easy to change the format of numeric fields that appear on the report.   Even a numeric parameter that is placed on the report can be formatted just about any way you can imagine.  But for some reason the format of a numeric parameter “List of values” (LOV) is much more difficult to control.  Users get annoyed when a numeric parameter input for an account number or some other numeric code is formatted with commas and (in some cases) decimal points.

In most versions up through v10 there was one less-than-ideal way to control of the format of numeric Continue Reading »
Numeric parameters without commas

(For an update to this post, read my follow-up article)

When you create a connection to data in Crystal Reports there are 3 different methods to connect to XML data.  The first two (“ADO.NET (XML)” and “Database Files”) allow you to connect to a single XML (data) or XSD (structure) file on your local PC. If you connect to an XML file then you can read the data, but all the data types will be strings.  If you connect to an XSD you will see the data types specified in the XSD but Continue Reading »
Reporting on XML data

There are two “special fields” in Crystal Reports that allow you to print the date or the time of when the report file was last modified.  Any change to the design of the report should cause these fields to update. But Gordon Portanier of Crystalize in Canada just pointed out an apparent bug to me.  If a report has a subreport then the modification date and time update every time you open the report.

So say you saved 2 reports last night, one with and one without a subreport. They both had the special field “modification date” in the report header.   Then you open and run them both this morning without making any changes.  The one without a subreport will show a modification date of yesterday, which is correct.  The one with a subreport will show a modification date of today.   Every day you run a report with a subreport the modification date/time will show as if a modification was made today.  This behavior exists in all versions of CR that I tested – from v8.5 up through v12 (CR 2008)

I found an exception that I thought would lead to a workaround.  Say that Continue Reading »
Modification date/time bug

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