5.4.1 Creating a Calculated Field from Detail Data
To create a new calculated field from detail data, follow the steps below:
1. In the Viewsheet Component panel, right-click on the Data Block, data model, or query to which you want to add the calculated field. Select 'New Calculated Field' from the context menu.

This opens the 'Calculated Field' dialog box.
Note: The selection of 'Detail' or 'Aggregate' is a one-time choice. You cannot convert between these two types at a later time.
2. In the 'Name' field, enter a name for the new calculated field.

3. Select the 'Detail' option, and click 'OK'. This opens the Formula Editor.
4. In the 'Return Data Type' menu, select the data type that the calculated field will return.
5. Enable the 'SQL' option to construct the formula using SQL syntax. Disable the 'SQL' option to construct the formula using JavaScript syntax.
Click on an available detail field in the panel to add it to the expression.
6. Enter the desired calculation into the formula field at the bottom of the dialog box. The available detail fields are listed in the top-left panel.

7. Click 'OK' to close the Formula Editor. This adds the new calculated field to the data source tree in the Component panel.

You can now bind this calculated field to a Viewsheet component (Output Components, Data View Components, etc.) in the same way that would bind any other data field.
Consider a Viewsheet that is based on the sample 'Order Model' data model. In this example, you will analyze how the average difference between the 'Number in Stock' and the 'Reorder Level' varies according to product category. Note that this difference ('Number in Stock' – 'Reorder Level') is not available as a predefined attribute within the data model. However, you can create this quantity in the Viewsheet as a calculated field.
Follow the steps below:
1. Create a new Viewsheet based on the 'Order Model' data model. (See Quick Start: Creating a Viewsheet for more information.)
2. Right-click the 'Order Model' node in the Component panel, and select 'New Calculated Field' from the context menu.

This opens the 'Calculated Field' dialog box.
3. In the 'Name' field of the 'Calculated Field' dialog box, enter the text “Reorder Count”.
4. Select the 'Detail' option and click 'OK'. This opens the Formula Editor.

5. In the Formula Editor, follow the steps below:
a. In the top-left panel, click the 'Product:Number In Stock' field to enter the field into the expression.
b. Type a minus (“–”) symbol, or select this symbol from the top-right 'Operators' panel.
c. In the top-left panel, click the 'Product:Reorder Level' field to enter the field into the expression.
d. In the 'Return Data Type' menu, select 'Integer'.

e. Click 'OK' to close the Editor. This adds the new 'Reorder Count' calculated field to the 'Order Model'.

6. From the Component panel, drag a Chart component into the Viewsheet.
7. Click the 'Edit' button on the new Chart to open the Chart Editor.
8. In the Data Source panel, expand the 'Dimensions' folder under the 'Product' entity.
9. Drag the 'Product:Category' field from the Data Source panel to the 'X' region in the Data panel.
The 'Reorder Count' calculated field appears by default under the 'Measures' folder because it has a numerical 'Return Data Type'.
10. In the Data Source panel, expand the 'Measures' folder.
11. Drag the 'Reorder Count' field from the Data Source panel to the 'Y' region in the Data panel.
12. Click the 'Edit Measure' button next to the 'Y' region in the Data panel, and select 'Average' from the 'Aggregate' menu.

13. Click the green 'Apply' button.
The Chart Y-axis now displays the average of the calculated difference between 'Number in Stock' and 'Reorder Level' on a category-by-category basis.
14. Resize the Chart so that the Y-axis title is fully displayed.

| << 5.4 Calculated Fields | © 1996-2013 InetSoft Technology Corporation (v11.4) | 5.4.2 Creating a Calculated Field from Aggregate Data >> |