All docs
V21.1
21.2 (EAP/Beta)
21.1
20.2
The page you are viewing does not exist in version 20.2. This link will take you to the root page.
20.1
The page you are viewing does not exist in version 20.1. This link will take you to the root page.
19.2
The page you are viewing does not exist in version 19.2. This link will take you to the root page.
19.1
The page you are viewing does not exist in version 19.1. This link will take you to the root page.
18.2
The page you are viewing does not exist in version 18.2. This link will take you to the root page.
18.1
The page you are viewing does not exist in version 18.1. This link will take you to the root page.
17.2
The page you are viewing does not exist in version 17.2. This link will take you to the root page.

How to: Use Expressions

  • 5 minutes to read

This topic lists solutions to common expression-related tasks.

Group Clauses with Brackets

Use square brackets to specify a condition under which the expression should return the result.

For instance, the following expression returns all Customers that have an account Date of 8/25/2006 and an account Amount of 100:

[Accounts][[Date] == #8/25/2006#] && [Accounts][[Amount] == 100]

Construct an expression as in the following example to search for all Customers that have an Account with both a Date of 8/25/2006 and an Amount of 100:

[Accounts][[Date] == #8/25/2006# && [Amount] == 100]

Calculate Group Summaries

Use the ^ operator to specify an expression that calculates a group summary.

  • Sum up the EFC field values in a group:

    [][[GroupFieldName] == [^.GroupFieldName]].Sum([EFC])

  • Specify the group header value:

    [][[CategoryID] == [^.CategoryID] and [ProductID] == [][[CategoryID] == [^.CategoryID]].Max([ProductID])].Max([ProductName])

  • Count the number of times a value occurs:

    The following expression counts how many times the value 12 occurs in the data source:

    [][[FootSize]='12'].Count()

    The following expression counts the number of records with non-zero values:

    [][[FootSize]!=0].Avg([FootSize])

Reference Report Items

A report’s elements are displayed in the Report Designer’s Report Explorer. You can access these elements and their properties in an expression. The following example demonstrates how to set a label’s BackColor property to another label’s BackColor property value:

[ReportItems].[xrLabel2].[BackColor]

Tip

[ReportItems] is a plain list that provides access to all report items at one level.

Note

You cannot use the ReportItems collection in a Calculated Field‘s expression.

Specify Images for Picture Boxes

When you specify an expression for the XRPictureBox‘s ImageSource property, you can use image Ids from the report’s ImageResources collection.

IIf([MarchSales]>20, [Images.ArrowUp],[Images.ArrowDown])

Use Row/Column Indexes for Cross Tab Cells

Use the following variables to change a Cross Tab cell’s appearance settings:

Variable

Description

Example

Arguments.GroupColumnIndex

Returns the index of a cell’s column within a group.

iif([Arguments.GroupColumnIndex] % 2 == 1, Rgb(235, 241, 252), ?)

Result: The specified color applies an odd-even color style to the Cross Tab’s columns.

Arguments.GroupRowIndex

Returns the index of a cell’s row within a group.

iif([Arguments.GroupRowIndex] % 2 == 1, Rgb(235, 241, 252), ?)

Result: The specified color applies an odd-even color style to cross tab rows.

You can specify expressions that are evaluated when the XRControl.BeforePrint and XRControl.PrintOnPage events occur.

Use the following variables to specify an expression that the XRControl.BeforePrint event resolves:

Variable

Description

Example

DataSource.RowCount

Returns the total amount of data rows in a data source.

[DataSource.RowCount] != 0

Result: When this expression is applied to a control’s Visible property, the control is hidden if the data source contains no data.

DataSource.CurrentRowIndex

Returns an index of the current data row in a data source.

Iif([DataSource.CurrentRowIndex] % 2 = 0, ‘red’, ‘green’)

Result: When this expression is used for a table row’s BackColor property, odd rows are colored in red, even rows are colored in green.

DataSource.CurrentRowHierarchyLevel

Returns a zero-based level of the current row in a hierarchical report.

Iif([DataSource.CurrentRowHierarchyLevel] == 0, Rgb(231,235,244), ?)

Result: When this expression is used for the BackColor property of the Detail band that is printed in tree mode, the root level rows are highlighted.

Use the following variables to specify an expression that the XRControl.PrintOnPage event resolves:

Variable

Description

Example

Arguments.PageIndex

Returns an index of the currently generated report document page.

[Arguments.PageIndex] % 2 = 0

Result: The control is displayed on odd pages only when this expression is used for a control’s Visible property.

Arguments.PageCount

Returns the page count in a report document.

Note

Do not use the Arguments.PageCount property in the following scenarios, as this property is not set to the correct value:

Iif(([Arguments.PageIndex] = [Arguments.PageCount] - 1), ‘The last page!’, ‘’)

Result: When this expression is applied to a label’s Text property, the ‘The last page!’ string is displayed on the last page.

Note

Do not use the Arguments.PageIndex and Arguments.PageCount variables in following scenarios, as these properties are not set to correct values:

Specify Parent Relations

Use the ‘^’ parent relation operator to refer to a parent in expressions that are written in the context of a child. You can apply this operator successively to span multi-level parent relationships.

You can use this operator to refer to the currently processed report group. This allows you to calculate aggregates within groups, as shown in the following expression:

[][[^.CategoryID] == [CategoryID]].Sum([UnitPrice])

View Example: How to use aggregate functions in Calculated Field expressions

Test Collection Elements

Use brackets to check if a collection contains an element that meets a condition. The following expression returns true if the Accounts collection contains at least one element that meets the [Amount] == 100 condition:

[Accounts][[Amount] == 100]

The following expression returns false if the Accounts collection is empty:

[Accounts][]

Refer to the following topic for an example on how to use this syntax: Calculate an Aggregate Function.