Advanced Reporting: Expression Editor

NAVIGATION   Modules > Advanced Reporting > Categories icon > click a row > Edit > click an element > Properties > Data Binding > Expressions

SECURITY  Advanced Reporting > View Reporting and run reports

You can use an expression to set the value of an element in a report or set conditions under which certain styles apply. You can set expressions through the Expression Editor while setting values in the properties window. The editor 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 by selecting nearly any property of an element.

While building an expression, you can directly add the entire expression or part of it in the Expression section of the Expression Editor. Then use the Insert or Append options to create a complete expression.

Concatenating fields and strings

You can concatenate fields with strings and with other fields.

EXAMPLE  The following expression produces a result like Customer Name: Bossert, Lewis: Customer Name: {LastName}, {FirstName}

Conditional formatting

You can highlight a part of data by using expressions in properties, such as Color, Font, Border, on specific field values based on a condition. The formula for conditional formatting is as follows: {IIF(<Condition>, <TruePart>, <FalsePart>)}

EXAMPLE   If you enter the following expression in the Font > FontWeight property of a text box that displays the names of people, you produce the name Denise in bold: {IIF(FirstName { "Denise", "Bold", "Normal")}

Expression Editor attributes

The Expression Editor has three major sections:

  • Expression
  • Values
  • Functions

Expression

The Expression text box is the input area of the Expression Editor, where you enter the expressions as strings.

All expressions are enclosed within curly braces {}. Even the expression for a field value for the TextBox element is set as {TextBox}.

NOTE  The Date & Time functions used to define the date and time values display the date in MM/DD/YYYY and the time in HH:mm:ss format.

Values

Values are the input fields available to the selected report. The following values are available in the Expression Editor:

  • Constants: Static constants, like numbers or strings (for example, 123 and abc).
  • Common Values: Built-in field values of the report (for example, the current page number and the total number of pages).
  • Parameters: Parameters of the report defined in the report designer.
  • Data Sets: Datasets and fields linked to the selected report.
  • Operations: Arithmetic, comparison, concatenation, logical/bitwise, and bit shift operators.
  • Documents Map: Labels of the TOC (Table of Contents) members of the selected report.
  • Theme: Visual settings, including fonts, colors, constant, and images. A combination of two or more theme elements is used to increase the graphical appeal of a table in the selected report.
  • Report Items: Field items available as text boxes in the selected report.

For more information, refer to Expression Editor values.

Functions

You can use several aggregate and other functions in your expressions. Advanced Reporting includes a range of functions, including running value, population standard variance, standard deviation, count, minimum, and maximum.

EXAMPLE  The following expression produces a count of employees: {Count(EmployeeID, Nothing)}

For more information, refer to Expression Editor functions.

How to...

Create an expression

To access the Expression Editor in Advanced Reporting, complete the following steps:

  1. Navigate to the Categories tab.
  2. Click a row in the Documents list (without clicking the report name). Either click the Edit this report (pencil) icon in the upper-right corner of the page, or click the More icon and select Edit. The selected report will open in edit mode.

    NOTE  If the edit option is not available, you may not have access to edit the report, such as one of the admin reports. You will need to copy the report and edit the copy.

  3. Select the text box you wish to create or edit the expression for.
  4. In the Common section of the Properties pane on the right side of the page, click the Data Binding yellow square box next to the Value property and select Expression. The Expression Editor will open.

Configure or edit an expression

Complete the following steps:

  1. In the Expression Editor, select an expression element from the Values or Functions section or directly enter the expression in the Expression text box. Double-click the expression element to add it.
  2. Click Save to add the expression.

NOTE  To resize the Expression Editor, click and drag the lower-right corner of the dialog box. To move the Expression Editor, click and drag the header.