In this topic
Expressions form the building blocks of your reports and extracts. An expression—in its simplest form—is a field in the report or extract. An expression can also combine a function with a field to perform calculations based on the data in your report or extract.
For example, in report of sources, you could calculate the average number of coding references in your sources. To do this, you would create an expression based on the:
Source.Number of Coding References field.
Average function.
The resulting expression would be: {Average(Source.Number of Coding References)}.
You can add or modify expressions in your reports or extracts. You can add:
A field on its own—for example, {Source.Name}.
A field combined with a function—for example, {Count values(Source.Name)}.
The following illustration shows the different parts of the Add Expression or Modify Expression dialog box:
1 Select a field that you want to base your expression on.
2 Click Add Field.
3 Select a function. Only functions that are applicable to the selected field are displayed—refer to List of functions, for more information.
4 Click Add Function.
5 The expression is shown here and reflects any changes you make using this dialog box.
The functions that are available depend on the type of data that is contained in the field. For example, the Sum function can be used on a numeric (integer or decimal) field such as Number of coding references, but not on a text field.
| Function | Description | Example | Can be used with |
| Sum | Totals the numbers in a report. | Add the Number of Coding References for my sources | Numeric |
| Average | Calculates an average. | Calculate the average Number of Coding References for my sources | Numeric |
| Max | Displays the maximum value. You can also use Max on Date/Time fields. | Display the most recent date that my sources were Modified On. | Numeric or Date |
| Min | Displays the minimum value. You can also use Min on Date/time fields. |
Display the earliest date that my sources were Modified On. |
Numeric or Date |
| Upper | Display the text in upper case. | Change water quality to WATER QUALITY. | Text |
| Lower | Display the text in lower case. | Change Balance and COMMUNITY to balance and community | Text |
| Day | Displays the day of the month for a date. The result is a number from 1 to 31. | Displays 17 March 2011 as 17. | Date |
| Month | Displays the month for a date. The result is a number from 1 (January) to 12 (December). | Displays 17 March 2011 as 3. | Date |
| Year | Displays the year for a date. The result is a number from 1900 to 9999 | Displays 17 March 2011 as 2011. | Date |
| Count values | Counts the number of unique values in the selected field. Repeated values are excluded in the count. | If the data contained the following: Wanda, Henry, Wanda, Effie, Henry, the result would be 3. | Numeric, Text, Date, Boolean |
| Count records | Counts the number of records, excluding records where the selected field is empty. | Count the number of sources in the report. | Numeric, Text, Date, Boolean, Image |
You can add expressions to the Group Header, Group Footer or Detail bands. For example, you can add an expression to the group footer to display a subtotal for each group. You can also add expressions to the Detail band to display summary information—for example, count or sum rather than showing every record for a field.
To add an expression:
Select the band where you want to add an expression.
On the Report tab, in the Controls group, click Expression. The Add Expression dialog opens.
Create your expression—refer to Understand expressions for more information.
Click OK.
NOTE To include totals and other calculations in the Report Header or Footer, use the Totals command in the Header & Footer group, on the Report tab.
You can modify expressions in the Group Header, Group Footer, or Detail bands—for example, if you want to change a field or calculation in your report.
Select the expression (field or calculation based on a field) that you want to modify.
On the Report tab, in the Add/Modify group, click Modify. The Modify Expression dialog opens.
Make any changes to the expression—refer to Understand expressions for more information.
Click OK.
