Using Global Constants

One item that is very important, but I rarely see addressed, is the different ways to create global constants. A global constant is a variable that has a fixed value for the entire duration of the report. It stores a common number/string/date that can be shared among all the formulas. For example, Crystal Reports gives you the constant crPI which stores the value PI as 3.1415… Your reports might need to create a fixed value that can be shared among all formulas. For example, you could store the state sales tax rate or the current monetary exchange rate between two currencies. There are different methods of storing global constants, and as you might expect, each has its own benefits and drawbacks.

The first way to store a constant is by using parameter fields. When the report opens, the user is prompted to type in a value and that value is used throughout the report. Since the user is only prompted to enter a new value when the report opens, the value stays the same while the report runs. The only problem with this approach is that if the user doesn’t know what the value should be, then this might prevent them from getting accurate report output. For example, it may not be a good idea to prompt the user for the number of sales offices within the company unless that is common knowledge. You need to make sure that what you are asking the user for is something that they are familiar with. Another consideration for parameters is that since the user gets prompted to enter it every time they refresh the report, it should be used for data that changes frequently.

The second way to store a constant is within a field in the database table. I’ve worked on many applications where we use a table strictly for the purpose of saving setup values that the application needs to run. These fields can save options such as the company address and contact information (it seems like companies are always merging with another company). You can create an Options table that uses one field for each option needed. It has one row of data that stores the current value for each field. Referencing a value in your report formulas is a simple matter of referencing a field from this table just as you would a normal data field.

Storing constants in a database has two concerns. The first is that there needs to be a way to change the values when necessary. You’ll probably need a programmer to write a user interface to the Options table. This method is best used for values that don’t change very often.

The third method of storing constants is putting them in a formula field. The purpose of a formula is to perform a calculation and return a value. But who says you always need to perform a complex calculation? You can have the formula that just always returns the same value. For example, the following formula returns a sales tax rate of 7.75%.

//California sales tax is currently 7.75%
Formula := 0.0775;

This is nice and simple and it does the job. Using a formula saves you the trouble of creating an additional table in the database. One thing to be aware of is that since this value is stored within the report, you should only use this method for values that rarely change. Updating the report and redistributing it can be a hassle if you have to do it frequently.

Another limitation to using formulas is that they can only be shared within a single report. If another report needs the same value then you have to copy that formula to the additional report. When the time comes to change the value, then you’ll have to remember which report uses it and modify each report individually. Storing constants in a database table doesn’t have this drawback because its data is in a central location. Making the change in the database table guarantees that each report is automatically updated.

Table 7-2 gives you a summary of each method for storing global constants as well as some guidelines to follow when doing so.

Table 7-2 Methods and guidelines for storing constants.