Creating Calculations and Percentage Items

...

Ownership

The Director of the Business Support Center is responsible for ensuring that this document is necessary, reflects actual practice, and supports University policy.

Section Objectives

At the end of this section, you should be able to:

Create a calculation
Create a complex calculation
Pass a parameter value to a calculation
Create a percentage

Creating Calculations

Calculations

A calculation describes an operation between items that a query requests. Calculations can be simple, such as an operation between items that belong to a folder, or they can be complex mathematical or statistical expressions.

Using Parentheses to Change Operator Precedence: The following arithmetic operators are used in calculations:

Multiply (*)
Divide (/)
Add (+)
Subtract (–)

When you use arithmetic expressions in a calculation, the multiply and divide operators are executed first, regardless of their position in the calculation. If you have multiple operators that follow the same precedence rule, they are evaluated from left to right.

Annual Salary + Annual Increase Amount = New Annual Salary

(Annual Salary + Annual Increase Amount)/ 12 = New Monthly Salary

If you use parentheses around the expression (Annual Salary + Annual Increase Amount), the addition is executed before the division.

Creating a Simple Calculation in the Calculations Dialog Box

To create a new simple calculation, follow these steps:

1. Select Tools—>Calculations.

2. Click New in the Calculations dialog box. The Edit Calculation dialog box is displayed.

Creating a Calculation in the Edit Calculation Dialog Box

1. Enter the calculation for the item in one of the following ways:

If you know the calculation syntax, enter it directly in the Calculation field.
Build the calculation by pasting items from the Items list and functions from the Functions list into the Calculation field.

2. Click OK to create the calculated item. The calculation appears selected in the Calculations dialog box.

3. Click OK in the Calculations dialog box.

Note: When you create a calculation, you should change the item name to reflect the calculation content.

Creating a Simple Calculation from the Toolbar

1. Select the symbol that represents the operation you want to perform. The Calculation dialog box for that operation is displayed.

2. Enter the name of the item as you want it to be displayed in the results window.

3. Select the item from the first drop-down list in the “Defined as” region.

4. Select the second item from the second drop-down list in the “Defined as” region.

5. Select [OK] to create the new calculation.

Operating on Two Items: Example

To create a calculation based on two existing items, follow these steps:

1. Select two items in a worksheet.

2. Select an operation from the toolbar. The Calculation dialog box is displayed containing the two items.

3. Enter a name for the calculated item.

4. Alter the order of operations as desired by selecting the items in the fields in the “Defined as” drop-down lists, as discussed on the previous page.

5. Select [OK] to create the new calculation.

Building Complex Calculations

You can create complex calculations using a combination of SQL functions and folder items. These calculations can be as simple as the example on the previous slide, or as complex as the DECODE example shown at the end of this chapter. The steps to create a complex calculation are similar to those for creating a simple calculation.

Available Functions

*Functions*	*Examples*
Conversion	TO_NUMBER converts a character value to a number.
Date	ADD_MONTHS adds months to a date.
Group	SUM adds two items or values.
Numeric	ABS calculates the absolute value of a number.
Other	DECODE compares an expression to a search criterion and returns a value.
String	CONCAT links two strings of characters.

Available Functions

Conversion: Single-row functions that convert one data type to the same or another data type
Date: Calculates arithmetic operations on dates
Group: Calculates the reporting functions described earlier in this lesson
Numeric: Single-row numeric functions that return numeric values
Other: Single-row functions that do not apply to any other grouping (You can include multiple search criteria and return expressions.)
String: Single-row functions that return character values

Note: Administrators can create additional functions in the database based on user requirements. These functions are grouped under the Database functions.

Using Calculations in Conditions

You can include a calculation in a condition. To include a calculation in a condition, follow these steps:

1. In the New Condition dialog box, select Create Calculation from the Item drop- down list. An Edit Calculation dialog box is displayed.

2. Create the calculation.

Note: You can also create calculations for the condition values.

Building Complex Calculations

You can create complex calculations using a combination of SQL functions and folder items. These calculations can be as simple as using the analysis toolbar, or as complex as using a DECODE statement.

DECODE is a function that is used to compare an expression to a search criteria and return a value. The DECODE statement is very similar to the “if-then” statement in excel. For instance, if you want to perform a calculation based on two columns and plan to divide one column by the other, you will receive a negative result if the first column contains a zero. You can avoid the divide-by error by using the DECODE function.

EXAMPLE:

The syntax below states: If the data in the column named contains a 0, treat it as a 0 and do not perform a calculation; otherwise perform the calculation. Commas separate the operations.

The DECODE statement below (named EC Sort) was created by the IS Developers to define the order of expenditure categories rather than using the alpha sort. A numeric value is assigned to each expenditure category; hence if EC Sort is selected as the primary sort, the numeric value will apply the order of the sort precedence. (This column is usually hidden, so the sort values are not visible in the results window.)

Example:

Passing a Parameter to a Calculation

To pass a parameter value to a calculation, use the following steps:

1. Select Tools—>Parameters.

2. Click New in the Parameters dialog box.

3. Create the parameter in the Edit Parameter dialog box. Select NONE in the For Item field. Give the parameter a name.

4. Create the calculation in the Edit Calculation dialog box. In the calculation string, enter the name of the parameter, where needed, preceded by a colon (:), or paste it from the Parameters folder; then, apply the calculation.

Creating Percentages

Select Tools—>Percentages ® New. The Percentage window is displayed.

1. Select the item from the “Calculate percentages for” drop-down list.

2. Select the item from the “Calculate as a percentage of” list.

Select “Grand total of all values” to display the percentages as data in the worksheet.
Select “Grand total for each column” to display the percentages in the bottom row of the worksheet.
Select “Grand total for each row” to display the percentage as a column in the worksheet.
Select “Each change in” to display the percentages by the item that you select in the drop-down list.

For a table worksheet, only the first and last options are available.

1. Enter the text for the header in the “Column heading” field.

2. Select the label that appears in the worksheet.

Select “Display subtotal and subtotal percentage.” Create the label yourself with text and parameters from the Label drop-down list. The parameters are the values or items used in the calculation, and they appear as &Value, &Item, and & Data in the Label drop-down list. In the worksheet, they represent the column value, item, and data point, respectively.
Select “Display subtotal’s percentage of grand total.”

Percentages in the Result Window: An Example

Summary

In this lesson, you should have learned how to:

Create a simple calculation using the Calculations dialog box
Create a simple calculation using the calculations functions provided on the analysis toolbar
Create a complex calculation
Pass a parameter value to a calculation
Create a percentage
Effective: 02/03/06 njk

Revision: 2

Copyright 2004 University of Virginia and Oracle Corporation. All rights reserved.

Oracle® Tutor Copyright © 1997, 2004, Oracle. All rights reserved.