Defining Expressions in Classic Designer

Use a report expression to define custom measures and calculations based on one or more base measures, to specify the condition that drives a conditional format rule, and to define a query that returns choices for a dashboard prompt. Define report expressions using BQL. You can save expressions for re-use as "saved expressions" that are globally available across the space.

Important: To have access to create, modify, or delete saved expressions, you must belong to a group that as Edit Saved Expressions permission. See Creating Groups and Adding Users to Groups.

You can also pass prompt filters and values to expressions. See Passing Prompt Filters and Values to Expressions and Custom Formulas and About Dimensional Expressions.

You can also create a saved expression by selecting a folder in the Subject Area and clicking the Expression icon or by right-clicking a folder in the Subject Area and selecting Add Expression.

For information on editing a saved expression, see Editing Saved Expressions.

For information on how to use a saved expression in a dashboard prompt, see Using a Saved Expression in a Dashboard Prompt.

To define an expression for a column in a report

Open a report in Classic Designer.

Click the column header and select Column Properties from the menu. The Column Properties dialog box opens.

For Expression Type, select Dimension, Measure or Ratio. By default, attribute columns are of the type Dimension and measure columns are of the type Measure.

Tip: The Ratio expression type is used for calculating true totals in Summary/Group bands and pivot tables in the case of expressions that involve divisions and to avoid average of averages. See True Totals Using the Ratio Expression Type.

Type your custom expression in the Expression field, or in the Numerator and Denominator fields for a Ratio expression type. By default the base column expression is shown.

For example, to define a Revenue measure based on the two existing measures, Sum: Quantity and Avg: Unit Price, you would enter the following expression:

[Sum: Quantity]*[Avg: Unit Price]

Tip: Click on the Expression Builder icon

at the top right of the Expression box to use the Expression Builder to build your expression.

If you are placing a custom expression on a group header or footer band in Layout, select how the custom expression aggregates to the group level from the Group Aggregation Rule dropdown.

If you want to save the custom expression to the Subject Area for future use, click the Save Expression button and complete the fields in the Expression Properties dialog box.

Click Save in the Column Properties dialog box.