Create a Formula in a Cost Sheet Column

This procedure addresses how to create a formula column for use on a cost sheet. (The general procedure also applies to cost sheet templates and work sheets.)

When creating a cost sheet column, selecting a logical data source lets you build a formula in which you can include:

Other columns from this cost sheet
Single data sources, such as business processes in a certain status
Total elements, such as the tallied value of a column
Data elements from single-record, non-workflow business processes

If you are creating a formula for forecasts (unaccepted), data sources are limited to business processes flagged in uDesigner for cost sheet forecasting. For example, if base commits and change commits were enabled for cost sheet forecasting, you will see base commits (unaccepted) and change commits (unaccepted) as available data sources for use in the forecasts (unaccepted) formula. You will not see other data sources that were not flagged in uDesigner for this purpose.

When you build your formula, think about which data sources will generate the result you want in your cost sheet column. For example, if you want to see total commitments, you can add contracts (approved) and change orders (approved).

Notes:

Use care when selecting the Cost Column that you use to build your formula. You can possibly choose from Columns (columns in the sheet), Data Sources (values of business processes), Total Elements (totals from columns), or Data Elements (data elements on Information/General record(s), typically named Project Information). The source column that you use can affect the data values in your cost sheet. If you select an incorrect source, you can get a result you do not expect. Be sure that you know the source to use before you build your formula.
Formula-based columns display a label when you hover over the column name to show what comprises the contents of the column. Formulas that are based on BPs are not automatically updated if you subsequently change the name of the BP. To update the hover label, you can right-click the column name, select Properties, click Select in the Formula section, and make the applicable changes. You can also update the cost sheet template and use the Update Shells feature to update the applicable projects/shells.

To create a formula:

Go to the Company Workspace tab and switch to Admin mode.
In the left Navigator, select Templates, and then select Cost Sheets to open the Cost Templates log.
Open the applicable cost sheet template.
From the toolbar, click Add Column (  ).
In the New Column dialog box, choose Formula as the type, select a Datasource, and then click the Select button in the Formula field.
Enter the first value in the formula:
- To enter numerical values into the formula, click the number keys on the on-screen keypad. (Include parentheses, % or decimal point as necessary.)
- To add a data element or column value (existing columns on the sheet you are working on) into the formula, double-click an item from the left pane.
As you build the formula, it appears in the Formula box in the upper portion of the window.
Click the appropriate operator: add, subtract, multiply, or divide.
Continue to alternate between choosing values and operators to add to the formula.
Note: The system applies the operators in the order of proper mathematical procedure: from left to right, with multiplication and division first, followed by addition and subtraction.
To undo (reverse) the last action, click Undo; to clear the entire formula, click Clear All.
When the formula is complete, click Save to save your formula and return to the Column Properties window.

Related Topics