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:
The following expression counts the number of records with non-zero values:
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] is a plain list that provides access to all report items at one level.
You cannot use the ReportItems collection in a Calculated Field‘s expression.
Specify Images for Picture Boxes
Use Row/Column Indexes for Cross Tab Cells
Use the following variables to change a Cross Tab cell’s appearance settings:
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.
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.
Use Variables for Event-Related Expressions
Use the following variables to specify an expression that the XRControl.BeforePrint event resolves:
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.
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.
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:
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.
Returns the page count in a report document.
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.
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])
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:
Refer to the following topic for an example on how to use this syntax: Calculate an Aggregate Function.