The requested page is not available for the requested platform. You are viewing the content for Default platform.

Calculate Custom Values

  • 3 min 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.

report-server-report-with-calc-field-result-highlight

Specify an Expression Binding

Do the following to specify a custom expression with data source fields for the control's Text property:

  1. 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.

    report-server-add-table-cell-for-calc-field

  2. 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.

    report-server-table-cell-expression

    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').

  3. 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.

    report-server-report-layout-with-expression-binding

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:

  1. Switch to the Field List, select a node corresponding to a master-detail relationship and click the Add calculated field button.

    report-server-create-calc-field

  2. 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.

    report-server-calc-field-settings

  3. Click the Expression property's ellipsis button to specify an expression for evaluating a calculated field's values.

    report-server-calc-field-expression

    A calculated field's expression can evaluate the values of other calculated fields if you avoid circular references.

  4. Use the calculated field as an ordinary field for binding report controls. Drop this field from the Field List onto the target table cell.

    report-server-drop-calc-field-onto-table-cell

    You can also filter, sort and group your report against calculated fields.

View the Result

Click the Preview report-server-web-preview-button toolbar button to see the resulting report.

report-server-report-with-calc-field-result

See Also