Appendix E: Crystal Reports Built-in Functions

Appendix E - Crystal Reports Built-in Functions
Mastering Crystal Reports 9
by Cate McCoy and Gord Maric
Sybex 2003

Control Functions

There are several categories of functions that act above and beyond the actual data in a metadata way. These functions act on the report as a whole and often affect the processing and result of data.

Evaluation Time

Not every built-in function can be used at every point in report processing. In fact, Crystal’s report-processing model governs the order in which formulas execute (see Chapter 14, “The Report Engine Processing Model,” for more details). There are three passes through the data in the processing model, and if you use a function at the wrong point, you’ll get a general error message that says something like “The function cannot be used because it must be evaluated later.” For instance, you might get this message if you tried to use a function that is evaluated in the second pass in a record-selection formula because record-selection processing is a first-pass activity.

Crystal Reports handles the evaluation time of Report objects automatically. However, you may use a formula in the wrong place or get unexpected results because of the evaluation time mode. Operations such as record selection (which happen on the first pass through the data) cannot contain any functions that are not available until the second pass through the data. This includes the following special fields and functions: GroupNumber, PageNumber, RecordNumber, Previous, and Next. In addition, you cannot create running total fields using any functions that are evaluated on the second pass through the data.

Several built-in statements allow you to control when your formula executes. Add these statements to the beginning of your formula to control when the formula will run:

BeforeReadingRecords Use this statement to process a formula when constants are being evaluated; it cannot combine with database fields. In the example here, a global variable is set; since it doesn’t involve database fields, it can be processed before data is read from the data source:

BeforeReadingRecords; Global numberVar annualQuota = 500000;

WhileReadingRecords This statement forces evaluation during the first pass through the data. It can be used to create a formula field to create a second group on a field that is already used in a group in the report. First create the field with following formula and then create a group on the formula field:

WhileReadingRecords; {Resorts.Country};

WhileReadingRecords cannot be combined with any functions that are not in the second data pass, including the following:

DistinctCount	NextIsNull	TotalPageCount
WhilePrintingRecords	GroupNumber	Average
TotalPageCount	Previous	PageNofM
Next	PageNumber	OnFirstRecord
RecordNumber	PreviousIsNull	OnLastRecord

WhilePrintingRecords Use this statement to guarantee that the formula executes as database records are being retrieved from the data source. The following formula could be used to suppress the Details section on the first page of a report:

WhilePrintingRecords; PageNumber<>1

EvaluateAfter(x) Use this function to force one formula to execute after another. X represents the name of a formula.

Print State/Print Time

Crystal precalculates several pieces of information for you and makes it available in formulas for use in any report. The following functions are evaluated at print time, which is during the second pass through the data.

DrillDownGroupLevel When drilling down through groups in a report, returns a 0 for the top-level group, a 1 for the next level down, and so on.

GroupNumber Each group has a number; use this function in selection formulas to include or exclude groups.

GroupSelection Returns a text string representing the group-selection criteria in effect for the report.

InRepeatedGroupHeader Returns a True value if a Group Header section appears on multiple pages.

IsNull({table.field}) This function checks to see if a Null value is stored in a database field. When used in a formula, it can be used by itself or in combination with other conditions. The IsNull function must be the first condition in the statement, as demonstrated in the following if-then-else statement, which prints five asterisks in the report if the resort has a five-star rating or prints nothing if the value in the database is either Null or No:

If IsNull({Resorts.FiveStarRating}) or   ®{Resorts.FiveStarRating}) = "No" Then     "" Else     "*****" Next({table.field})

When used in the Details section, returns the value of the given field in the next record.

NextIsNull({table.field}) When used in the Details section, checks to see if the field in the previous record has a Null value; returns True or False.

OnFirstRecord Use this function when formatting a report and wanting to apply or negate special formatting on the first record retrieved from the data source.

OnLastRecord Use this function when formatting a report and wanting to apply or negate special formatting on the last record retrieved from the data source. The following example prints a "Continued on next page…" message when this formula is placed in the Details section and when the current record’s ResortCode is the same as the ResortCode in the next record:

If OnLastRecord Then   //do nothing Else If {Resorts.ResortCode} = Next({Resorts.ResortCode}) Then      "Continued on next page…";

PageNofM Returns the current page and the total page in a text string separated by the word "of"; e.g., 7 of 10.

PageNumber Returns the current page number.

Previous({table.field}) When used in the Details section, returns the value of the given field in the previous record:

If Previous({table.field}) = {table.field} Then   "This value is the same as previous."  Else   "This value has changed!" PreviousIsNull({table.field})

When used in the Details section, checks to see if the given field in the previous record has a Null value; returns True or False.

RecordNumber Returns the number of the current database record being processed.

RecordSelection Returns a text string representing the record-selection criteria in effect for the report.

TotalPageCount Returns the total number of pages in a report.

Document Properties

Functions in the Document Properties category return information about the report as a whole. Each of the document properties functions maps directly to the Special Fields category of the Field Explorer.

DataDate Returns the date when the data was last retrieved from the data source.

DataTime Returns the timestamp for when the data was last retrieved from the data source.

FileAuthor Returns a field with the name of the author of the report.

FileCreationDate Returns a field with the date the report was created.

Filename Returns a field with the external filename of the report.

ModificationDate Returns the date the report was last modified.

ModificationTime Returns the timestamp for when the report was last modified.

PrintDate Returns the date the report was last printed or displayed.

PrintTime Returns the date the report was last printed or displayed.

ReportComments Returns the value of the Comments field in the document properties assigned using File > Summary Info.

ReportTitle Returns the value of the ReportTitle field in the document properties assigned using File > Summary Info.

Report Alerts

Report alerts are messages that pop up when a report first opens to indicate that a value that has been flagged has reached a threshold. For instance, if MonthlySales < 10,000, a message will pop up. Use the Report Alerts functions to determine the state of the alerts.

IsAlertEnabled(alertName) Returns True if the named alert is active, False otherwise.

IsAlertTriggered(alertName) Returns True if the named alert has been triggered on the current set of data, False otherwise.

AlertMessage(alertName) Returns the string message associated with the named alert.

Use of content on this site is expressly subject to the restrictions set forth in the Membership Agreement

Conello © 2000-2003     Feedback