Adding a computed field to a data set
A data set can contain computed data as well as data that is returned from a data source. Computed data displays the result of an expression, typically involving one or more columns from a data source. For example, if each row that is returned from the data source contains a price and a quantity, you can create a computed field that calculates the total amount paid, using the following expression:
row["pricequote"] * row["quantity"]
You can also concatenate values from multiple fields, using the + operator, or calculate values using JavaScript functions. The expression builder provides a list of operators and functions that you can use to build expressions.
You can also define a computed field in the report layout. Defining computed fields in the data set is, however, the preferred approach. Defining the computed field in the data set separates business logic from the presentation of the data. Defining the computed field in the data set also enables you to verify the results of the calculation in the Preview Results page of Edit Data Set. You can determine whether the expression for the computed field is correct before using the field in the report design.
Figure 2‑2 shows the Preview Results page including the results of a computed field, Total_cost.
Figure 2‑2 Preview Results including the results of a computed field, Total_cost
How to add a computed field to a data set
1 In Edit Data Set, choose Computed Columns.
2 Choose New to create a new computed field.
3 In New Computed Column:
1 In Column Name, type a name for the computed field.
2 In Data Type, select a data type appropriate for the data returned by the computed field.
3 To perform a calculation on all the rows in the data set, in Aggregation, select an aggregate function. If performing an aggregate calculation, you can optionally specify a filter expression in Filter to determine which rows to include in the calculation.
4 In Expression, specify the expression to calculate the desired value. You can either type the expression or use the expression builder to construct the expression. To use the expression builder, complete the following steps:
Choose the expression builder button to open the expression builder. In Category, select Available Data sets, then select your data set. Double-click an item to add it to the text area at the top.
Figure 2‑3 shows how to create an expression for a computed column.
Figure 2‑3 Creating an expression for a computed column
Choose OK to save the expression. The expression appears in the Expression field in New Computed Column.
5 Choose OK to save the computed field. Computed Columns displays the computed field and the expression you defined.
4 Choose Output Columns to see all the columns that are specified in the data set. The computed field appears on this page.
5 Choose Preview Results to confirm that the computed field returns the correct data.
6 Choose OK to save your changes to the data set.
