Contributed by Allan McMordie, BOCP. When Allan isn't creating world-class Crystal Reports or teaching, he rescues errant hikers off the mountains surrounding Vancouver Harbour with North Shore Rescue.

Shorten Development Time in Crystal with this Formatting Property

Back in Crystal Reports version 9, with very little fanfare, a powerful property was added to the Common tab of the Format Editor of report objects. It is the Display String Property.

With its conditional formatting button, you can take a database value and convert it to a string to display on the report. While this does not do anything that you could not do with a formula, it can save you hours of development time.

Here are two examples that you can use immediately without any changes to the code:

Conversion of a field containing seconds to the format HH:MM:SS
Say that you have a call center database that has many fields like Length of Call and Agent Logged-In Time and these fields are all numeric containing the number of seconds the event lasted.  Instead of creating a different formula for each field to convert the number to the string in the HH:MM:SS format, you can paste the following formula into the Display String.  No editing or changes to the syntax is required.  The biggest advantage to this method, besides its ease of use, is that the field remains as a numeric field on the report so that it can be summarized and used in a chart.

The Display String Property is also available in the Format Editor of summary fields used in Cross Tab objects.  This is the only way you will be able to display a summarized field containing seconds in the HH:MM:SS format.

Local numberVar TimeInSeconds := Round(CurrentFieldValue , 0);

Local numberVar hours := Truncate (TimeInSeconds / 3600, 0 );
Local numberVar minutes := Truncate ((Remainder (TimeInSeconds,3600) / 60 ),0 );
Local numberVar seconds := Remainder (TimeInSeconds,60);

ToText(hours,"00")& ":" & ToText(minutes,"00")& ":" & ToText(seconds,"00")

Conversion of the two letter State Code to the full State Name
There are many ways to solve this problem but this is quick and easy, and easy to reuse.  It may give you some ideas for your own use of the Display String property. 
The example below uses the Xtreme sample database in Crystal Reports, an ODBC database that is automatically installed with most versions of Crystal Reports.  The Region field contains the two letter codes for the US and Canadian States and Provinces and there is no look-up table in the database that gives the full name.  Paste this formula into the Display String property of the Region field and you will get the full names.

Select CurrentFieldValue
//   United States
Case "AL" : "Alabama"
Case "AK" : "Alaska"
Case "AS" : "American Samoa"
Case "AZ" : "Arizona"
Case "AR" : "Arkansas"
Case "CA" : "California"
Case "CO" : "Colorado"
Case "CT" : "Connecticut"
Case "DE" : "Delaware"
Case "DC" : "District Of Columbia"
Case "FM" : "Federated States Of Micronesia"
Case "FL" : "Florida"
Case "GA" : "Georgia"
Case "GU" : "Guam"
Case "HI" : "Hawaii"
Case "ID" : "Idaho"
Case "IL" : "Illinois"
Case "IN" : "Indiana"
Case "IA" : "Iowa"
Case "KS" : "Kansas"
Case "KY" : "Kentucky"
Case "LA" : "Louisiana"
Case "ME" : "Maine"
Case "MH" : "Marshall Islands"
Case "MD" : "Maryland"
Case "MA" : "Massachusetts"
Case "MI" : "Michigan"
Case "MN" : "Minnesota"
Case "MS" : "Mississippi"
Case "MO" : "Missouri"
Case "MT" : "Montana"
Case "NE" : "Nebraska"
Case "NV" : "Nevada"
Case "NH" : "New Hampshire"
Case "NJ" : "New Jersey"
Case "NM" : "New Mexico"
Case "NY" : "New York"
Case "NC" : "North Carolina"
Case "ND" : "North Dakota"
Case "MP" : "Northern Mariana Islands"
Case "OH" : "Ohio"
Case "OK" : "Oklahoma"
Case "OR" : "Oregon"
Case "PW" : "Palau"
Case "PA" : "Pennsylvania"
Case "PR" : "Puerto Rico"
Case "RI" : "Rhode Island"
Case "SC" : "South Carolina"
Case "SD" : "South Dakota"
Case "TN" : "Tennessee"
Case "TX" : "Texas"
Case "UT" : "Utah"
Case "VT" : "Vermont"
Case "VI" : "Virgin Islands"
Case "VA" : "Virginia"
Case "WA" : "Washington"
Case "WV" : "West Virginia"
Case "WI" : "Wisconsin"
Case "WY" : "Wyoming"

//   Canada
Case "AB" : "Alberta"
Case "BC" : "British Columbia"
Case "MB" : "Manitoba"
Case "NB" : "New Brunswick"
Case "NL" : "Newfoundland & Labrador"
Case "NT" : "Northwest Territories"
Case "NS" : "Nova Scotia"
Case "NU" : "Nunavut"
Case "ON" : "Ontario"
Case "PE" : "Prince Edward Island"
Case "QC" : "Quebec"
Case "SK" : "Saskatchewan"
Case "YT" : "Yukon"

Default : "Unknown Province or State"

What I do is save these Display String formulas in a text file that I call the poor man’s formula repository. (Editor’s Note:  Business Objects Enterprise/Edge includes a true repository that can store, share, and automatically update formulas such as these across all of your Crystal Reports). I can then just Copy and Paste the code from there into my report.
What is really powerful is that the Display String Property is available when you multi-select fields on a report.  This means you can put all of your data fields onto the report canvas, multi-select them all and with one Copy and Paste, convert them to the desired format.  I think you will find that it is a real time saver.

Tell us what you thought about this article at cnenews@cne.com.