Tips – All About Reports

Page Setup

The first is to set the No Printer option on the Page Setup dialog box. This will stop the report from looking for the selected printer each time the report is run. It also has the added bonus of making the report look better within the web viewer. Open the Page Setup by clicking on Files and then Page Setup

Figure 1 – Page Setup Dialog Box

Report Options

The report option dialog box has several options that can help the report process quicker. Open the Report Option by clicking on File and then Report Options.

Perform Grouping On Server – This works with the “Use Indexes Or Server For Speed” option to allow the server to perform much of the processing for the report. This reduces the processing and memory used by the Ripplestone server.

Use Indexes Or Server For Speed – Using this option will allow the SQL query to use the database indexes and can increase the speed when selecting large amounts of data.

Verify on First Refresh –Unchecking this option will stop the report from first checking the database to refresh the schema. This will save a trip to the database.

Verify Stored Procedure on First Refresh – Unchecking this option will stop the report from first checking the stored procedure to refresh the schema. This will save a trip to the database.

Save Data With Report – Unchecking this option will keep the file size small and will allow the report to be loaded by the Crystal Report engine quicker.

Perform Query Asynchronously – Check this box to allow the report to execute the SQL statement without requiring Ripplestone to wait for the select statement to finish.

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.