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

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

  • Analysis Index Weight Factor
  • Analysis Priority
  • Budget Category
  • Treatment Cost

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

The Calculated Expressions window also contains the following three panes:

  • Expressions pane on the top left
  • SQL pane in the bottom left
  • 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 is the 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 for each section in Network Master that are generated from a Decision Tree. It is applied in network analysis when the prioritization method is chosen. The higher the number, the higher the priory to treat the road section. An SQL expression is required for this parameter. This parameter is the 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 values are 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 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 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 top left. When the selected parameter has multiple, discrete values that appear in a drop-down list, that means multiple records in this pane are configured but if the parameter's values result from a formula, then only one record is configured.

SQL Pane

The SQL pane in the bottom left contains the calculated Oracle SQL expression pertinent to the selected parameter and record in the Expressions pane. This expression is used in analysis to provide the relevant 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:

  1. Click Check to be sure that the expression is valid
  2. Click Accept
  3. 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 Columns in Analysis window.

  • No labels

Provide Feedback