Creating a Formula Column in Data Binding Wizard

In addition to selecting existing columns directly from a data source (query, data model, etc.) you can also create new columns that are derived from existing columns. The values in these derived columns are computed by using SQL or JavaScript commands, and are called formula columns or expression columns.

To create a formula column, follow the steps below:

1. In the 'Data Binding' dialog box, select the Columns tab. (See Creating a New Table, Crosstab, or Section using a Wizard for information on how to access the 'Data Binding' dialog box.)

2. Click the 'Expression' button (labeled 'fx') above the right-side panel. 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.

5. Close the Script Editor, and click 'OK' to close the 'Formula' dialog box. This adds the new formula column (indicated by “script” icon) to the list of selected columns.

 

6. Click 'Finish' to close the 'Data Binding' dialog box, or continue with other desired modifications.

To edit an existing formula column, select the desired column, and then click the 'Edit' button. This will reopen the Script Editor. You can also hide, sort, reorder, or remove a formula column in the same way as other columns. See the appropriate section in Specifying Columns in Data Binding Wizard for instructions.

See Also

Selecting Columns in Data Binding Wizard, for information on selecting existing columns.

<< Hiding Columns in Data Binding Wizard © 1996-2013 InetSoft Technology Corporation (v11.5) Walkthrough: Selecting and Creating Columns in Data Binding Wizard >>