• I Didn't Know... - your Crystal knowledge resource. Look for tips and tricks from George Peck's Crystal Reports Professional Results and from you (when you send them in), our subscribers.
• User Group Forum. - Rocky Mountain Oracle Users Group (RMOUG) Training Days slated for February 15 & 16. George Peck compares Crystal and Oracle reporting tools. See the News section for more.
• Crystal Reports Server/Crystal Enterprise Fast Lane - Looking for a FAST one-source, one-stop way to get leading edge Enterprise Reporting in place in your organization in one week? Hurry to our Promotions for all the details.
• Online Learning: CE Administration Tips and Pick & Choose - The December 2005 and January 2006 sessions were very well attended and received! Crystal Enterprise Administration Tips, is scheduled for February 21, 2006. Pick & Choose custom training subjects will be available online every quarter. See our Promotions for more information and to register.

... how to control rounding and trailing zeros.
"Out of the box", Crystal Reports rounds and formats number fields using the number of decimal places specified in your Windows Control Panel’s Regional Settings area (typically two decimal places for standard US Windows installations). While you can select a different overall default from the Fields tab of the File -> Options dialog box, it will still be a set number of decimal places that will be used as a default when formatting number and currency fields.

This caused a problem for one of our clients who needed to display numeric values with the exact number of decimal places that the number actually was stored with. For example, if the actual field was 1, they wanted the value displayed as 1 instead of 1.00. And, if the field was .125, they wanted the value displayed that way instead of .13. The problem was, these various decimal precisions were contained in the same field and changed from record to record. Thusly, they couldn’t manually set the number of decimal places that would properly display the value’s exact precision.

To solve this problem, enter the following formula in the Conditional Format Formula Editor for both the Decimals and Rounding properties on the Number tab of the field’s Custom Style dialog box from the Format Editor:

If CurrentFieldValue = Int(CurrentFieldValue)
Then 0
Else
If
CurrentFieldValue * 10 = Int(CurrentFieldValue * 10)
Then 1
Else
If
CurrentFieldValue * 100 = Int(CurrentFieldValue * 100)
Then 2
Else
If
CurrentFieldValue * 1000 = Int(CurrentFieldValue * 1000)
Then 3
Else
If
CurrentFieldValue * 10000 = Int(CurrentFieldValue * 10000)
Then 4
Else
If
CurrentFieldValue * 100000 = Int(CurrentFieldValue * 100000)
Then 5
Else
If
CurrentFieldValue * 1000000 = Int(CurrentFieldValue * 1000000)
Then 6
Else
DefaultAttribute

This conditional formula will automatically round and display the precise number of decimal places, based on the actual decimal precision of the value displayed in the field. This formula should work properly for up to six decimal places, using the manual setting in the Format Editor for any finer precision.

... the best way to troubleshoot database performance problems.
Larger corporations and organizations are moving mainframe systems to SQL database-based systems on a regular basis. As such, industry-standard databases, such as Microsoft SQL Server and Oracle, are being used to host larger sets of data. It's not uncommon to find databases that contain multimillion-record tables.

With these increasingly large databases comes the potential for slower reports. Common sense dictates that the larger the amount of data that must be selected, formatted, calculated, and so on, the longer the period of time required to run the report. When reports run slowly, there are some simple steps you can take to determine where the performance bottleneck is. And, depending on where the bottleneck is, there are some steps (sometimes simple, sometimes not) that can be taken to improve performance.

Determine Where the Problem Exists
The first step in evaluating a slow report is determining where the performance problem comes from. The Crystal Reports processing model can be broken down into two very high-level steps: the database query step and the report processing step.

• The database query step This is part of the overall report process with Crystal Reports sends a SQL query to the database for processing. The database server then performs various processes, such as optimizing the query, running it against its local database, and returning the resulting set of data back to Crystal Reports.
• The report processing step Once the set of data (known as the result set) begins to arrive from the database server, Crystal Reports must conduct its own set of report processes. These can include running secondary record selection that the database server couldn't perform, grouping records, performing object and report section formatting, calculating formulas and totals, and so on.