1. Using Oracle Business Intelligence for Oracle Taleo Enterprise Edition

18Appendix

Appendix

Subject Areas

The standard content was delivered using the Recruiting subject area.

For a complete list of fields, refer to the document entitled Reporting with Oracle Business Intelligence – Data Dictionary – Subject Area available on My Oracle Support (Doc ID 1496352.1)

Accessibility Mode

Changing to Accessibility Mode

Accessibility mode in Oracle BI EE makes the rendering of the user interface more compatible with screen readers while allowing only that functionality that is supported for users with disabilities to be visible. The following list provides information on accessibility mode:

  • The "BI Composer wizard" is displayed in place of the "Analysis editor." For more information on BI Composer, see Chapter 14, "Using BI Composer to Work with Analyses."

  • The Home page does not contain links for accessing the Administration page or for performing most editing functions, such as for editing dashboards.

  • Graphs and map views are not displayed but are instead converted to one or more annotated tables.

  • Tables and pivot tables are rendered with appropriate internal annotations to enable screen readers to describe the contents of cells.

    Refer to your assistive-technology documentation for all applicable table navigation shortcuts.

  • You cannot use the mouse to modify the layout of a table or pivot table.

  • Performance tiles, and treemaps are rendered as pivot tables.

  • The collapse icon is included in the upper-left corner of each section on a dashboard page, even if that section is marked as not collapsible in the Dashboard builder. This allows the icon to be the first element that receives focus when using the keyboard to navigate on a dashboard page.

  • The Page Options toolbar button on a dashboard page, which displays a menu of page options, is not available.

  • If the dashboard page is refreshed, even if you navigate to another page, then the location of the focus is not preserved. You must press Tab to navigate through the focusable items.

  • Trellis views, when displayed on dashboards, are converted to pivot table views with the following changes:

    • Attribute columns of the inner graph are not included in the pivot table views.

    • Measure values are aggregated to the new grain of the pivot table views.

By default, Oracle BI EE does not use accessibility mode.

Enabling Accessibility Mode

  1. Press Tab multiple times to navigate through the global header, until the focus is on your user name in the Signed In As area.

  2. Press Enter, then Tab to highlight the My Account link.

  3. Press Enter to display the "My Account dialog."

  4. Press Tab to select the "My Account dialog: Preferences tab."

  5. Press Tab to navigate through the fields on the tab until you reach the Accessibility Mode options.

  6. Use the arrow keys to select the On option.

  7. Press Enter to save your changes and close the dialog.

  8. Refresh the page to see it displayed in accessibility mode.

Keyboard Shortcuts

Oracle BI supports standard keyboard shortcuts that are used in many software applications. In addition, there OBI offers shortcuts to perform specific tasks.

General Keystrokes

Keyboard Shortcut Result
CTRL+ALT+G Navigates to the first focusable element in the global header, which is the Skip to Content link. This link enables you to bypass the options that are available in the global header and to move to the features that available in the main part of the Home page.
CTRL+ALT+D Navigates to the Dashboards popup menu in the global header. You can then press ENTER to display a menu from which you select a dashboard to display.
CTRL+ALT+P Navigates to the current dashboard page tab, if the tab is displayed. If there is only one page in the dashboard, then the page tab is not displayed.
CTRL+SHIFT+S Navigates to the first focusable element in the next section. For a dashboard page, the first element is the collapse icon.
CTRL+SHIFT+ U Navigates to the first focusable element in the previous section. For a dashboard page, the first element is the collapse icon.
TAB Navigates to the next focusable element.
SHIFT + TAB Navigates to the previous focusable element.
Down Arrow Navigates to the next menu option.
Up Arrow Navigates to the previous menu option.
ENTER Triggers the activity, when the focus is on a link, an image, or a button with an associated URL or activity.
ESC Closes the menu that has the focus.
Keyboard Shortcuts for Navigating Dashboards in OBI
Note: If you use Freedom Scientific JAWS or other screen reader programs, then you must first disable the virtual PC cursor before using the keystroke combinations to navigate the dashboard. You must enable the virtual PC cursor at other times, such as when navigating within table objects on a dashboard.

Keyboard Shortcut Result
ALT + Up or Down Arrow Opens drop-down and combo boxes.
CTRL + Up or Down Arrow Shows the next or previous item in a combo box.
Keyboard Shortcuts for Navigating the BI Composer Wizard
Note: The Catalog tree that is displayed in the Catalog tab of the BI Composer wizard is interpreted as a table. To navigate the tree, use the table keystrokes for your screen reader program.

Keyboard Shortcut Result
ALT + SHIFT + b Navigates to the Back button.
ALT + SHIFT + c Navigates to the Cancel button.
ALT + SHIFT + l Navigates to the Finish button.
ALT + SHIFT + x Navigates to the Next button.
ALT + CTRL + m Navigates to the context menu. For example, selecting an item in the Catalog tree and pressing ALT + CTRL + m displays a menu with options to expand and collapse menu items.
ALT + Down Arrow Reads the messages in a pop-up window, one by one.

Navigating on the Home Page

  1. Press CTRL+ALT+G to display the Skip to Content link in the upper-left corner of the Home page.

  2. Press one of the following:

    • ENTER on this link to navigate to the default starting location on the Home page, which is the first link in the upper-left corner under the global header.

    • TAB to navigate to the first focusable element in the global header.

  3. Continue to press TAB to navigate through the elements on the Home page.

Navigating Dashboards with Keystrokes

  1. Navigate to the desired dashboard page as follows:

    1. Press CTRL+ALT+G to display the Skip to Content link.

    2. Press TAB multiple times to navigate through the global header to the tab that corresponds to the first page of the dashboard. If there are no page tabs, then you navigate to the first focusable element on the dashboard page. Press TAB to move through the dashboard pages. After the last page, pressing TAB gives focus to the dashboard page menu.

    3. Press ENTER when the focus is on the appropriate page tab to open that page of the dashboard.

  2. Press CTRL+SHIFT+S to navigate to the first focusable element in the next section, which is the collapse icon.

  3. Press TAB to navigate to the next focusable element in the current section.

  4. Press CTRL+SHIFT+S to navigate to the first focusable element in the next section.

  5. While the section collapse and expand icon has focus, press ENTER to collapse the current section.

  6. To navigate in a table or pivot table:

    1. Press TAB to select the table.

    2. Press TAB to move through the headers of the table and to select any menus that exist there.

    3. When the table has focus, press the arrow keys to navigate among the columns and body cells of the table.

      To access the drop-down menus in the header rows when a body cell has focus, press TAB, then ENTER to display the options for the menu in the first header row. Press TAB and use the arrow keys to highlight the options in the menu and press ENTER to select the desired option.

Syntax and Usage Notes for the SELECT Statement

The SELECT statement, or query specification, is the way to query a decision support system through the Oracle BI Server. A SELECT statement returns a table to the client that matches the query. It is a table in the sense that the results are in the form of rows and columns.

The SELECT statement is the basis for querying any structured query language (SQL) database. The Oracle BI Server accepts logical requests to query objects in a repository, and users (or query tools) make those logical requests with ordinary SQL SELECT statements. The server then translates the logical requests into physical queries against one or more data sources, combines the results to match the logical request, and returns the answer to the end user.

The SELECT statement in Logical SQL differs from standard SQL in that tables do not need to be joined. Any join conditions supplied in the query are ignored because the join conditions are predefined in the Oracle BI repository.

This section provides the basic syntax for the SELECT statement, as well as definitions for individual clauses. The syntax descriptions cover only basic syntax and features unique to the Oracle BI Server.

Basic Syntax for the SELECT Statement

Syntax for the SELECT statement is as follows:

SELECT [DISTINCT] select_list
FROM from_clause
[WHERE search_condition]
[GROUP BY column {, column}
[HAVING search_condition]]
[ORDER BY column {, column}]

Where:

select_list is the list of columns specified in the request.

FROM from_clause is the list of tables in the request. Optionally includes certain join information for the request.

WHERE search_condition specifies any combination of conditions to form a conditional test. A WHERE clause acts as a filter that lets you constrain a request to obtain results that answer a particular question. Together with the columns you select, filters determine what your results will contain.

GROUP BY column {, column} specifies a column (or alias) belonging to a table defined in the data source.

HAVING search_condition specifies any combination of conditions to form a conditional test. The syntax is identical to that for the WHERE clause. ORDER BY column {, column} specifies the columns to order the results by.

Usage Notes

The Oracle BI Server treats the SELECT statement as a logical request. If aggregated data is requested in the SELECT statement, a GROUP BY clause is automatically assumed by the server. Any join conditions supplied in the query are ignored because the join conditions are all predefined in the Oracle BI repository.

The Oracle BI Server accepts the following SQL syntaxes for comments:

  • /* */ C-style comments

  • // Double slash for single-line comments

  • # Number sign for single-line comments

Subquery Support

The Oracle BI Server supports certain subqueries, as well as UNION, UNION ALL, INTERSECT, and EXCEPT operations in logical requests. This functionality increases the range of business questions that can be answered, eases the formulation of queries, and provides some ability to query across multiple business models.

The Oracle BI Server supports the following subquery predicates in any conditional expression (for example, within WHERE, HAVING, or CASE statements):

IN, NOT IN
Any, >=Any, =Any, <Any, <=Any, <>Any
All, >=All, =All, <All,<=All, <>All
EXISTS, NOT EXISTS

In Answers, advanced users and developers can use the Advanced SQL Clauses fields in the Advanced tab of the Analysis editor to specify various SQL clauses, such as GROUP BY, HAVING, and DISTINCT, to include in the SQL queries that are sent to the Oracle BI Server. If an analysis contains hierarchical columns, selections, or groups, then certain Advanced SQL Clauses fields are not available.

SELECT List Syntax

The select_list lists the columns in the request. All columns need to be from a single business model. Table names can be included (as Table.Column), but are optional unless column names are not unique within a business model. If column names contain spaces, enclose column names in double quotes. The DISTINCT keyword does not need to be included, because the Oracle BI Server always does a distinct query. Columns that are being aggregated do not need to include the aggregation function (such as SUM), as aggregation rules are known to the server and aggregation is performed automatically.

Syntax

...
* |
(column | expr) [[AS] alias]
{, (column | expr) [[AS] alias] }
...

Where:

* Indicates all columns in the resultant table in the FROM clause.

column is a column (or alias) belonging to a table defined in the data source.

expr is any valid SQL expression.

Note: You cannot use * to select all columns from the Advanced tab of the Analysis editor in Answers. Instead, you must specify particular columns.

FROM Clause Syntax

The Oracle BI Server accepts any valid SQL FROM clause syntax. To simplify FROM clause creation, you can specify the name of a subject area instead of a list of tables. The Oracle BI Server determines the proper tables and the proper join specifications based on the columns the request asks for and the configuration of the Oracle BI repository.

WHERE Clause Syntax

The Oracle BI Server accepts any valid SQL WHERE clause syntax. There is no need to specify any join conditions in the WHERE clause, because the joins are all configured within the Oracle BI repository. Any join conditions specified in the WHERE clause are ignored.

The Oracle BI Server also supports the following subquery predicates in any conditional expression (WHERE, HAVING or CASE statements):

IN, NOT IN
Any, >=Any, =Any, <Any, <=Any, <>Any
All, >=All, =All, <All,<=All, <>All
EXISTS, NOT EXISTS

GROUP BY Clause Syntax

With auto aggregation on the Oracle BI Server, there is no need to submit a GROUP BY clause. When no GROUP BY clause is specified, the GROUP BY specification defaults to all of the nonaggregation columns in the SELECT list. If you explicitly use aggregation functions in the select list, you can specify a GROUP BY clause with different columns and the Oracle BI Server computes the results based on the level specified in the GROUP BY clause.

ORDER BY Clause Syntax

The Oracle BI Server accepts any valid SQL ORDER BY clause syntax, including referencing columns by their order in the select list (such as ORDER BY 3, 1, 5).

In addition, you can use the following syntax to alter the sort order for nulls in the query:

ORDER BY col1 NULLS LAST, ORDER BY col2 NULLS FIRST

Limiting and Offsetting Rows Returned

This is a short description.

You can use theFETCH and OFFSET clauses to constrain the number of rows returned by the SELECT statement and to skip a specified number of rows from the beginning of the result set. Both clauses are optional and can be used together, or independently. The fetch and offset clauses are part of the SELECT statement and are placed at the end.

These clauses are useful for situations where you have a large result set (such as with a large dimension), and you want to present, for example, the first 100 rows to the user. The Oracle BI Server stops processing when the limit is reached, improving overall performance and conserving resources. In addition, the limit is pushed to the back-end database in many cases so that the database can optimize the query.

Technically, both clauses can be used without an ORDER BY clause, but the results would be non-deterministic. Because of this, both clauses should always be used with ORDER BY.

If OFFSET is not specified, the default value is 0, which means that results are returned starting from the first row. If FETCH is not specified, it means that there is no limitation on the number rows returned.

Both clauses are evaluated after the WHERE clause, aggregation, HAVING clause, window analytic function, and ORDER BY clause.

Syntax for OFFSET Clause

OFFSET n ROW[S]

n is the number of rows you want to skip from the beginning of the result set. Note that n must be greater than zero.

Syntax for FETCH Clause

FETCH FIRST | NEXT n ROW[S] ONLY

n is the number of rows you want to skip from the beginning of the result set.. Note that n must be greater than zero.

Typically, FIRST is used when the limit clause is used independently of the offset clause, while NEXT is used when the limit clause is used in conjunction with the offset clause.

SELECT employeeid, firstname, revenue
FROM sales.employee
ORDER BY revenue desc
OFFSET 2 ROWS
FETCH NEXT 4 ROWS ONLY0

The following table lists the entire result set without the OFFSET and FETCH clauses. When the OFFSET and FETCH clauses are included, only the rows shown in bold are returned.

Employeeid FirstName Revenue
4 Margaret 250187
3 Janet 213051
1 Nancy 202143
2 Andrew 202143
7 Robert 177749
8 Laura 141295
9 Annie 133301
6 Michael 82964
5 Steven 78198

Limitations of the FETCH and OFFSET Clauses

Because ORDER BY clauses are ignored in UNION ALL set-operator blocks, using these clauses in such queries would be non-deterministic. Do not use FETCH and OFFSET with these queries.

Operators

There are two types of operators: SQL logical operators, and mathematical operators.

SQL Logical Operators

The following SQL logical operators are used to specify comparisons between expressions.

  • Between: Used to determine boundaries for a condition. Each boundary is an expression, and the bounds do not include the boundary limits, as in less than and greater than (as opposed to less than or equal to and greater than or equal to). BETWEEN can be preceded with NOT to negate the condition.

  • In: Specifies a comparison of a column value with a set of values.

  • Is Null: Specifies a comparison of a column value with the null value.

  • Like: Specifies a comparison to a literal value. Often used with wildcard characters to indicate any character string match of zero or more characters (%) or a any single character match (_).

Mathematical Operators

Mathematical operators are used to combine expression elements to make certain types of comparisons in an expression.

Operator Description
+ Plus sign for addition.
- Minus sign for subtraction.
* Multiply sign for multiplication.
/ Divide by sign for division.
|| Character string concatenation.
( Open parenthesis.
) Closed parenthesis.
> Greater than sign, indicating values higher than the comparison.
< Less than sign, indicating values lower than the comparison.
= Equal sign, indicating the same value.
<= Less than or equal to sign, indicating values the same or lower than the comparison.
>= Greater than or equal to sign, indicating values the same or higher than the comparison.
<> Not equal to, indicating values higher or lower, but different.
AND AND connective, indicating intersection with one or more conditions to form a compound condition.
OR OR connective, indicating the union with one or more conditions to form a compound condition.
NOT NOT connective, indicating a condition is not met.
, Comma, used to separate elements in a list.

Conditional Expressions

Expressions are building blocks for creating conditional expressions that convert a value from one form to another.

CASE (Switch)

This form of the CASE statement is also referred to as the CASE(Lookup) form. The value of expr1 is examined, then the WHEN expressions. If expr1 matches any WHEN expression, it assigns the value in the corresponding THEN expression.

If none of the WHEN expressions match, it assigns the default value specified in the ELSE expression. If no ELSE expression is specified, the system automatically adds an ELSE NULL.

If expr1 matches an expression in multiple WHEN clauses, only the expression following the first match is assigned.

Note: In a CASE statement, AND has precedence over OR.

Syntax

CASE expr1
WHEN expr2 THEN expr3
{WHEN expr... THEN expr...}
ELSE expr
END

Where:

CASE starts the CASE statement. Must be followed by an expression and one or more WHEN and THEN statements, an optional ELSE statement, and the END keyword.

WHEN specifies the condition to be satisfied.

THEN specifies the value to assign if the corresponding WHEN expression is satisfied.

ELSE specifies the value to assign if none of the WHEN conditions are satisfied. If omitted, ELSE NULL is assumed.

END ends the CASE statement.

Example

CASE Score-par
WHEN -5 THEN 'Birdie on Par 6'
WHEN -4 THEN 'Must be Tiger'
WHEN -3 THEN 'Three under par'
WHEN -2 THEN 'Two under par'
WHEN -1 THEN 'Birdie'
WHEN 0 THEN 'Par'
WHEN 1 THEN 'Bogey'
WHEN 2 THEN 'Double Bogey'
ELSE 'Triple Bogey or Worse'
END

In this example, the WHEN statements must reflect a strict equality. For example, a WHEN condition of WHEN < 0 THEN 'Under Par' is illegal because comparison operators are not allowed.

CASE (If)

This form of the CASE statement evaluates each WHEN condition and if satisfied, assigns the value in the corresponding THEN expression.

If none of the WHEN conditions are satisfied, it assigns the default value specified in the ELSE expression. If no ELSE expression is specified, the system automatically adds an ELSE NULL.

Note: In a CASE statement, AND has precedence over OR.

Syntax

CASE
    WHEN request_condition1 THEN expr1
    {WHEN request_condition2 THEN expr2}
    {WHEN request_condition... THEN expr...}
    ELSE expr
END

Where:

CASE starts the CASE statement. Must be followed by one or more WHEN and THEN statements, an optional ELSE statement, and the END keyword.

WHEN specifies the condition to be satisfied.

THEN specifies the value to assign if the corresponding WHEN expression is satisfied.

ELSE specifies the value to assign if none of the WHEN conditions are satisfied. If omitted, ELSE NULL is assumed.

END ends the CASE statement.

Example

CASE
    WHEN score-par < 0 THEN 'Under Par'
    WHEN score-par = 0 THEN 'Par'
    WHEN score-par = 1 THEN 'Bogie'
    WHEN score-par = 2 THEN 'Double Bogey'
    ELSE 'Triple Bogey or Worse'
END

Unlike the Switch form of the CASE statement, the WHEN statements in the If form allow comparison operators. For example, a WHEN condition of WHEN < 0 THEN 'Under Par' is legal.

Expressing Literals

A literal is a nonnull value corresponding to a given data type. Literals are typically constant values, or in other words, they are values that are taken as they are. A literal value must comply with the data type that it represents.

SQL provides mechanisms for expressing literals in SQL statements.

Character Literals

A character literal represents a value of CHARACTER or VARCHAR data type. To express a character literal, enclose the character string in single quotes ( ' ). The number of characters enclosed between the single quotes implies the length of the literal.

Examples

'Oracle BI Server'

'abc123'

Datetime Literals

The SQL 92 standard defines three kinds of 'typed' datetime literals, in the following formats:

DATE 'yyyy-mm-dd'

TIME 'hh:mm:ss'

TIMESTAMP 'yyyy-mm-dd hh:mm:ss'

To express a typed datetime literal, use the keywords DATE, TIME, or TIMESTAMP followed by a datetime string enclosed in single quotation marks, as in the preceding example. Two digits are required for all nonyear components even if the value is a single digit.

Examples

DATE '2000-08-15'

TIME '11:55:25'

TIMESTAMP '1999-03-15 11:55:25'

Numeric Literals

A numeric literal represents a value of a numeric data type (such as INTEGER, DECIMAL, or FLOAT). To express a numeric literal, type the number as part of a SQL statement.

Do not surround numeric literals with single quotes. Doing so expresses the literal as a character literal.

Note: When treating literals as NUMERIC, be aware of the Oracle standard double promotion rules, including the following: DOUBLE/NUMBER = DOUBLE , DOUBLE * NUMBER = DOUBLE Because the parsing of numeric literals happens very early in the query processing before the actual data source is known, internally, the Oracle BI Server treats decimal numbers as NUMERIC if ENABLE_ NUMERIC_DATA_TYPE is set to YES , regardless of data source type. When NUMERIC is enabled and the Oracle BI Server executes an expression internally involving decimal literals, the server treats the literals as NUMERIC even if the back-end data source does not support the NUMERIC data type. However, the type promotion rules still apply. For example, if the Oracle BI Server retrieves the data from a data source as DOUBLE and combines that with a NUMERIC literal during internal execution, the final result is still be converted to DOUBLE.

Numeric literals include:

  • Integer Literals

  • Decimal Literals

  • Floating Point Literals

Integer Literals To express an integer constant as a literal, specify the integer as part of a SQL statement (for example, in the SELECT list). Precede the integer with a plus sign (+) to indicate the integer is positive, or a minus sign (-) to indicate the integer is negative. Unsigned integers are assumed to be positive.

Examples

234

+2

567934

Decimal Literals To express a decimal literal, specify a decimal number. Precede the number with a plus sign (+) to indicate the number is positive, or a minus sign (-) to indicate the number is negative. Unsigned numbers are assumed to be positive.

Examples

1.223

-22.456

+33.456789

Floating Point Literals To express floating point numbers as literal constants, enter a decimal literal followed by the letter E (either uppercase or lowercase), followed by the plus sign (+) to indicate a positive exponent, or the minus sign (-) to indicate a negative exponent. No spaces are allowed between the integer, the letter E, and the sign of the exponent.

Examples

333.456E-

1.23e+

Variables

You can include and set variables in SQL statements. To do this, include the variable at the beginning of the SQL statement.

Syntax

SET VARIABLE variable_name = variable_value; SELECT_statement

If you are executing a query from the nqcmd utility, use a colon as a delimiter. Otherwise, you can use either a semicolon or a colon.

Examples

SET VARIABLE LOGLEVEL = 3; SELECT Products.Brand, Measures.Dollars FROM "Products"
SET VARIABLE DISABLE_CACHE_HIT=1, LOGLEVEL = 3, WEBLANGUAGE='en': SELECT
Products.Brand, Measures.Dollars FROM "Products"

Aggregate Functions

AVG

Aggregate functions perform operations on multiple values to create summary results.

This function calculates the average (mean) value of an expression in a result set. It must take a numeric expression as its argument.

Note that the denominator of AVG is the number of rows aggregated. For this reason, it is usually a mistake to use AVG(x) in a calculation in Oracle Business Intelligence. Instead, write the expression manually so that you can control both the numerator and denominator (x/y).

Syntax 
AVG(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

BOTTOMN

Example:

AVG(TIMESTAMPDIFF(SQL_TSI_DAY, "Requisition Dates"."Req. Creation Date", "Requisition Dates"."Latest Filled Date"))

This returns, in Days, the average time between the Req. Creation Date and the Latest Filled Date.

Adding DISTINCT to the formula will give the average (mean) of all distinct values of an expression:

Syntax

AVG(DISTINCT numExpr)

This function ranks the lowest n values of the expression argument from 1 to n, 1 corresponding to the lowest numeric value. The BOTTOMN function operates on the values returned in the result set. A request can contain only one BOTTOMN expression.

Syntax

BOTTOMN(numExpr, integer)

Where:

numExpr is any expression that evaluates to a numeric value.

integer is any positive integer. Represents the bottom number of rankings displayed in the result set, 1 being the lowest rank.

Example:

BOTTOMN("Candidate Counts"."# Submissions", 5)

This returns the 5 locations with the least number of Submissions.

This function calculates the number of rows having a nonnull value for the expression. The expression is typically a column name, in which case the number of rows with nonnull values for that column is returned.

Syntax

COUNT(expr)

Where:

expr is any expression.

Example:

COUNT("Requisition Collaborator"."Collaborator Name")

This returns the number of Collaborators per Requisition.

Adding DISTINCT to the formula will give the count of all distinct values of an expression:

Syntax

COUNT(DISTINCT expr)

MAX

This function calculates the maximum value (highest numeric value) of the rows satisfying the numeric expression argument.

Syntax 

MAX(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

Example:

MAX("Submission Dates"."Hired Date")

This returns the last date/time a candidate was hired.

MEDIAN

This function calculates the median (middle) value of the rows satisfying the numeric expression argument. When there are an even number of rows, the median is the mean of the two middle rows. This function always returns a double.

Syntax 

MEDIAN(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

Example:

MEDIAN(TIMESTAMPDIFF(SQL_TSI_DAY, "Requisition Dates"."Req. Creation Date", "Requisition Dates"."Latest Filled Date"))

This returns, in Days, the median time between the Req. Creation Date and the Latest Filled Date.

MIN

This function calculates the minimum value (lowest numeric value) of the rows satisfying the numeric expression argument.

Syntax 

MIN(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

Example:

MIN("Submission Dates"."Hired Date")

This returns the first date/time a candidate was hired.

NTILE

This function determines the rank of a value in terms of a user-specified range. It returns integers to represent any range of ranks. In other words, the resulting sorted data set is broken into several tiles where there are roughly an equal number of values in each tile.

NTile with numTiles = 100 returns what is commonly called the "percentile" (with numbers ranging from 1 to 100, with 100 representing the high end of the sort). This value is different from the results of the Oracle BI PERCENTILE function, which conforms to what is called "percent rank" in SQL 92 and returns values from 0 to 1.

Syntax

NTILE(numExpr, numTiles)

Where:

numExpr is any expression that evaluates to a numeric value.

numTiles is a positive, nonnull integer that represents the number of tiles.

If the numExpr argument is not null, the function returns an integer that represents a rank within the requested range.

Example:

NTILE("Candidate Counts"."# Submissions", 4)

When Req, Organization is included in Selected Columns, this formula will rank each organization in 4 groups (from 1st quartile to 4th quartile) according to the number of submissions.

PERCENTILE

Similar to NTILE, this function calculates a percent rank for each value satisfying the numeric expression argument but there will be 100 ranks, from 1st percentile to 100th percentile.

Example:

PERCENTILE("Candidate Counts"."# Submissions")

When Req. Organization is included in Selected Columns, this will rank each organization in 100 groups according to the number of submissions.

RANK

This function calculates the rank for each value satisfying the numeric expression argument. The highest number is assigned a rank of 1, and each successive rank is assigned the next consecutive integer (2, 3, 4,...). If certain values are equal, they are assigned the same rank (for example, 1, 1, 1, 4, 5, 5, 7...).

The rank is calculated based on the values in the result set.

Syntax 

RANK(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

Example:

RANK("Candidate Counts"."# Submissions")

When Req. Organization is included in Selected Columns, this will rank each organization starting with 1 according to the number of submissions.

STDDEV

This function returns the standard deviation for a set of values. The return type is always a double. STDEV_SAMP is a synonym for STDDEV.

Syntax 

STDDEV([ALL | DISTINCT] numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

If ALL is specified, the standard deviation is calculated for all data in the set.

If DISTINCT is specified, all duplicates are ignored in the calculation.

If nothing is specified (the default), all data is considered.

Example:

STDDEV("Candidate Counts"."# Submissions")

When Req. Organization is included in Selected Columns, this returns the number of submissions for each organization and the amount of variation of the entire data set (in this case the number of submissions) from the average.

STDDEV_POP

This function returns the standard deviation for a set of values using the computational formula for population variance and standard deviation.

Syntax 

STDDEV_POP([ALL | DISTINCT] numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

If ALL is specified, the standard deviation is calculated for all data in the set.

If DISTINCT is specified, all duplicates are ignored in the calculation.

If nothing is specified (the default), all data is considered.

SUM

This function calculates the sum obtained by adding up all values satisfying the numeric expression argument.

Syntax 

SUM(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

TOPN

Adding DISTINCT to the formula will sum all distinct values of an expression

Syntax

SUM(DISTINCT numExpr)

NOTE: for most numeric fields, OBI will automatically sum the values without specifying the function in the code.

This function ranks the highest n values of the expression argument from 1 to n, 1 corresponding to the highest numeric value. The TOPN function operates on the values returned in the result set. A request can contain only one TOPN expression.

Syntax 

TOPN(numExpr, integer)

Where:

numExpr is any expression that evaluates to a numeric value.

integer is any positive integer. Represents the top number of rankings displayed in the result set, 1 being the highest rank.

Example:

TopN("Candidate Counts"."# Submissions", 5)

When Req. Organization is included in Selected Columns, this returns the top 5 organizations with the highest number of submissions.

String Functions

String functions perform various character manipulations, and they operate on character strings.

ASCII

This function converts a single character string to its corresponding ASCII code, between 0 and 255. If the character expression evaluates to multiple characters, the ASCII code corresponding to the first character in the expression is returned.

Syntax 

ASCII(strExpr)

Where:

strExpr is any expression that evaluates to a character string.

Example:

ASCII('A')

This will give you the ASCII Code for the letter “A”, which is 65.

CHAR

This function converts a numeric value between 0 and 255 to the character value corresponding to the ASCII code.

Syntax 

CHAR(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value between 0 and 255.

Example:

CHAR(65)

This will give you the letter corresponding to the ASCII Code 65, which is the letter “A”.

CHAR_LENGTH

This function returns the length, in number of characters, of a specified string. Leading and trailing blanks are not counted in the length of the string.

Syntax 

CHAR_LENGTH(strExpr)

Where:

strExpr is any expression that evaluates to a character string.

Example:

SUBSTRING("Candidate Identification"."Name" FROM 4 FOR CHAR_LENGTH("Candidate Identification"."Name"))

This will return the string within the Candidate Name starting with the 4th character and extending to the end of the record.

CONCAT

There are two forms of this function. The first form concatenates two character strings. The second form uses the character string concatenation character to concatenate more than two character strings.

Syntax for Form 1 (To Concatenate Two Strings) 

CONCAT(strExpr1, strExpr2)

Where:

strExprs are expressions that evaluate to character strings, separated by commas.

Syntax for Form 2 (To Concatenate More Than Two Strings) 

CONCAT(strExpr1, strExpr2 || strExpr3)

Where:

strExprs are expressions that evaluate to character strings, separated by commas and the character string concatenation operator || (double vertical bars). First, strExpr2 is concatenated with strExpr3 to produce an intermediate string, then both strExpr1 and the intermediate string are concatenated by the CONCAT function to produce the final string. 

Example:
CONCAT("Candidate Identification"."Last Name",CONCAT(', '"Candidate Identification"."First Name,))

OR

CONCAT("Candidate Identification"."Last Name", ‘, ‘||"Candidate Identification"."First Name,)This will return the string: Last Name, First Name.

LEFT

Returns a specified number of characters from the left of a string.

Syntax 

LEFT(strExpr, integer)

Where:

strExpr is any expression that evaluates to a character string.

integer is any positive integer that represents the number of characters from the left of the string to return.

Example:

LEFT("Requisition Department"."Department Name",6)

This will return the first 6 characters of the Requisition Department Name.

LENGTH

This function returns the length, in number of characters, of a specified string. The length is returned excluding any trailing blank characters.

Syntax 

LENGTH(strExpr)

Where:

strExpr is any expression that evaluates to a character string.

Example:

SUBSTRING("Candidate Identification"."Name" FROM 4 FOR LENGTH("Candidate Identification"."Name"))

This will return the string within the Candidate Name from the 4th character to the end of the record.

LOCATE

This function returns the numeric position of a character string in another character string. If the character string is not found in the string being searched, the function returns a value of 0.

If you want to specify a starting position to begin the search, include the integer argument. The numeric position to return is determined by counting the first character in the string as occupying position 1, regardless of the value of the integer argument.

Syntax 

LOCATE(strExpr1, strExpr2 [, integer])

Where:

strExpr1 is any expression that evaluates to a character string. Identifies the string for which to search.

strExpr2 is any expression that evaluates to a character string. Identifies the string to be searched.

integer is any positive (nonzero) integer that represents the starting position to begin to look for the character string. The integer argument is optional.

Example:

substring("Requisition Department"."Department Level 1 Name",locate('-',"Requisition Department"."Department Level 1 Name")+1,length("Requisition Department"."Department Level 1 Name"))

This will return the string within the Req. Department Name starting from the first character after the dash (notice the +1), wherever it is located in the Department Name, to the end of the record.

This function converts a character string to lowercase.

Syntax 

LOWER(strExpr)

Where:

strExpr is any expression that evaluates to a character string.

Example:

LOWER("Candidate Identification"."Name")

This will convert all upper case characters to lower case within the Candidate Name.

POSITION

This function returns the numeric position of strExpr1 in a character expression. If strExpr1 is not found, the function returns 0.

Syntax 

POSITION(strExpr1 IN strExpr2)

Where:

strExpr1 is any expression that evaluates to a character string. Identifies the string to search for in the target string.

strExpr2 is any expression that evaluates to a character string. Identifies the target string to be searched.

Examples

This example returns 4 as the position of the letter d in the character string abcdef: 

POSITION('d', 'abcdef')

This example returns 0 as the position of the number 9 in the character string 123456, because the number 9 is not found. 

POSITION('9', '123456')

This function replaces one or more characters from a specified character expression with one or more other characters.

Syntax 

REPLACE(strExpr1, strExpr2, strExpr3)

Where:

strExpr1 is any expression that evaluates to a character string. This is the string in which characters are to be replaced.

strExpr2 is any expression that evaluates to a character string. This second string identifies the characters from the first string that are to be replaced.

strExpr3 is any expression that evaluates to a character string. This third string specifies the characters to substitute into the first string.

Example:

REPLACE("Candidate Identification"."Name",',',' ')

This will replace all commas within the Candidate Name with spaces.

This function returns a specified number of characters from the right of a string.

Syntax

RIGHT(strExpr, integer)

Where:

strExpr is any expression that evaluates to a character string.

integer is any positive integer that represents the number of characters from the right of the string to return.

Example

RIGHT("Requisition Department"."Department Name",6)

This will return the last 6 characters of the Requisition Department Name.

This function creates a new string starting from a fixed number of characters into the original string.

Syntax 

SUBSTRING(strExpr FROM starting_position)

Where:

strExpr is any expression that evaluates to a character string.

starting_position is any positive integer that represents the number of characters from the start of the left side of the string where the result is to begin.

Example:

SUBSTRING("Requisition Department"."Department Name",5,6)

Will give you 6 characters of the Requisition Department Name, starting at position 5.

TRIMBOTH

This function strips specified leading and trailing characters from a character string.

Syntax 

TRIM(BOTH character FROM strExpr)

Where:

character is any single character. If you omit this specification (and the required single quotes), a blank character is used as the default.

strExpr is any expression that evaluates to a character string.

Example:

TRIM(BOTH '0' from "Requisition Identification"."Req. Identifier")

This will remove all leading and trailing zeros from the Requisition Number. Zeros in the middle will remain.

TRIMLEADING

This function strips specified leading characters from a character string.

Syntax 

TRIM(LEADING character FROM strExpr)

Where:

character is any single character. If you omit this specification (and the required single quotes), a blank character is used as the default.

strExpr is any expression that evaluates to a character string.

Example:

TRIM(LEADING '0' from "Requisition Identification"."Req. Identifier")

This will remove all leading zeros from the Requisition Number.

TRIMTRAILING

This function strips specified trailing characters from a character string.

Syntax 

TRIM(TRAILING character FROM strExpr)

Where:

character is any single character. If you omit this specification (and the required single quotes), a blank character is used as the default.

strExpr is any expression that evaluates to a character string.

Example:

TRIM(TRAILING '0' from "Requisition Identification"."Req. Identifier")

This will remove all trailing zeros from the Requisition Number.

UPPER

This function converts a character string to uppercase.

Syntax 

UPPER(strExpr)

Where:

strExpr is any expression that evaluates to a character string.

Example:

UPPER("Candidate Identification"."Name")

This will convert all lower case characters to upper case within the Candidate Name.

Math Functions

The math functions perform mathematical operations.

ABS

This function calculates the absolute value of a numeric expression.

Syntax 

ABS(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

Example:

ABS(TIMESTAMPDIFF(SQL_TSI_DAY, "Submission Dates"."Submission Created Date", "Submission Dates"."Hire Start Date"))

This will give you the absolute value of the days between the Submission Created Date and the Hire Start Date. If the Hire Start Date is before the Submission Creation Date resulting in negative days between, ABS will give you the positive equivalent. For example, if days between is -2, ABS will show a value of 2.

CEILING

This function rounds a noninteger numeric expression to the next highest integer. If the numeric expression evaluates to an integer, the CEILING function returns that integer.

Syntax 

CEILING(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

FLOOR

Example:

CEILING("Candidate Counts"."# Submissions Complete"/"Candidate Counts"."# Submissions")

This will round the ratio value to the next highest integer.

This function rounds a noninteger numeric expression to the next lowest integer. If the numeric expression evaluates to an integer, the FLOOR function returns that integer.

Syntax 

FLOOR(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

Example:

FLOOR("Candidate Counts"."# Submissions Complete"/"Candidate Counts"."# Submissions")

This will round the ratio value to the next lowest integer.

RAND

Returns a pseudo-random number between 0 and 1.

Syntax 

 RAND()

This function rounds a numeric expression to n digits of precision.

Syntax

ROUND(numExpr, integer)

Where:

numExpr is any expression that evaluates to a numeric value.

integer is any positive integer that represents the number of digits of precision. 

Example:
ROUND("Candidate Counts"."# Submissions Complete"/"Candidate Counts"."# Submissions",3)

This will round the value of the ratio to 3 digits.

NOTE: The Data Format in Column Properties will need to be configured to more than 2 digits to see up to the 3 digits specified in the formula.

TRUNCATE

This function truncates a decimal number to return a specified number of places from the decimal point.

Syntax 

 TRUNCATE(numExpr, integer)

Where:

numExpr is any expression that evaluates to a numeric value.

integer is any positive integer that represents the number of characters to the right of the decimal place to return. 

Example:
TRUNCATE("Candidate Counts"."# Submissions Complete"/"Candidate Counts"."# Submissions",3)

This will remove the characters after the third decimal place without rounding the number. If the ratio yields 0.8745, TRUNCATE will show 0.874 (ROUND will show 0.875).

NOTE: The Data Format in Column Properties will need to be configured to more than 2 digits to see up to the 3 digits specified in the formula.

Calendar Date/Time Functions

The calendar date/time functions manipulate data of the data types DATE and DATETIME based on a calendar year. You must select these functions with another column; they cannot be selected alone.

CURRENT_DATE

This function returns the current date. The date is determined by the system in which the Oracle BI Server is running.

Syntax

CURRENT_DATE

Example:

TIMESTAMPDIFF(SQL_TSI_DAY, "Requisition Dates"."First Fully Approved Date", CURRENT_DATE)

This will return the days between the First Fully Approved Date and today.

CURRENT_TIME

This function returns the current time. The time is determined by the system in which the Oracle BI Server is running.

Syntax 

CURRENT_TIME(integer)

Where:

integer is any integer representing the number of digits of precision with which to display the fractional second. The argument is optional; the function returns the default precision when no argument is specified.

CURRENT_TIMESTAMP

This function returns the current date/timestamp. The timestamp is determined by the system in which the Oracle BI Server is running.

Syntax

CURRENT_TIMESTAMP(integer)

Where:

integer is any integer representing the number of digits of precision with which to display the fractional second. The argument is optional; the function returns the default precision when no argument is specified.

DAY_OF_QUARTER

This function returns a number (between 1 and 92) corresponding to the day of the quarter for the specified date.

Syntax 

DAY_OF_QUARTER(dateExpr)

Where:

dateExpr is any expression that evaluates to a date.

Example:

DAY_OF_QUARTER(“Requisition Dates”.”First Fully Approved Date”)

This will return the corresponding day of the quarter for the First Fully Approved Date.

DAYNAME

This function returns the name of the day of the week for a specified date.

Syntax

DAYNAME(dateExpr)

Where:

dateExpr is any expression that evaluates to a date.

DAYOFMONTH

This function returns the number corresponding to the day of the month for a specified date.

Syntax

DAYOFMONTH(dateExpr)

Where:

dateExpr is any expression that evaluates to a date.

DAYOFWEEK

This function returns a number between 1 and 7 corresponding to the day of the week, Sunday through Saturday, for a specified date. For example, the number 1 corresponds to Sunday, and the number 7 corresponds to Saturday.

Syntax

DAYOFWEEK(dateExpr)

Where:

dateExpr is any expression that evaluates to a date.

DAYOFYEAR

This function returns the number (between 1 and 366) corresponding to the day of the year for a specified date.

Syntax

DAYOFYEAR(dateExpr)

Where:

dateExpr is any expression that evaluates to a date.

HOUR

This function returns a number (between 0 and 23) corresponding to the hour for a specified time. For example, 0 corresponds to 12 a.m. and 23 corresponds to 11 p.m.

Syntax

HOUR(timeExpr)

Where:

timeExpr is any expression that evaluates to a time.

MINUTE

This function returns a number (between 0 and 59) corresponding to the minute for a specified time.

Syntax

MINUTE(timeExpr)

Where:

timeExpr is any expression that evaluates to a time.

MONTH

This function returns the number (between 1 and 12) corresponding to the month for a specified date.

Syntax

MONTH(dateExpr)

Where:

dateExpr is any expression that evaluates to a date.

MONTH_OF_QUARTER

This function returns the number (between 1 and 3) corresponding to the month in the quarter for a specified date.

Syntax

MONTH_OF_QUARTER(dateExpr)

Where:

dateExpr is any expression that evaluates to a date.

MONTHNAME

This function returns the name of the month for a specified date.

Syntax

MONTHNAME(dateExpr)

Where:

dateExpr is any expression that evaluates to a date.

NOW

This function returns the current timestamp. The NOW function is equivalent to the CURRENT_TIMESTAMP function.

Syntax

NOW()

QUARTER_OF_YEAR

This function returns the number (between 1 and 4) corresponding to the quarter of the year for a specified date.

Syntax

QUARTER_OF_YEAR(dateExpr)

Where:

dateExpr is any expression that evaluates to a date.

SECOND

This function returns the number (between 0 and 59) corresponding to the seconds for a specified time.

Syntax

SECOND(timeExpr)

Where:

timeExpr is any expression that evaluates to a time.

TIMESTAMPADD

This function adds a specified number of intervals to a specified timestamp, and returns a single timestamp.

In the simplest scenario, this function adds the specified integer value to the appropriate component of the timestamp, based on the interval. Adding a week translates to adding seven days, and adding a quarter translates to adding three months. A negative integer value results in a subtraction (such as going back in time).

An overflow of the specified component (such as more than 60 seconds, 24 hours, 12 months, and so on) necessitates adding an appropriate amount to the next component. For example, when adding to the day component of a timestamp, this function considers overflow and takes into account the number of days in a particular month (including leap years when February has 29 days).

When adding to the month component of a timestamp, this function verifies that the resulting timestamp has enough days for the day component. For example, adding 1 month to 2000-05-31 does not result in 2000-06-31 because June does not have 31 days. This function reduces the day component to the last day of the month, 2000-06-30 in this example.

A similar issue arises when adding to the year component of a timestamp having a month component of February and a day component of 29 (that is, last day of February in a leap year). If the resulting timestamp does not fall on a leap year, the function reduces the day component to 28.

These actions conform to the behavior of Microsoft SQL Server and the native OCI interface for Oracle Database.

Syntax

TIMESTAMPADD(interval, intExpr, timestamp)

Where:

interval is the specified interval. Valid values are:

  • SQL_TSI_SECOND

  • SQL_TSI_MINUTE

  • SQL_TSI_HOUR SQL_TSI_DAY

  • SQL_TSI_WEEK

  • SQL_TSI_MONTH

  • SQL_TSI_QUARTER

  • SQL_TSI_YEAR

intExpr is any expression that evaluates to an integer value.

timestamp is any valid timestamp. This value is used as the base in the calculation.

A null integer expression or a null timestamp passed to this function results in a null return value.

Example:

TIMESTAMPADD(SQL_TSI_DAY, 7, CURRENT_DATE)

This will return a value of one week from today.

TIMESTAMPDIFF

This function returns the total number of specified intervals between two timestamps.

This function first determines the timestamp component that corresponds to the specified interval parameter, and then looks at the higher order components of both timestamps to calculate the total number of intervals for each timestamp. For example, if the specified interval corresponds to the month component, the function calculates the total number of months for each timestamp by adding the month component and twelve times the year component. Then the function subtracts the first timestamp's total number of intervals from the second timestamp's total number of intervals.

Note: This section describes the TIMESTAMPDIFF behavior when the function is calculated in the Oracle BI Server. If this function is calculated in the data source, then the result might be different from the behavior described in this section. If the TIMESTAMPDIFF function result is different from the desired result, then you can disable TIMESTAMP_DIFF_SUPPORTED in the Features tab for the database object in the Administration Tool to ensure that the function is calculated in the Oracle BI Server. However, making this change might adversely affect performance.

The TIMESTAMPDIFF function rounds up to the next integer whenever fractional intervals represent a crossing of an interval boundary. For example, the difference in years between 1999-12-31 and 2000-01-01 is one year because the fractional year represents a crossing from one year to the next (such as 1999 to 2000). By contrast, the difference between 1999-01-01 and 1999-12-31 is zero years because the fractional interval falls entirely within a particular year (that is, 1999). Microsoft SQL Server exhibits the same rounding behavior, but IBM DB2 does not; it always rounds down.

When calculating the difference in weeks, the function calculates the difference in days and divides by seven before rounding. Additionally, the function takes into account how the parameter FIRST_DAY_OF_THE_WEEK has been configured in the NQSConfig.INI file. For example, with Sunday as the start of the week, the difference in weeks between 2000-07-06 (a Thursday) and 2000-07-10 (the following Monday) results in a value of 1 week. With Tuesday as the start of the week, however, the function would return zero weeks since the fractional interval falls entirely within a particular week. When calculating the difference in quarters, the function calculates the difference in months and divides by three before rounding.

The Oracle BI Server pushes down the TIMESTAMPADD and TIMESTAMPDIFF functions to Microsoft SQL Server, Oracle Database, IBM DB2, and ODBC databases by default.

Syntax

TIMESTAMPDIFF(interval, timestamp1, timestamp2)

Where:

interval is the specified interval. Valid values are:

  • SQL_TSI_SECOND

  • SQL_TSI_MINUTE

  • SQL_TSI_HOUR

  • SQL_TSI_DAY

  • SQL_TSI_WEEK

  • SQL_TSI_MONTH

  • SQL_TSI_QUARTER

  • SQL_TSI_YEAR

timestamp1 and timestamp2 are any valid timestamps.

A null timestamp parameter passed to this function results in a null return value.

Example:

TIMESTAMPDIFF(SQL_TSI_DAY, "Requisition Dates"."First Sourced Date", "Requisition Dates"."Latest Filled Date")

This will return the days between the First Sourced Date and the Latest Filled Date.

WEEK_OF_QUARTER

This function returns a number (between 1 and 13) corresponding to the week of the quarter for the specified date.

Syntax 

WEEK_OF_QUARTER(dateExpr)

Where:

dateExpr is any expression that evaluates to a date.

WEEK_OF_YEAR

This function returns a number (between 1 and 53) corresponding to the week of the year for the specified date.

Syntax 

WEEK_OF_YEAR(dateExpr)

Where:

dateExpr is any expression that evaluates to a date.

YEAR

This function returns the year for the specified date.

Syntax

YEAR(dateExpr)

Where:

dateExpr is any expression that evaluates to a date.

Example:

YEAR("Requisition Dates"."First Sourced Date")

This will give you the year of the First Sourced Date.

Conversion Functions

The conversion functions convert a value from one form to another. You can also use the VALUEOF function in a filter to reference the value of an Oracle BI system variable.

CAST

This function changes the data type of an expression or a null literal to another data type. For example, you can cast a customer_name (a data type of Char or Varchar) or birthdate (a datetime literal). The following are the supported data types to which the value can be changed:

CHARACTER, VARCHAR, INTEGER, FLOAT, SMALLINT, DOUBLE PRECISION, DATE, TIME, TIMESTAMP, BIT, BIT VARYING

Depending on the source data type, some destination types are not supported. For example, if the source data type is a BIT string, the destination data type must be a character string or another BIT string.

Use CAST to change to a DATE data type. Do not use TO_DATE.

The following describes unique characteristics of the CHAR and VARCHAR data types:

  • Casting to a CHAR data type. You must use a size parameter. If you do not add a size parameter, a default of 30 is added. Syntax options appear in the following list:

    • The recommended syntax is: 

      CAST(expr|NULL AS CHAR(n))

      For example: 

      CAST(companyname AS CHAR(35))

    • You can also use the following syntax: 

      CAST(expr|NULL AS data_type)

      For example: 

      CAST(companyname AS CHAR)
      Note: If you use this syntax, the Oracle BI Server explicitly converts and stores as CAST(expr|NULL AS CHAR(30))

  • Casting to a VARCHAR data type. You must use a size parameter. If you omit the size parameter, you cannot can save the change.

Examples:
CAST("Requisition Identification"."Req. Identifier" AS CHAR(40))

CAST("Submission Job Offer"."Sequence" AS VARCHAR(40))

CAST(AVG(“Candidate Counts”.”# Submissions”) AS DOUBLE)

CAST("Submission Dates"."Hire Start Date" AS DATE)

CAST(TIMESTAMPDIFF(SQL_TSI_DAY, "Submission Dates"."Submission Created Date", CURRENT_DATE) AS INTEGER)

IFNULL

This function tests if an expression evaluates to a null value, and if it does, assigns the specified value to the expression.

Syntax 

IFNULL(expr, value)

Where:

expr is the expression to evaluate.

value is the value to assign if the expression evaluates to a null value.

Example:

IFNULL("Submission Job Offer Flex Field”.”UDF1”, “Requisition Compensation Flex Fields”.”UDF1”)

This will return the value for Requisition Compensation UDF1 if the value for Submission Job Offer UDF1 is null.

TO_DATETIME

This function converts string literals of dateTime format to a DateTime data type.

Syntax 

TO_DATETIME('string1', 'DateTime_formatting_string')

Where:

string1 is the string literal you want to convert

DateTime_formatting_string is the DateTime format you want to use, such as yyyy.mm.dd hh:mi:ss. For this argument, yyyy represents year, mm represents month, dd represents day, hh represents hour, mi represents minutes, and ss represents seconds. 

Example:
TO_DATETIME("Requisition Flex Fields"."UDF4", 'mm-dd-yyyy')

This will convert UDF4 to a date/time data type while also enabling the date format to be specified.

Database Functions

Users and administrators can create requests by directly calling database functions from either Oracle BI Answers, or by using a logical column (in the logical table source) within the metadata repository. Key uses for these functions include the ability to pass through expressions to get advanced calculations, as well as the ability to access custom written functions or procedures on the underlying database.

Support for database functions does not currently extend across all multidimensional sources. Also, you cannot use these functions with XML data sources.

EVALUATE

This function passes the specified database function with optional referenced columns as parameters to the back-end data source for evaluation. This function is intended for scalar calculations, and is useful when you want to use a specialized database function that is not supported by the Oracle BI Server, but that is understood by the underlying data source.

The embedded database function may require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function.

Syntax 

EVALUATE('db_function(%1...%N)' [AS data_type] [, column1, columnN])

Where:

db_function is any valid database function understood by the underlying data source.

data_type is an optional parameter that specifies the data type of the return result. Use this parameter whenever the return data type cannot be reliably predicted from the input arguments. However, do not use this parameter for type casting; if the function needs to return a particular data type, add an explicit cast. You can typically omit this parameter when the database-specific function has a return type not supported by the Oracle BI Server, but is used to generate an intermediate result that does not need to be returned to the Oracle BI Server.

column1 through columnN is an optional, comma-delimited list of columns. 

Example:
EVALUATE('NLS_INITCAP(%1)' AS VARCHAR(4000),"Requisition Identification"."Title (BL)")

This will capitalize the first letter in every word in the Requisition Title.

EVALUATE_ANALYTIC

This function passes the specified database analytic function with optional referenced columns as parameters to the back-end data source for evaluation.

The embedded database function may require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function.

Syntax 

EVALUATE_ANALYTIC('db_function(%1...%N)' [AS data_type] [, column1, columnN])

Where:

db_function is any valid database analytic function understood by the underlying data source.

data_type is an optional parameter that specifies the data type of the return result. Use this parameter whenever the return data type cannot be reliably predicted from the input arguments. However, do not use this parameter for type casting; if the function needs to return a particular data type, add an explicit cast. You can typically omit this parameter when the database-specific analytic function has a return type not supported by the Oracle BI Server, but is used to generate an intermediate result that does not need to be returned to the Oracle BI Server.

column1 through columnN is an optional, comma-delimited list of columns.

Example:

EVALUATE_ANALYTIC ('LEAD(%1,1) OVER (ORDER BY %1)' AS TIMESTAMP,"Submission Dates"."Hired Date")

This will return the next hire according to the Hired Date.

EVALUATE_AGGR

This function passes the specified database function with optional referenced columns as parameters to the back-end data source for evaluation. This function is intended for aggregate functions with a GROUP BY clause.

The embedded database function may require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function.

Syntax 

EVALUATE_AGGR('db_agg_function(%1...%N)' [AS data_type] [, column1, columnN)

Where:

db_agg_function is any valid aggregate database function understood by the underlying data source.

data_type is an optional parameter that specifies the data type of the return result. Use this parameter whenever the return data type cannot be reliably predicted from the input arguments. However, do not use this parameter for type casting; if the function needs to return a particular data type, add an explicit cast. You can typically omit this parameter when the database-specific function has a return type not supported by the Oracle BI Server, but is used to generate an intermediate result that does not need to be returned to the Oracle BI Server.

column1 through columnN is an optional, comma-delimited list of columns. 

Example:
EVALUATE_AGGR('COUNT(DISTINCT CASE WHEN %1 = %2 THEN %3 END)' AS NUMERIC,"Submission CSW Status - Current"."Current Step Name","Interview","Submission General Info"."Submission Identifier")

This will return the number of submissions currently in the Step of Interview.