6.8 Calculating a New Query Field
You can create a new query field (also known as a formula column) by performing a calculation on existing query fields. To create a new field, follow the steps below:
1. Expand the Style Studio Data panel.
2. Right-click on the query to which you want to add the new field, and select 'Create Calculated Field' from the context menu.

This opens the 'Formula' dialog box.

3. With the 'Formula' dialog box open, follow the steps below:
a. In the 'Formula Name' field, enter a name for the new column.
b. For 'Return Data Type', select the data type of values that will populate the new column. This data type should match the type of value returned by your script.
Note: In cases where the data binding specifications cannot be merged into the query, the 'SQL' option is not available. (This may be due to the presence of grouping and aggregation at the query level.)
c. For 'Formula Type' select 'SQL' or 'Script', and then click the corresponding 'Edit' button. This opens the Script Editor.

4. Enter the desired script, using the syntax (SQL or JavaScript) that you specified in the previous step. To data values, use the following syntax:
a. To access the value in another column of the current row, use the “field['field name']” syntax.
b. To access the value in another column of a row at a certain offset (i.e., relative position) from the current row use the following syntax:
field[relative position]['field name'];
For example, field[-1]['Product.Total'] contains the value in the Product.Total field for the previous record.
For more information on scripting, see Report Scripting. For a simple example of a formula column script, see Walkthrough: Selecting and Creating Columns in Data Binding Wizard.
3. Close the Script Editor, and click 'OK' to close the 'Formula' dialog box. This adds the new formula column (indicated by the “fx” icon) to the query.
To edit an existing calculated field, right-click the desired column, and select 'Edit' from the context menu. This reopens the Script Editor, where you can proceed to edit the expression.
See Also
Creating a Formula Column in Data Binding Wizard, to create fields at Wizard level.
Creating a New Query, in Data Modeling, to create fields at the query level.
Defining Expression Attributes, in Data Modeling, to create fields at the data model level.
| << Walkthrough: Embedding the Subreport | © 1996-2013 InetSoft Technology Corporation (v11.4) | 6.9 Updating Data Binding After Query Modification >> |