Calculate Custom Values
- 3 minutes to read
This tutorial demonstrates how to calculate custom values using a predefined expression with fields from the bound data source. You can use the data fields containing information about price, quantity, and discount to evaluate an extended price as well. This document shows two ways to accomplish this task: by specifying a control’s expression binding and by creating a calculated field.
Specify an Expression Binding
Do the following to specify a custom expression with data source fields for the control’s Text property:
Select the rightmost table cell in the detail report’s Detail band. Switch to the Properties panel and click the Insert Column to Right button in the Actions category.
Select the created table cell, and in the Properties panel, click the Expression property’s ellipsis button. The invoked Expression Editor allows you to visually construct an expression using the predefined functions, logical and arithmetical operators, and the fields of a data source to which a report is bound.
Use the field name in [square brackets] to insert a data field into the expression’s text. Date-time constants should be wrapped in #hashtags#. Use apostrophes to denote ‘strings‘. For instance, Iif( [OrderDate] > #1/1/2017#, [OrderDate] , ‘Earlier than 1/1/2017’).
In the same way, add a new cell to the table located in the Group Header band, double-click this cell and enter the static caption for the calculated field.
Use a Calculated Field
If you want to use the calculated values in other calculations or group and sort report data against these values, you can create a calculated field at the data source level and then use it as an ordinary data field.
Do the following to create a calculated field:
Switch to the Field List, select a node corresponding to a master-detail relationship and click the Add calculated field button.
Select the calculated field and click the Edit button to customize its settings. Specify the field name and make sure to change the Field Type property to an appropriate value.
Click the Expression property’s ellipsis button to specify an expression for evaluating a calculated field’s values.
A calculated field’s expression can evaluate the values of other calculated fields if you avoid circular references.
Use the calculated field as an ordinary field for binding report controls. Drop this field from the Field List onto the target table cell.
View the Result
Click the Preview toolbar button to see the resulting report.