Creating Calculated Columns in a Data Source

A calculated column creates a new output column and calculates its values at runtime based on the result of an expression. For example, if you are creating a query on employees' Job Information and you want to report the duration of their employment, you can create a calculated column, Job Tenure, which would show the years between the current date and the employee hire date.

To create a calculated column, complete the following steps.

1. Navigate to the Query Designer.
2. Under the Data section, select the Create Calculated Column icon.

   The Column Overview dialog box appears.

3. Change to the Calculated Columns tab.
4. Choose the Add Calculated Column link.

   The Calculated Columns dialog box appears.

5. Enter a column ID and enter the name of the calculated column in Description.
6. In the Edit Formula section, create an expression using the available Functions, Conditions, and Operators, and choose Format to verify whether the expression is correct.

   This will be described in more detail with examples in the following section.

7. Choose OK to add the calculated column to the list of columns in your report.

   The column appears in the Calculated Columns section of the Column Overview dialog box.

8. If you do NOT want to display the calculated column in the query output, turn off the Show in Grid switch.
9. Choose OK.

Expressions define a calculated column. Calculated columns are built as formulas. Formulas are comprised of fields, constants, functions, conditions, and operators. You can type formulas manually or use the formula editor if you're not sure how to construct the formula you want.

• Fields: Fields appear in formulas with the syntax as [Schema#Table#Field]. You can select fields in the query by typing the description of the field.
• Values: Add text in " ", add numbers as values, enter dates as #YYYY-MM-DD#.

• Functions, conditions, and operators: Many will appear as FUNCTION() or as a single identifier such as >.

  : Many will appear as FUNCTION() or as a single identifier such as >.

As you begin to type, a hint list shows all available options (including formula and fields) that match the text you've typed. The list shows values for both Field IDs and Field descriptions.

Instead of typing formulas manually, you can do the following:

• Press CTRL + SPACE (on your keyboard) to choose from a list of values that are valid for that location in the formula
• Type [ for a list of fields.
• If you select a formula from the hint list, a template is automatically entered in the formula entry bar.
• A short help text description is displayed when you move the mouse pointer over the template.

A concatenation is the joining of two strings, in this case, text fields.

For example, you can create a text concatenation that will show a user's full name:

Example Inputs: First Name field (Kenneth), SPACE, Last Name field (Roden)

Example Output: Kenneth Roden

To concatenate fields, you can use the CONCAT() function

Example Expression: CONCAT( [User#Basic User Information#First Name], CONCAT( " ", [User#Basic User Information#Last Name] ) )

Use number formulas to calculate sums, differences, products, and quotients. First, select the data type as Number and add two or more fields to the Condition Editor with the appropriate operators. For example, you can create a formula that calculates the difference in New Salary and Current Salary:

• Example Inputs: New Salary (170,000) , Current Salary (165,000)
• Example Output: 5,000

To calculate values, you can use operators (+ - * /) as well as some functions such as POWER(), ROUND()

• [Compensation (Employee Central)#Compensation Information#New Salary] -

• [Compensation (Employee Central)#Compensation Information#Current Salary]

Date formulas work with fields that store date values. A variety of functions exist to work with dates.

For example, you can calculate the number of days since the current hire date:

• Example Inputs: CurrentDate () , Hire Date, 2018-04-01
• Example Output: 418

Many functions work with date fields, such as DAYS_BETWEEN(), YEAR(), ADDDAYTODATE(),

Example Expression: DAYS_BETWEEN([User#Basic User Information#Hire Date], CURRENTDATE() )

The IF() function compares two or more sets of data and tests the results. If the results are true, the second parameter instructions are taken; if not, the third parameter instructions are taken. IFs can be nested to include more than two possible outcomes.

For example, you may want to substitute the value stored in the gender field with a different label for each record:

• Example Inputs: Gender (M)
• Example Output: Male

Use one or more IF functions to build conditional logic.

Example Expression: IF([User#Basic User Information#Gender]="M","Male" ,"Female")

IF() can also be used to get counts (total number of items) and ratios (percentages).

For example, you may want to include the number of active employees in a division. In this circumstance, you can include a column that will display a value of 1 for active employees, and a value of 0 for inactive employees. During the display of the data, you can sum the column for the total count, or also display for each division.

• Example Inputs: Status (Active User)
• Example Output: 1

Use one or more IF functions to build conditional logic.

Example Expression: IF([User#Basic User Information#Status_Description]="Active User" ,1 ,0 )