Create Formula Fields
The Data Source Editor tab in the Data Source Edit screen contains the Add Formula Field button, located immediately above the Selected Fields grid.
Clicking this button produces a menu of possible functions types and sub menus of functions within each type. The list below shows the available functions by function type.
See the following pages on Microsoft.com for more in-depth explanations of these functions:
|
Category |
Function |
|---|---|
|
String Functions |
ASCII |
|
String Functions |
DIFFERENCE |
|
String Functions |
LOWER |
|
String Functions |
REPLACE |
|
String Functions |
RIGHT |
|
String Functions |
SPACE |
|
String Functions |
SUBSTRING |
|
String Functions |
CHAR |
|
String Functions |
LEFT |
|
String Functions |
LTRIM |
|
String Functions |
REPLICATE |
|
String Functions |
RTRIM |
|
String Functions |
STR |
|
String Functions |
UPPER |
|
String Functions |
CHARINDEX |
|
String Functions |
LEN |
|
String Functions |
NCHAR |
|
String Functions |
REVERSE |
|
String Functions |
SOUNDEX |
|
String Functions |
STUFF |
|
Mathematical Functions |
ABS |
|
Mathematical Functions |
ATAN |
|
Mathematical Functions |
COS |
|
Mathematical Functions |
EXP |
|
Mathematical Functions |
LOG10 |
|
Mathematical Functions |
RADIANS |
|
Mathematical Functions |
SIGN |
|
Mathematical Functions |
SQUARE |
|
Mathematical Functions |
ACOS |
|
Mathematical Functions |
ATN2 |
|
Mathematical Functions |
COT |
|
Mathematical Functions |
FLOOR |
|
Mathematical Functions |
PI |
|
Mathematical Functions |
RAND |
|
Mathematical Functions |
SIN |
|
Mathematical Functions |
TAN |
|
Mathematical Functions |
ASIN |
|
Mathematical Functions |
CEILING |
|
Mathematical Functions |
DEGREES |
|
Mathematical Functions |
LOG |
|
Mathematical Functions |
POWER |
|
Mathematical Functions |
ROUND |
|
Mathematical Functions |
SQRT |
|
Data Conversion Functions |
CAST |
|
Data Conversion Functions |
CONVERT |
|
Date Functions |
DATEADD |
|
Date Functions |
DATEPART |
|
Date Functions |
GETUTCDATE |
|
Date Functions |
DATEDIFF |
|
Date Functions |
DAY |
|
Date Functions |
MONTH |
|
Date Functions |
DATENAME |
|
Date Functions |
GETDATE |
|
Date Functions |
YEAR |
Examples of why you might want to use a formula field:
-
To display a resource's full name in one field, instead of two fields (one for the first name, one for the last name).
-
To "ark up a cost on a work order so that you can charge a customer this additional amount.
-
To calculate a new date based on an existing date field and a set number of days.
These are the most common examples of the uses of Formula Fields. For information on creating formulas for these function, see the examples in the next topic.Formula Field Examples
Formula Field Properties
Once you select a function type and function for the formula field, the field's properties box opens. Below are the options that can be set for each field.
|
Option |
Purpose |
|---|---|
|
Hide Field |
This button allows you to hide the field and its data return when the data source is generated. |
|
Summary Function |
You can select from mathematical summary function like Count and Sum. |
|
Sort By |
This allows you to choose Ascending or Descending. |
|
Function |
This displays the function that was selected for this formula field. You can click the down arrow to view and edit the SQl for the formula field. |
|
Fields section |
Below the Function field, there is an area where you specify the field or fields involved in the formula. For example, if you are using the STRINGPLUS function shown in the screen shot above, you are given up to three fields to implement in the formula. The button next to each field allows you to change the text field to a drop-down menu, where you can select one of the fields on the data source. |
Once you set the options for the field, click the Close button (the gray X in the upper-right corner of the Properties panel). TMS applies your changes. This panel can be opened again by clicking the Properties button.
Things to Note About Formula Fields
-
Grouping is not available on formula fields.
-
Formula fields do not transfer into SQL.
-
Formula fields cannot be edited manually.
