The purpose of the Calculated Expressions window is to supply user-defined information for some parameters used in analysis. This setting is either the definition of a drop-down list for use in other windows or a formula for direct use in analysis. Each analysis module (i.e., Pavement Analyst, Bridge Analyst, etc.) has its own Calculated Expressions window, and the expressions only apply to analysis in that module.

The parameters are shown in the drop-down list in the top left corner of the window. The system allows 4 parameters to be defined using Calculated Expressions:

Analysis Index Weight Factor
Analysis Priority
Budget Category
Treatment Cost

By selecting from this drop-down list, you can then define the expressions for the parameter. Multiple expressions are allowed for all parameters except Analysis Index Weight Factor and Analysis Priority. These two parameters are only allowed one expression.

In addition to the parameters drop-down list, the Calculated Expressions window contains three panes: Expressions pane on the left, SQL pane in the bottom left, and Columns pane on the right.

Description of the Available Expressions

The available expressions are:

Analysis Index Weight Factor — This parameter affects the benefits resulting from a scenario analysis regardless of the objective of the analysis. An SQL expression is required for this parameter. This parameter acts as a default when a Groovy script is not selected in the Analysis Weight Groovy Script column in the Optimization Analysis window.

Analysis Priority — This parameter is a single formula that is the priority to select candidate treatment that are generated from Decision Tree, for each section in Network Master. It is applied in network analysis when the prioritization method is chosen. The higher the number the higher the priory that the road section will be treated at. An SQL expression is required for this parameter. This parameter acts as a default when a Groovy script is not selected in the Priority Groovy Script column in the Optimization Analysis window.

Note: Currently the system does not use any Calculated Expression for analysis priority. All analysis priority value is calculated from the groovy script. If no groovy script is selected, all sections will have a 0 priority.

Budget Category — When this parameter is selected, every record in the Budget Categories window (see Section 9.2 for details) is entered in the Expressions pane. Then, for each record in the Expressions pane, the ID of the budget category record in the Budget Categories window is entered in the SQL pane.

Treatment Cost — This parameter is composed of the set of values of all treatment costing methods. An SQL expression is required for each treatment cost method and is entered in the SQL pane. For example, if treatment cost is by square feet, then the formula would look like this: "UNIT_COST * COALESCE(SEC_WIDTH,24) * (LENGTH) * 5280 / 900", where unit cost, length, and width are columns in the Network Master. Treatment costs then appear in a drop-down list in the Cost column in the Treatments pane of the Treatments window.
The following example shows a relatively more complex treatment cost, where the cost is calculated based on different AADT levels, and the unit cost is stored in another table named PMS_TREATMENT_COSTS:

CASE

     WHEN AADT<2000 THEN (SELECT B.UNIT_COST* NVL(SEC_WIDTH,24)*(LENGTH) * 5280 /900 FROM PMS_TREATMENT_COSTS WHERE PMS_TREATMENT_COSTS_ID=1)

     WHEN AADT<5000 THEN (SELECT B.UNIT_COST* NVL(SEC_WIDTH,24)*(LENGTH) * 5280 /900 FROM PMS_TREATMENT_COSTS WHERE PMS_TREATMENT_COSTS_ID=2)

     ELSE THEN (SELECT B.UNIT_COST* NVL(SEC_WIDTH,24)*(LENGTH) * 5280 /900 FROM PMS_TREATMENT_COSTS WHERE PMS_TREATMENT_COSTS_ID=3)

END

Expressions Pane

The Expression pane allows you to configure the value of the parameter selected in the drop-down list in the upper left corner of the Calculated Expressions window. When the selected parameter may have multiple, discrete values that appear in a drop-down list, then multiple records in this pane are configured; when the parameter's values result from a formula, then only one record is configured.

SQL Pane

The SQL pane in the bottom left corner of the window contains the calculated Oracle SQL expression pertinent to the currently selected parameter and record in the Expression pane. It is this expression that is used in analysis to provide the appropriate calculation. The Oracle SQL expression must be limited to columns that exist in the Network Master table and must be valid. (To check that the SQL expression is valid, click the Check button.) The Columns pane lists the columns that may be included in the SQL expression.

Once an SQL expression is completed, activate the new SQL expression in the network analysis process by following the steps below:

Click the Check button to be sure that the expression is valid.
Click Accept.
Click Save to save the new expression.

Columns Pane

The Columns pane on the right side contains the list of columns that may be used in expressions. If a column is a list, then a hyperlink is provided in the List Column to display the values in the list.

A column of the Network Master is marked for use in expressions by selecting the Used in Expressions check box in the PMS Columns in Analysis window.