Geocortex Reporting Help
Open topic with navigation
Expressions specify how to find or calculate a value when the report runs. They allow you to configure report elements when you don't know in advance what an element's value will be. A simple example of this is a data field whose value is obtained when Geocortex Reporting queries the data source. Expressions also allow you to compute complex values. For example, you could use an expression to perform mathematical operations on data fields and present the results in the report.
One common use for expressions is to build complex strings. For example, you could use an expression to build a phrase with embedded data values, or you could build the URL for an image that you want to include in a report. Another important use for expressions is to use conditional values in a report. For example, you could present different information depending on the value of a data field. The
Iif() function is used to configure conditional values.
You can use expressions in certain types of controls, for example, Label controls. They are also used to filter a report's query. Expressions that filter queries are discussed in Filter Conditions.
Expressions are made up of elements like data fields, functions, and literals. Literals are actual values, for example,
True. A simple expression has a single element, for example,
[Name]. Complex expressions have multiple elements that are combined by operators, for example,
[Population] > 1000000 uses the greater than operator,
>, to compare a field value to a number. Whether they are simple or complex, expressions evaluate to a single value of a particular type—numeric expressions evaluate to a number, Boolean expressions evaluate to a value of type Boolean (
False), string expressions evaluate to a string, and so on.
To see a complete list of the operators, literals, and functions that you can use in expressions, see Operators, Literals, and Functions.
The following table gives examples of different types of expressions.
Examples of Expressions
Same as the type of the data field
Evaluates to the value of the OBJECTID field for the current record, for example: 145632
Concatenates the "Object ID: " string with the value of the OBJECTID field for the current record, for example: Object ID: 145632
Concatenates the value of the current record's Distance field and the value of the current record's DistanceUnits field, for example: 26.3km
Calculates the difference between the values of the pop2018 field and pop2014 field, for example, if pop2018 is 966 and pop2014 is 711: 255
Concatenates the "Population Change: " string with the difference between pop2018 and pop2014, for example: Population Change: 255
An expression similar to this is used to present the current selection set in the report. See Include the Current Features in a Report.
Evaluates to today's date, for example: 23 July, 2018
Expressions use SQL syntax. Follow the syntax conventions below when you create expressions:
Enclose data field names in square brackets (
), for example:
Type a question mark (?) before query parameters names, for example:
Parameters prefix before report parameters names, for example:
Enclose string literals in apostrophes (
'), for example:
The use of quotation marks (
"), for example,
"Quoted Text", results in an error.
To embed an apostrophe into a string literal, type two apostrophes, for example:
'It''s an example'
The Boolean literals are
Enclose date-time literals in hash marks (
#), for example:
[OrderDate] >= #1/1/2016#
Use a question mark (
?) to represent a null reference (one that does not refer to any object), for example:
[Region] != ?
Use round brackets
() to control the order in which an expression is evaluated, for example:
2 * ([Quantity] - 1)
If an expression contains terms of different types, you can use dedicated functions to convert the types, for example:
Refer to Operators, Literals, and Functions for a complete list of supported operators, literals, and functions.
To help you build expressions, Report Designer has a built-in Expression Editor that gives you easy access to the different types of elements you can use in an expression. The Expression Editor only shows items that are permitted in the current context.
To open the Expression Editor, click the icon for a property that allows expressions. The expression that you create will show in . You can type the expression or parts of the expression directly in . You can also insert items by selecting the category of item in and then double-clicking the item in . Data fields and report parameters are listed in the Fields category.
This video shows an expression being created in the Expression Editor.
String expressions are expressions that evaluate to a string. A complex string expression combines (concatenates) an number of string literals, data fields, and functions using the concatenation operator,
String literals are enclosed in apostrophes, for example,
'This is a string literal'. Note that you cannot use quotation marks (
") instead of apostrophes. To embed an apostrophe into a string literal, type a two apostrophes, for example,
'It''s an example' appears in the report as: It's an example
Concatenating strings in an expression allows you to build complex strings that contain data. One common scenario is labelling data. For example, to label a record's object ID, you could use an expression like
'Object ID: ' + [objectid]. This appears in the report as, for example: Object ID: 19245
In other situations, you may want to build phrases or complete sentences. For example, the expression
'Towns with a population of ' + [MaxPop] + ' or less are eligible to apply for a ' + [ProgramName] + '.' appears in the report as, for example: Towns with a population of 50000 or less are eligible to apply for a Small Town Improvement Grant.
You can configure multiple lines of text in a single string expression by using the
NewLine() function. For example, suppose you want to configure a Label control to present data for a person's name on the first line and the person's address on subsequent lines, for example:
465 Mallory Lane
To do this, you could configure an expression like this:
[Name] + NewLine() + [StreetAddress] + NewLine() + [City] + ', ' + [State] + NewLine() + [ZipCode]
As an alternative to the
+ concatenation operator, you can use the
Concat() function to concatenate any number of strings. For example, to label data, you could use an expression like this:
Concat('Object ID: ', [objectid])
You can use conditional values in a report by using the logical function,
Iif() function takes three arguments: a Boolean expression, a value to use if the Boolean expression evaluates to
True, and a value to use if the Boolean expression evaluates to
False. The arguments are separated by commas:
Iif(BoolenExpression, TruePart, FalsePart).
BooleanExpression==true is equivalent to
BooleanExpression==false is equivalent to
Not BooleanExpression or
For example, to present the value of a Boolean data field in a report, you could use the expression
Iif([BooleanField],True,False). More commonly, you use values that reflect what the field represents instead of
False, for example,
Iif([RoadClosed], 'Road Closed', 'Road Open'). The conditional values do not have to be literals—they can be expressions. For example,
Iif([CropArea]<=10, 'Livestock: '+[HasLivestock]+' Produce: '+[HasProduce]+' Flowers: '+[HasFlowers], 'Not a market garden').
Geocortex Reporting supports a wide range of .NET functions that you can use in expressions. The functions fall into the following categories:
String Functions: Allow you to pad and trim strings, convert a string's case, manipulate substrings, and more. See String Functions.
GIS Functions: Allow you to retrieve ArcGIS field values for presentation in reports. See GIS Functions.
Logical Functions: Allow you to test for null and empty values and present conditional content in a report. See Logical Functions.
Aggregate Functions: Allow you to aggregate values by calculating sums, averages, and more. See Aggregate Functions.
Math Functions: Allow you to perform mathematical calculations like logarithms and trigonometric calculations, perform rounding, generate random numbers, convert between number types like integers and real numbers, and more. See Math Functions.
Date-Time Functions: Allow you to get UTC and local dates, calculate time spans and future dates, and more. See Date-Time Functions.