Expressions

In the Report Designer you can use an expression to set the value of a control in the report, or set conditions under which certain styles apply. You can define expressions in:

• Properties in the properties window
• Expression Editor dialog

Expressions use the ActiveReportsJS expression syntax: an expression is enclosed in curly braces { }, and anything outside the braces is treated as literal text. For example, the expression for a field value for a TextBox is set as follows:

{Room}

Expression Editor Dialog

You can build expressions quickly using the Expression Editor dialog. This dialog allows you to choose from a number of fields available to the report as well as to a particular property.

You can access the Expression Editor from nearly any property of a control, using the button at the right of the property row in the Properties panel.

There are the following types of fields available in the Expression Editor:

• Constants
  Constants available for properties which have enumerated values. If, for example, you have chosen to set an Expression for the VerticalAlign property of a TextBox the available constants would be Top, Middle, Bottom.
• Common Values
  Run time values available to every property in every report. See Common Values for further information.
• Parameters
  Parameters fields available in reports which contain report parameters. If available, you can choose a parameter from this field to retrieve the current value of the parameter.
• Data Sets
  All fields from each dataset associated with the report. However, the report retrieves only the sum or the first value of any field that is not within the current dataset scope.
• Operations
  Arithmetic, comparison, concatenation, logical/bitwise, bit shift operators for creating custom expressions.
• Common Functions
  Predefined functions. See Common Functions for more information.
• Document Map
  The DocumentMap.Path expression defines labels for the report's TableOfContents members. The example of such expression is {DocumentMap.Path & " General Information"} . If this expression is defined in the Label property of the report's control associated with the report's TableOfContents, General Information will be displayed as the label of the corresponding report's TableOfContents member.

Create an Expression in the Expression Editor

The Expression Editor dialog is composed of two panes, Fields and Expression.

1. From the Fields pane, select a field you want to use in your expression.
2. Click the Replace, Insert or Append button to add the field to the Expression pane. The expression pane shows the fields in a valid expression format.
3. Click OK to close the dialog.

The expression appears as the property value in the properties grid.

Using Expressions in Reports

In its raw form, your data may not be ideally suited for display in a report. You can customize and manipulate data to make it perform and display in ways that suit your needs using expressions. Following are some examples of how expressions are set in different scenarios.

Concatenating Fields and Strings

Because anything outside the curly braces is literal text, the easiest way to combine fields with strings is to mix text and expressions in the same value. For example, use the following to get a result that reads "System: Auditorium - Audio System".

System: {Room} - {System} System

This example takes the hardcoded string "System: ", adds the value of the Room field, then adds another string " - ", then the value of the System field, then finishes with the string " System".

Within a single expression you can also concatenate (or 'add') strings and fields using the & operator; the following produces the same result:

{"System: " & Room & " - " & System & " System"}

Conditional Formatting

You can use expressions in properties like Color, Font, Border etc. to return different values based on a condition e.g. to highlight a part of your data. The formula for conditional formatting is:
{IIF(YourFieldName operator "Value to compare", "use this value if true", "use this value if false")}

For example, if you enter the following expression in the Font > FontWeight property of a textbox that displays information about a product, you will get bold text if the product's Type Attribute has the value "New".

{IIF(Type = "New", "Bold", "Normal")}

Functions

You can use a number of aggregate and other functions in your expressions. The Report Designer includes a range of functions, including running value, population standard variance, standard deviation, count, minimum and maximum. For example, use the following expression to get a count of products with the same Model Number.

{Count([Model Number])}

The general expression forms, for field values and for aggregates on field values, are:

{<FieldName>} or {[<FieldName>]}

{<Aggregate>(<FieldName>)} or {<Aggregate>([<FieldName>])}

The square-bracket form is required when the field name contains spaces or other special characters, as in the Model Number example above.

Besides field references, you can also reference report parameters as {@Param} and Globals as {&Value}, such as {&PageNumber} — see Common Values.

Common Values

Common Values are run time values available to every property in every report. You can directly drag and drop these common values from the Common Values node of the Data panel onto the design surface or add and modify the values from the Expression Editor. Following is a list of the values that you can see under the Common Values node.

Information about the current user and project is available from the User and Project datasets — see Datasets.

Common Functions

You can use a function in an expression to perform actions on data in data regions, groups and datasets. You can access these functions in the Expression Editor dialog, which you can open from any property that accepts expressions using the button at the right of the property row.

Within the Expression Editor dialog, there is a tree view of Fields. Expand the Common Functions node to view the available functions. The following tables contain details about each of the functions included in the Report Designer for use in property expressions.

Date & Time

These are ActiveReportsJS date and time functions. See the ActiveReportsJS Date and Time Functions topic for information on overloads for each method.

These are all the available aggregate functions:

Math

These are ActiveReportsJS math functions. See the ActiveReportsJS Math Functions topic for information on overloads for each method.

Inspection

These are ActiveReportsJS functions. See the ActiveReportsJS Functions topic for information on overloads for each method.

Program Flow

These are ActiveReportsJS functions. See the ActiveReportsJS Decision Functions topic for more information.

Aggregate

You can use aggregate functions within report control value expressions to accrue data. For all of the functions, you can add an optional <Scope> argument, passed as a string — see Using the Scope Argument below.

These are all the available aggregate functions:

Conversion

These are ActiveReportsJS conversion functions. See the ActiveReportsJS Conversion Functions topic for more information.

Miscellaneous

The Report Designer also offers several functions which do not aggregate data, but which you can use with an IIF function to help determine which data to display or how to display it.

Using the Scope Argument

All functions have a Scope argument which determines the grouping, data region, or dataset to be considered when calculating the aggregate or other function. By default — when the Scope argument is omitted — the calculation scope is the current scope: within a data region, this is the innermost grouping to which the report control belongs. To calculate over a different scope, pass the name of a grouping, dataset, or data region as a string. For example, in a table containing a group named Room:

{Sum([Cost Price], "Room")}

totals the cost price over the current Room group, wherever the expression is placed.

The Scope argument must name a data region, grouping, or dataset that directly or indirectly contains the report control using the function in its expression. If the report control is outside of a data region, the Scope argument refers to a dataset. If there is only one dataset in the report, you can omit the Scope argument. If there are multiple datasets, you must specify which one to use to avoid ambiguity.