Table of Contents

How to: Use Expressions

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]

Note
  • [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

When you specify an expression for the Picture Box's Image Source 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:

  • 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.
*/

  • 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.
*/
Note

These variables are not valid when 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: Calculate an Aggregate Function.

Edit this page