Discoverer Plus - Functions Used in Calculations
ODS Specialist
ODS Specialist-Limited
ODS Specialist-Restricted
The ISDS System Usability Director is responsible for ensuring that this document is necessary, reflects actual practice, and supports University policy.
Functions Used in Calculations
You can use calculations to create a column of data that will exist only within your worksheet. Youcan then apply sorts, totals, conditions or even base other calculations on the newly created column.
For example, you want to sort expenditures based on expenditure categories but in a set order that does not correspond to the way expenditure categories sort alphabetically.
First determine the sequence in which you want to sort expenditure categories.Example:
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
The syntax for the DECODE statement is :
DECODE(expr, search1, result1[, searchN[, resultN[, default]]])
Expr (or expression) is the name of a column (data item) against which you want to test a possible value.
Search 1 = a possible value contained in that column
Result1 = an alternate value to assign to the calculated column when exprescolumn =Search1 value
SearchN = another possible value contained in that column (separated by a comma)
ResultN = an alternate value to assign to the column when it contains that searchN value
Default = the alternate value to assign when there is no matching value in the column you are testing that matches one already identified in the search1-searchN values.
Note: Each of the variables in the DECODE function are separated by commas.
The DECODE example for the desired alternated sort sequence of expenditure categories defined above:
Calculation Name = Exp Cat Sort Seq
Calculation =
DECODE(
Expenditure_Category,
'Revenue',1,
'Recoveries',2,
'Faculty Salaries',3,
'Classified Salaries',4,
'University Staff',5,
'Wages',6,
'GTA/GRA',7,
'UVa, Fringe Benefits, Employer',8,
'OTPS',9,
'Travel',10,
'Health Care Services',11,
'Contractual Services',12,
'Supplies & Materials',13,
'Equipment',14,
'Plant and Improvement',15,
'Property and Improvement',16,
'Continuous Charges',17,
'Scholarships & Fellowships',18,
'Other',19,
'UVa, F&A Costs',20
)
To sort expenditures based on the new Exp Cat Sort Sequence, TOOLS, SORT
Add Exp Cat Sort Seq
HINT: Create in NOTE PAD, then copy into Discoverer. Be sure to prefix each column name prefixed with the folder name (followed by a . (period))
Example: UVA_PA_Expnese_Summary.Expenditure_Category.
The syntax for the CASE statement is:
CASE WHEN X THEN Y ELSE Z END
The CASE example for the desired alternated sort sequence of expenditure categories defined above:
Calculation Name = Exp Cat Sort Seq
Calculation =
CASE WHEN Expenditure_Category = 'Revenue
THEN 1
ELSE
CASE WHEN Expenditure_Category ='Recoveries'
THEN 2
ELSE
CASE WHEN Expenditure_Category ='Faculty Salaries'
THEN 3
ELSE
CASE WHEN Expenditure_Category = 'Classified Salaries'
THEN 4
ELSE
CASE WHEN Expenditure_Category = 'University Staff'
THEN 5
ELSE
CASE WHEN Expenditure_Category ='Wages'
THEN 6
ELSE
CASE WHEN Expenditure_Category = 'GTA/GRA'
THEN 7
ELSE
CASE WHEN Expenditure_Category ='UVa, Fringe Benefits, Employer'
THEN 8
ELSE
CASE WHEN Expenditure_Category ='OTPS'
THEN 9
ELSE
CASE WHEN Expenditure_Category ='Travel'
THEN 10
ELSE
CASE WHEN Expenditure_Category ='Health Care Services'
THEN 11
ELSE
CASE WHEN Expenditure_Category ='Contractual Services'
THEN 12
ELSE
CASE WHEN Expenditure_Category ='Supplies & Materials'
THEN 13
ELSE
CASE WHEN Expenditure_Category ='Equipment'
THEN 14
ELSE
CASE WHEN Expenditure_Category ='Plant and Improvement'
THEN 15
ELSE
CASE WHEN Expenditure_Category ='Property and Improvement'
THEN 16
ELSE
CASE WHEN Expenditure_Category ='Continuous Charges'
THEN 17
ELSE
CASE WHEN Expenditure_Category ='Scholarships & Fellowships'
THEN 18
ELSE
CASE WHEN Expenditure_Category ='Other'
THEN 19
ELSE
CASE WHEN Expenditure_Category ='UVa, F&A Costs'
THEN 20
END END END END END
END END END END END
END END END END END
END END END END END
HINT: Create in NOTE PAD, then copy into Discoverer. Be sure to prefix each column name prefixed with the folder name (followed by a . (period))
Example:
UVA_PA_Expnese_Summary.Expenditure_Category
SUBSTR syntax:
SUBSTR(column, start position, [count])
This function takes the value in a column of data and returns a substring (or a portion) of the value starting at the character in the start position and going on for the [count] number of characters.
Example:
SUBSTR(Award_Number, 1,2)
|
NVL (Null Value) Function
NVL syntax:
NVL(column, replacement value)
This function replaces any null value in the column specified with the replacement value. If the column specified does not contain a null value, the it retains the value in the column.
Examples:
NVL(posted_flag,'N')
NVL(Burdened_Cost_YTD,0)
The current system date can be accessed by the referencing SYSDATE.
Example:
SYSDATE between Project_Start_Date and Project_End_Date
NOTE: SYSDATE contains both Date and Time DD_Mon_YYYY HH:MM:SS
When comparing to columns that only store the date, use the TRUNC Function to strip off the
time.
Trunc Syntax:
TRUNC(date column or SYSDATE)
Example:
TRUNC(sysdate) between Project_Start_Date and Project_End_Date
You can combine functions to achieve more complex calculations.
Example:
CASE WHEN SUBSTR (Award_Number, 1, 1) = G or SUBSTR (Award_Number, 1, 1) = Z
THEN SUBSTR (Award_Number, 1, 1)
ELSE SUBSTR (Award_Number, 1, 2)
END
Revision: 1
Oracle Tutor can be licensed to modify and add to this content, documenting all of your business process documentation in the Oracle Tutor format.
Oracle® Tutor Copyright © 1997, 2010, Oracle. All rights reserved.