4.3.4 Creating an Expression/Formula Column
An expression column, or formula column, is a column whose data is generated from a script (expression) by processing existing data in the table. The expression can use either SQL or JavaScript syntax, and can reference any other columns within the Data Table.
To add an expression column to a table, follow these steps:

1. Click the 'Expression' button (labeled 'fx') located in the title row of the Data Table. This opens the 'Expression' dialog box.
2. Enter a name for the new column in the dialog box, and click 'OK'.

This adds the formula column to the right side of the table, and opens the Formula Editor.
3. In the Formula Editor, click the 'SQL' button to enter an expression using SQL syntax, or press the 'Script' button to enter an expression using JavaScript syntax. Select the appropriate data type from the 'Return Data Type' menu.

4. Enter the desired formula expression in the bottom text field.
JavaScript expression columns can make full use of the built-in scripting functions in the script library. You can reference the value of a Viewsheet input component, Viewsheet parameter, report parameter, or Worksheet variable by using the component name or variable name as a parameter. For example, if a Viewsheet input control has name 'RadioButton1', you can reference the input's value within a Worksheet expression by using syntax “parameter.RadioButton1”.
See Input Components in Dashboard Design for more information.
5. Click 'OK' to exit the Formula Editor. The data in the new column will reflect the results of the specified expression.
Consider the 'Sales by Category' query, which lists the total revenue generated from the sales of different categories of products. It also has information about the total discount given for each product category. Using these two pieces of information, you can calculate the 'Revenue' generated by each product category. Follow these steps to perform the calculation.

1. Create a new Worksheet by clicking on the 'New Worksheet' button.
2. Expand the 'Data Source' node, and the 'Orders' node.
3. Drag the 'Sales by Category' query from the tree onto an empty cell in the Worksheet. A new data block, 'Sales by Category1', is created.

4. Click on the 'Expression' button, located on the title row of the 'Sales by Category1' table. This opens the 'Expression' dialog box.
5. Enter “Revenue” as the name of the new expression column.

6. Click 'OK'. This appends a new 'Revenue' column to the right side of the Data Table, and opens the Formula Editor.
7. In the 'Return Data Type' menu, select the 'Float' option.
8. Enter the following formula in the text area:
You can click on a field name in the left panel to add it to the expression area.
field['Total Sales'] - field['Total Discounts'];

Refer to Appendix B:Accessing Table Cells in Script for information on how to reference the current table cell, row, and column in the expressions.
(Optional) To edit the expression again at a later time, click the 'fx' button in the expression column header (not the table title). This reopens the Formula Editor.
9. Preview the 'Sales by Category1' table.

See Also
Editing an Expression Column, to modify the formula of an expression column.
Appendix B:Accessing Table Cells in Script, to reference table data in expressions.
Appendix JS:General JavaScript Functions, for further information.
| << 4.3.3 Adding or Removing a Row from an Embedded Table | © 1996-2013 InetSoft Technology Corporation (v11.5) | 4.3.5 Automatically Substituting JavaScript For SQL >> |