• The number of ‘Work Days’ between two dates
• How to print ‘Continued on Next Page
• Use SQL expressions to improve performance
• Add Comments to your Reports
• How to create a calendar
• How to display a check-box in a report

The number of ‘Work Days’ between two dates:

Calculating the number of calendar days between two dates is fairly simple. However, if you need to calculate the number of work days, it gets a bit more difficult. Not only do you have to account for weekends, in many instances you also have to account for holidays, which change each year.

This formula does both, but it requires that you enter your list of holiday dates in a separate formula. The list should include only holidays that will occur during the work week but can include more than one year of holidays in the list.

To use this formula, simply copy it into your report and put your field names in on the second and third lines in place of the field names I have used. If your date field is a datetime format, change the format to date within the format, ie if ResignedDate is in DateTime format, enter the field as Date({table.ResignedDate}). Next put your holidays in the second formula and place this in the report header, suppressed if you wish.

Note: this formula includes in the count both the starting date and the ending date.

• Create formula called WORKINGDAYS
WhileReadingRecords;
Local DateVar Start := {StartDate}; // place your Starting Date here
Local DateVar End := {EndDate}; // place your Ending Date here
Local NumberVar Weeks;
Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays;

Weeks:= (Truncate (End - dayofWeek(End) + 1
• (Start - dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 1 +
(if DayOfWeek(Start) = 1 then -1 else 0) +
(if DayOfWeek(End) = 7 then -1 else 0);

Local NumberVar i;
For i := 1 to Count (Holidays)
do (if Holidays[i] in start to end then Hol:=Hol+1 );

Weeks + Days - Hol

• Create formula called HOLIDAYS and place this into the Report Header. (suppress this field from printing)
BeforeReadingRecords;
DateVar Array Holidays := [
Date (2003,12,25),
Date (2003,12,31)
];
0

Back to top


How to print ‘Continued on Next Page’

When a group will go beyond one page, you can print a message at the bottom of the page using the following three formulas:

• This formula goes on the Group Header:

WhilePrintingRecords;
BooleanVar Continued:= True

• This formula goes on the Group Footer:

WhilePrintingRecords;
BooleanVar Continued:= False

• This formula goes on the page footer:

WhilePrintingRecords;
BooleanVar Continued;
if Continued
then "Continued on next page"
else ""

Back to top


Use SQL expressions to improve performance

One of the best ways of boosting performance in Crystal Reports is to use SQL expressions. In favorable cases, a SQL expression can dramatically cut the time needed to refresh a report. It can also reduce network traffic and thereby improve the performance of the network as a whole. The increase in performance may not be noticeably different where the database is small, but where the database is considered large, then using SQL expressions can have a dramatic effect.
Unlike Crystal formulae, SQL expressions are executed by the database server. They can be especially beneficial when they are used in record or group selection criteria, as the following example will show.

The slow way
Where a single selection is made in the group or record selection, CR will firstly draw all records from the database then sort them to return the correct sub-set. This can be checked by, after entering in the selection criteria, go the Database menu and select Show SQL Query. Here you will see the SQL Statement. This is the SQL code that CR uses to request the records it needs from the database. The important point about this statement is that there is no WHERE clause. That means that all the records in the table will be retrieved from the server, regardless of whether they are needed for the report. It is up to CR to select the required records.

The fast way
The mechanics of this are straightforward. Start by selecting SQL Expressions in the Field Explorer. Click on the New button and enter in a name for the expression. The SQL Expression editor is similar to the Formula Editor, however instead of using either Crystal or Basic syntax, the code must be in SQL. The editor also similar in that there are the four components, the listing of available tables and fields, Functions, Operators and the Code Entry area. Assistance in entering the syntax can be obtained by double-clicking on the relevant function or operator. This will automatically add the expression into the Code area. Once the expression has been entered, save and close the editor.

Next, open up the Select Expert. Delete the existing criterion, and create a new one using the SQL expression. After you have closed the expert and refreshed the report, you should see exactly the same records as before.

However, now choose the Show SQL Query command from the Database menu. The SQL code will now have included a WHERE statement. The big difference is that the record selection will now take place on the server, and only the records which meet the criterion will travel across the network. With a small table, with a few hundred records or so, you might not notice a difference. But if the table contains tens of thousands of records, and if you only want to select a small percentage of them, the performance gains could be substantial.

The Disadvantages
The biggest disadvantage of SQL expressions is that they are database-specific. You have to be familiar with the SQL dialect and syntax used by the database server, and if you ever move the report to a different database, you might have to re-code some or all of your expressions.

In addition, SQL expressions are not available for all types of databases. Crystal Reports only supports them for what it calls 'SQL databases', such as SQL Server and Oracle, as opposed to 'PC databases' like Access. If the report is based on a PC database, you won't see an entry for SQL expressions in the Field Explorer.

Furthermore, the main benefit of SQL expressions is only available with true client/server databases, such as SQL Server, Oracle and Sybase, where the processing is performed on the back end. With file server-based SQL databases, Crystal Reports permits SQL expressions to be used, but they won't deliver the same performance gains. CR will send the correct SQL code, complete with WHERE clause, to the database, but the actual record selection will be performed by the ODBC driver or OLE DB provider on the local machine. So you don't get the benefit of reduced network traffic.

Despite these drawbacks, SQL expressions are definitely worth adding to your armoury of reporting techniques. If you are working with large tables in a true client/server environment, you will be pleasantly surprised by the improved speed of your reports. Give them a try.

Back to top


Add Comments to your Reports

Make your reports easier for yourself - and others - to understand
Have you ever used an unusual technique in a report, only to find yourself completely baffled by it when you re-visit the report a few weeks later? Or maybe you've written a clever formula, but cannot remember what the formula was all about when the time comes to modify the report.

A well-tried method of coping with this is commenting. By adding explanatory notes and comments to their programs, they provide themselves - and other programmers - with valuable reminders of what the program does and how it does it. The same technique can - and should - be applied to reports.

If your report is in any way unusual or complicated, you owe it to yourself to document it in the form of comments. This will also be helpful to others that may use your report in the future.

Where to put them?
One option is to enter your comments in the report's Document Properties box, which you get to by selecting Summary Info from the File menu. As you can see from Figure 1, this dialogue includes a Comments field, which you can use to hold any text you like. Whatever notes or comments you type there will show up in the Summary pane of the File Open dialogue (Figure 2). You, or another developer, can therefore view the comments without even having to open the report.

Figure 1: You can store short comments in the Document Properties window.

Figure 2:

The comments show up in the File Open dialogue (click the Properties button on the dialogue's toolbar to show the comments).

The problem with this approach is that the comments are not particularly easy to edit or to read. The Document Properties window only shows five short lines at a time. The File Open dialogue is more spacious, but the comments cannot be edited in that window. Also, your colleagues will have to know where to look. If they are not aware of this feature, they might never see the comments. Still, this type of commenting might be a good option for short comments if it is used consistently by all members of your team.

Comments as text objects
Another option is to add comments in the form of text objects placed on the surface of the report. To prevent them showing up in the report itself, you can either suppress them, or you can place them in a report section which is itself suppressed. In fact, you can create a (suppressed) report section for the express purpose of holding comments. Such comments will be easy to edit and their presence will be obvious to anyone working on the report in design mode.

But, again, this approach is more suited to shorter comments. If you have comments that contain more than a few lines of text, they will soon clutter up the design surface and make it much more difficult to see the important parts of the report.

Comments in formulae
A preferred approach is to create a special formula to hold the comments. The Crystal formula language supports commenting, and this is a valuable feature in its own right. It is always good practice to place comments within your formula code, to remind yourself what the formula does and how it works. Create a formula called ‘Comments’ where this will be used to hold all our report-wide comments.

Using Crystal syntax, you can add a comment to a formula simply by typing two forward slashes in the formula code. Whatever text you then type on the rest of the line will be treated as a comment (and will show up in green in the formula editor). Comments have no effect on the functioning of the formula, nor do they impact the performance of the report. If you prefer Basic syntax, just type an apostrophe instead of the two forward slashes.

A dedicated comment formula may be used to explain the overall structure of the report. They could show the report’s version history, the names and contact details of its designers, a copyright notice or any similar details that users might find useful.

Make it obvious
Once you have created the Comments formula, it will show up in the list of formulae in the Field Explorer, from where it can be accessed in the usual way. Even so, the existence of the formula might not be particularly obvious, and not everyone will think to look for it.
So, as a final step, we place our Comments formula in the report itself, along with an accompanying text object to draw attention to it. For good measure, we suggest you format the formula and text object with an eye catching font and color.

We normally put these objects near the left edge of the Report Header band, where they will be immediately visible to anyone working on the report in design mode. To prevent them showing up when the report is previewed, exported or printed, we conditionally suppress them, using a formula that always returns true (such as 1 = 1). (If we suppressed them unconditionally, they would appear in light grey in design mode, and would therefore be less obvious).

Back to top


How to create a Calendar

Use the following steps to create a formula and to format it for display as a calendar.
1. Create a formula, and copy the text below into the new formula. This formula uses Crystal’s BASIC syntax.

dim CalendarBase as date
dim Output as string
dim StartingDate as date
dim FirstDayOfMonth as number
dim DayString as string

Change {Table.YourDateTimeField} below to your DateTime field.

CalendarBase = DateValue({Table.YourDateTimeField})

DayString is used to create a header in the formula output.

DayString = "Su Mo Tu We Th Fr Sa" + chr(10) + "--------------------"

The following syntax finds out what day of the week the first day of the month is. The formula then loops through the month's data, creating a string running total of the dates and adding carriage returns every seven days.

StartingDate = DateValue(Year(CalendarBase), Month(CalendarBase), 1)
FirstDayOfMonth= DayOfWeek(StartingDate)
Do While StartingDate < DateValue(Year(DateAdd("m", 1, CalendarBase)),Month(DateAdd("m", 1, CalendarBase)),1)
if Day(StartingDate) = 1 and DayOfWeek(StartingDate) <> 7 then Output = ToText(StartingDate,"dd") + " " _
else _
if Day(StartingDate) = 1 and DayOfWeek(StartingDate) = 7 then Output = ToText(StartingDate,"dd") + chr(10) _
else _
if Day(StartingDate) <> 1 and DayOfWeek(StartingDate) <> 7 then Output = Output + ToText(StartingDate,"dd") + " " _
else _
Output = Output + ToText(StartingDate,"dd") + " " + chr(10)
StartingDate = StartingDate + 1
Loop
if FirstDayOfMonth> 1 _
then Formula = ToText(CalendarBase, "MMM/yy") + chr(10) + chr(10) + DayString + chr(10) + ReplicateString(chr(32), 3*(FirstDayOfMonth-1)) + Output _
else Formula = ToText(CalendarBase, "MMM/yy") + chr(10) + chr(10) + DayString + chr(10) + Output

2. Save and close the formula.

3. On the 'Insert' menu, click 'Group'. From the first drop-down box, click the same DateTime field as used in the formula.

4. From the 'The section will be printed:' drop-down box select 'for each month'. Click 'OK' to close the 'Insert Group' dialog box.

5. Place the formula on the report in the Group Header section.

6. Right-click the formula and select 'Format Field'.

7. Click the 'Font' tab and from the 'Font' drop-down box click a fixed width font such as 'Courier'.

8. Click the 'Common' tab and select the 'Can Grow' check box. Click 'OK' to close the 'Format Editor' dialog box.

9. Increase the width of the formula field to two inches or greater.

When you refresh the report you will see a calendar for each month.

Back to top


How to display a check-box in a report

1. Open Crystal Reports, and from 'Insert', select 'Field Object'. This launches the 'Field Explorer'.

2. Select 'Formula Fields', and click the 'New' icon.

3. Type a name, such as CheckBox, and click 'OK'. This launches the formula editor.

4. Create a formula similar to the following:

// (xxx) represents the character you want to display

CHR(xxx)

For example:

CHR(254) displays a check box with a check mark in the report and CHR(252) only displays the checkmark without the box in the report.

// CHR(254) - displays a box with a check mark
// CHR(253) - displays a box with an X
// CHR(111) - displays an empty box
// CHR(252) - displays a check mark only
// CHR(251) - displays an X only

5. Insert the formula into the report.

6. Right-click the @CheckBox formula field, and select 'Font'.

7. From 'Font', select 'Wingdings', and click 'OK'.

You should see a checkbox in the report.

If you have found that any of these formulas have been helpful or have a formula that you would like to contribute to this page, please let us know via contactus@quintexservices.com.au


Back to top