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. |
Use Variables for Event-Related Expressions
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:
- The report is merged with another report.
- The report includes a table of contents.
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:
[Accounts][]
Refer to the following topic for an example on how to use this syntax: How to Use an Aggregate Function in Calculated Fields.