About Formulas

What Is a Formula?

A formula is an expression that calculates the value for a computed column in a staged or aggregation table.

There are two types of formulas: supplied formulas and user-defined formulas.

Supplied formulas are provided by SAS IT Resource Management and are loaded along with the SAS IT Resource Management software at installation time. Supplied formulas cannot be modified. However, you can copy a supplied formula and modify the copied version, thus creating a user-defined formula.
User-defined formulas are created by the data administrator or the performance analyst.

How Can a Formula Be Used?

Formulas can be used in all SAS IT Resource Management tables in order to calculate new computed columns.

Computed columns can be used for various purposes, such as to standardize the measurement units or time zones of related data or to establish datetime-related constructs such as WEEKDATE and SHIFT.

Computed columns are supplied with the template tables for supplied adapters.
Computed columns can be generated and added to staged or aggregation tables.

For more information about computed columns, see Specify Computed Columns.

How Is a Formula Created?

There are two ways to create a user-defined formula.

A user-defined formula can be created by using the New Formula wizard, which is invoked by selecting Formula from the FileNewIT Resource Management menu of SAS IT Resource Management.
A user-defined formula can be created by copying a supplied formula and pasting it into a folder to which you have Write privileges. The newly copied formula will retain its name unless it is pasted into a folder that already contains a formula with the same name. In that situation, the formula will be named "Copy of formula name." After it is pasted into the target folder, the formula can be renamed and modified.

How Can Formulas Be Accessed?

Formulas ( formula icon

) can be accessed from several folders in the Folders tree of SAS Data Integration Studio and can be used when specifying the expression that defines a computed column.

Products is a folder that contains formulas that are supplied with SAS IT Resource Management software. These supplied formulas cannot be changed. However, they can be copied to another folder where they can be modified.

To access these supplied formulas from the Folders tree, expand ProductsSAS IT Resource ManagementIT Formulas.
Shared Data is a folder that contains formulas that are supplied with SAS IT Resource Management software, as well as those user-defined formulas that can be shared by other users at your site. Formulas that are in this folder can be modified.

To access these formulas from the Folders tree, expand Shared DataSAS IT Resource ManagementIT Formulas.
My Folder is a folder that contains user-defined formulas that you have saved in that location. My Folder is the private folder of the person, or owner, who is currently logged on to SAS IT Resource Management. The contents, including formulas, are not accessible to anyone other than the owner.
The Users folder contains the private folders of all the users who are registered users of the metadata repository to which you are connected. The only folder that you can access in the Users folder is your own, private folder. It is the folder that belongs to the person who is logged in to SAS IT Resource Management.

The following display shows the IT Formulas tab of the Expression Builder window. You can expand My Folder, Shared Data, or your private folder within the Users folder and use any of those formulas in your expression. Alternatively, you can select the Inventory tab and from the list that is displayed, use any of those formulas in your expression.

IT Formulas Tab of Expression Builder

Note: The IT Formulas tab of the Expression Builder window is available only when you are editing columns of a staged or aggregation table from the Properties dialog box in the process flow diagram.

How to Access the Expression Builder Window

You can use the Expression Builder window to add a new computed column to a table or to modify an existing computed column in a table.

To access the Expression Builder window in order to add a new computed column to a table, perform the following steps:

From the IT data mart that contains the table, double-click the job that uses the table. The job opens on the Diagram tab of the Job Editor window.
Select the table to which you want to add a column. Right-click the table to display the Properties dialog box of that table and click Properties.
Click the Columns tab to display the columns that make up the table.
Click New to add a new column to the table. From the drop-down menu, click Computed column.
Double-click the enabled Expression field for the new field to display an ellipsis (...).
Click the ellipsis (...). The Expression Builder window appears.

To access the Expression Builder window in order to modify a computed column in a table, perform the following steps:

From the IT data mart where the table is stored, double-click the job that uses the table. The job opens on the Diagram tab of the Job Editor window.
Select the table whose column you want to modify. Right-click the table to display the Properties dialog box of that table and click Properties.
Select the Columns tab to display the columns that make up the table.
Select the Expression field of the column that you want to change.
Double-click the enabled Expression field for the field to display an ellipsis (...).
Click the ellipsis. The Expression Builder window appears.

For more information about the Expression Builder window, press the F1 key or select Help from within that window.