Crystal Reports Formulas – All About Reports

Delimited Crystal Reports Parameter Values

We have a Ripplestone client that has a report with a string value parameter that had values that were delimited so it could be any of the listed values. This was then used within the Selection with a IN clause.

The values were surrounded with double quotes and then had brackets around the quoted values. Below is an example:

["40","41"."43","44","45"]

The problem was that the double quotes would not work within Ripplestone since the double quotes would not work with the HTML Dropdown Box. All that would be returned in the HTML would be the bracket (“[“) and nothing else.

We found with some testing that we could remove the Brackets AND the double quotes and the report would still work. Below is the delimited list without the brackets and the double quotes.

40, 41, 42, 43, 44, 45

This worked in both the Crystal Reports designer and also when published to Ripplestone.

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Converting Numbers to Dates in Crystal Reports

While working with a Ripplestone client today that had a Crystal Reports report that had data that was a date but the database stored the date as a number in the format YYYYMMDD (20090924 for example). They wanted the date to be displayed in Ripplestone as MM/DD/YYYY (09/24/2009).

In their copy of Crystal Reports Designer they had changed the default number format to only display the number with no formatting. They then used the following formula to convert the number into a date format:

Mid (ToText ({Database Field}), 5,2) + “/” + Right ( ToText({Database Field}),2) + “/” + Left (ToText ({Database Field}), 4)

This worked great when the number was 20090924, the formula split the number into the following 3 numbers 09, 24 and 2009 and then created the date by adding the “/” between the numbers.

The problem started once the report was published to Ripplestone and run. The dates on the report were displaying as 90/00/20,0

After a lot of looking at the formula we discovered that the default number for the Crystal Reports Runtime Engine is to add commas and 2 decimals to the number, so that it was displaying as 20,090,924.00

When this was run with the formula, the 3 number were 90, 00 and 20,0.

To fix this problem we needed to remove the formatting from the number before the Mid, Right and Left functions were applied. To remove the formatting of the number I added extra attributes to the ToText function. The first attribute was the “#” as the second value. This removes the commas from the number. The next attribute was a zero (0) as the third value. This will set the decimals to be zero and remove the “.00” from the end of the number. The revised formula is below, with the changes highlighted in red.

Mid (ToText ({Database Field}, “#”, 0), 5,2) + “/” + Right ( ToText({Database Field}, “#”, 0),2) + “/” + Left (ToText ({Database Field}, “#”, 0), 4)

This allowed the number to be formatted correct and when the report was run or scheduled from Ripplestone the dates are displayed in the correct format.