Crystal Report formula)

I. formula components
Ii. Formula syntax
Iii. Formula type
----------------------------------------------------------------
The formula consists of two key parts: component and syntax. A component is the part added to the creation formula, and the syntax is the rule followed by the Organization component.
There are several different types of formulas in Crystal Reports: Reports, formatting, selection, search, running total conditions, and alarm formulas. Most formulas in a report are report formulas and Conditional Formatting formulas.
----------------------------------------------------------------
I. formula components
Create formulas in Crystal Reports and apply them to any data table Program The creation formula is similar. You can use the following components in the formula:

Field
Example: {customer. Customer name} and {customer. sales last year}

Number
Example: 1, 2, 3.1416

Text
Example: "quantity", ":", and "your text"

Operator
Example: + (plus),/(except),-X (negative)

Operators are operations that can be used in formulas.

Function
Example: round (x), trim (X)

Calculate the average, sum, and count of function execution. Lists all available functions, including their parameters, and lists them by purpose.

Custom Functions
Example: cdfirstdayofmonth, cdstatutoryholidays

Custom functions provide a way to share and reuse the formula logic. They can be stored in the crystal repository and then added to the report. After a user-defined function is added to a report, you can use the User-Defined Function in "formula experts" when creating a formula.

Control Structure
Example: "if", "select", and "for" Loops

Group Field Value
Example: Average (condition, condlimit), sum (condition, condlimit, "condition ")

The group field values are summarized. For example, you can use the group field value to obtain the percentage of each group in the total.
Other formulas
Example: {@ grossprofit} and {@ quota}

For more information, see formula syntax.
----------------------------------------------------------------
Ii. Formula syntax
Syntax rules are used to create the correct formula. Some basic rules are as follows:

Enclose text strings in quotation marks.
Enclose the parameters in brackets (at the proper position ).
The referenced formula is identified by the leading @ symbol.
Crystal syntax and basic syntax
When creating a formula, you can choose to use the crystal or basic syntax. Almost any formula written using a certain syntax can be written using another syntax. A report can contain formulas using the crystal syntax or the formulas using the basic syntax.

The crystal syntax is included in all versions of Crystal Reports.

If you are familiar with Microsoft Visual Basic or other basic versions, you may be more familiar with basic syntax. All in all, in addition to specific extensions to process reports, the basic syntax uses Visual Basic as the model.

If you are used to the crystal syntax, you can continue to use it and benefit from the new functions, operators, and control structures generated by Visual Basic.

Note:

The formulas for record selection and group selection cannot be written in basic syntax.
Using basic syntax does not slow down the report processing process. Reports using basic syntax formulas can be run on any machine running Crystal Reports.
Using the basic syntax formula, you do not need to distribute any additional files together with the report.
Related Topics
For more information about basic syntax, see create formulas using basic syntax.

For more information about Crystal syntax, see use crystal syntax to create formulas.
----------------------------------------------------------------
Iii. Formula type
There are several different types of formulas in Crystal Reports: Reports, formatting, selection, search, running total conditions, and alarm formulas. Most formulas in a report are report formulas and Conditional Formatting formulas.

Report Formula
A report formula is a separate formula created in a report. For example, the formula for calculating the number of days between the order date and the delivery date is the report formula.

Conditional Formatting Formula
Format the formula to change the layout and design of the report, as well as the appearance of text, database fields, objects, or the entire report section. Use the "format Editor" to set the text format. To create a formatting formula, access the formula studio from the format editor. See use conditional formatting.

Selected Formula
Select the records and groups specified by the formula and restrict the records and groups that appear in the report. You can enter these formulas directly, or you can use "select experts" to specify the selected content. Crystal Reports then generates the record selection and group selection formulas. You can manually edit these formulas, but you must use the crystal syntax. See select record.

Search Formula
Search formulas help you locate data in reports. Similar to the selected formula, you do not need to enter these formulas directly, but use "search experts" to specify the search criteria. Crystal Reports generates this formula. You can manually edit these formulas, but you must use the crystal syntax.

Note: If you already know basic syntax, you only need to know a few crystal syntaxes to modify most of the selected formulas and search formulas.

Formula for running total Conditions
The run total condition formula enables you to define the conditions on which the run total is evaluated or reset. See create condition run total.

Alarm Formula
Alarm formulas help you define conditions and messages for report alarms. See about report alarms.

Common formulas for Crystal Reports:
1. Create a calculated field and add it to the report.
2. format the text on the report.
3. extract one or more parts of a text string
4. Extract the part of the date
5. Use User-Defined Functions
----------------------------------------------------------------
Formulas have multiple purposes. You can use a formula to perform special data operations.
----------------------------------------------------------------
1. Create a calculated field and add it to the report.
Calculate the price after 15% discount:

Crystal syntax example:
{Order details. Unit Price} *. 85

VB syntax example:
Formula = {order details. Unit Price} *. 85
----------------------------------------------------------------
2. format the text on the report.
Change all values in the "customer name" field to uppercase letters:

Crystal syntax example:
Uppercase ({customer. Customer name })

VB syntax example:
Formula = ucase ({customer. Customer name })
----------------------------------------------------------------
3. extract one or more parts of a text string
Extract the first letter of the customer name:

Crystal syntax example:
{Customer. Customer name} [1]

VB syntax example:
Formula = {customer. Customer name} (1)
----------------------------------------------------------------
4. Extract the part of the date:
Determine the month of order issuance:

Crystal syntax example:
Month ({Order. Order Date })

VB syntax example:
Formula = month ({Order. Order Date })
----------------------------------------------------------------
5. Use User-Defined Functions
Convert $500 from the U.S. currency to the Canadian currency:

Crystal syntax example:
Cdconvertustocanadian (500)

VB syntax example:
Formula = cdconvertustocanadian (500)

Crystal Report alert
Directory:
I. report alerts
Ii. Use report alerts
1. Create a report alert
2. edit a report alert
3. delete a report alert
4. View Report alarms
5. Reference report alarms in Formulas

----------------------------------------------------------------
I. report alerts
A report alert is a custom message created in Crystal Reports that appears when report data encounters a specific condition. A report alert can indicate the operations you will perform or information about the report data.

A report alert is created using a formula for calculating a specified condition. If the condition is true, an alarm is triggered and an alarm message is displayed. A message can be a text string or a formula with both text and report fields.

Once a report alarm is triggered, the alarm is calculated again until you refresh the report data.

Report alarms are for each report, so you need to decide when to use them and when not to use them. Report alerts are useful for identifying important information, such as sales that are higher than or below a certain threshold. And because the alarm message is created by you, it can also target your data.
----------------------------------------------------------------
Ii. Use report alerts
This section focuses on the tasks to be executed to use report alerts:

1. Create a report alert
2. edit a report alert
3. delete a report alert
4. View Report alarms
5. Reference report alarms in Formulas
----------------------------------------------
1. Create a report alert
When creating a report alert, you must complete the following three steps:

Name Alert.
Defines the conditions for triggering an alarm.
Create a message that you want to display when an alert is triggered (this step is optional ).
Create report alert
On the report menu, point to alarm and Click create or modify alarm ".
The create alarm dialog box appears.

Click new ".
The create alarm dialog box appears.

Enter the name of the new alarm in the Name box.
Enter an alarm message in the message box.
The message box allows you to enter a message that is used as the default value. If you want to display the same message each time an alarm is triggered, enter the message in the message box.

However, if you want to use formulas to customize messages with data elements, refer to the next step.

If you want to use a formula to create an alert message, click the formula button on the right of the message box.
The formula studio appears. For information about how to use the editor, see use the public editor.

Enter the alarm message formula.
For example, if you want to see the message "Country is a star sort mer" (country is the name of a specific country), you can create the following formula:

Groupname ({customer. Country}) + "is a star timer mer"

Note:

The result of the alarm message formula must be a string.
The defaultattribute function can be used to reference messages added to the message box. For example, if the message in the "message" box is "is a star timer mer", the alarm message formula can be:
Groupname ({customer. country or region}) + defaultattribute

This formula depends on the text you entered in the message box, which becomes defaultattribute.

Message condition formulas can be created using the crystal or basic syntax.
Click conditions ".
Then the formula studio appears.

Enter the alarm condition formula.
Alarm formulas can be based on repetitive records or summary fields, but cannot be based on print time fields (such as run total or print time formulas ). The alarm formula cannot contain shared variables.

If the alert formula is based on the summary field, any repetitive fields used must remain unchanged in the summary field. For example, if you group by country, region, or city, you can create the following alarm:

Sum ({customer. sales last year}, {customer. region })

In this example, your formula can reference "country/region" or "region", but cannot reference "city" or "customer name" because they are changed.

Note: alarm condition formulas can be created using the crystal or basic syntax.

If you do not want to calculate alarms, clear the Enable check box.
Otherwise, the selected status is retained.

Click OK to save the alarm.
The create alarm dialog box is displayed, and your new alarm is listed. You can see its name and status ("enabled" or "disabled ").

In the create alarm dialog box, only enabled and disabled alarms are displayed. If an alert is triggered, you can see it in the report alert dialog box.
----------------------------------------------
2. edit a report alert
On the report menu, point to alarm and Click create or modify alarm ".
In the create alarm dialog box, select the alarm to edit and click Edit ".
Tip: You can also edit an alert by double-clicking it.

In the edit alarm dialog box, make the required changes.
Click OK to save the changes.
NOTE: If an alert has been triggered, it will be deleted from the report alert dialog box when it is edited.
----------------------------------------------
3. delete a report alert
On the report menu, point to alarm and Click create or modify alarm ".
In the create alarm dialog box, select the alarm you want to delete and click Delete ".
The selected alert is deleted from the create alert dialog box.

NOTE: If an alert has been triggered, it will also be deleted from the report alert dialog box.
----------------------------------------------
4. View Report alarms
You can view the triggered report alarms in the following ways:

Refresh report data.
Select triggered alarms from the alarms submenu in the Reports menu ".
Note: If you want to view the alarm when the report data is refreshed, you must select "display alarm upon refresh" on the "Create report" tab of the "options" dialog box (this option is also available in the "Report options" dialog box ).

View Report alarms
On the report menu, point to alarm and click triggered alarm ".
The "Report alert" dialog box appears.

Select the alert for which you want to view its records.
Click view records ".
A new report tab is opened, showing the report records that trigger the alarm. If the record is hidden, the record group is displayed, but not deepened.

NOTE: If more than one trigger alarm is selected before you click the view record button, the result is generated by executing and Boolean operations on the selected alarm.

To return to the report alert dialog box, click the preview tab.
Click Close to close the report alarm dialog box.
----------------------------------------------
5. Reference report alarms in Formulas
You can reference an alarm in a formula. Any formulas that reference the alarm will be printed time formulas.

The following functions are available:

Isalertenabled ()
Isalerttriggered ()
Alertmessage ()
The actions of these functions are the same as those of the alarms created in the create alarm dialog box:

isalerttriggered ("alertname") is true only for the records that trigger the alarm.
alertmessage ("alertname") displays recorded messages when the alarm is true.
because alarms are not field objects (they cannot be placed in reports), their representation is different from the report fields in the formula studio. In the function tree, you can see the alarm title of the function. Available alarms are listed below this title by name.