Advanced Formulas

This page is for storing various SQL formulas and regular expressions for potential re-use in Alma Analytics. The SQL code will state whether it should be used as columns or as filters.

Advanced Formula Definitions (Read First)

Column Formula: If a column formula is altered then the content produced in the column will be different.  It can be altered with a filter or with other elements.  When altered by a filter command, then the column–and its content–can be filtered by criteria from a different column.  That way a column can contain a filter from a different column.

The content of a column can also be altered in the column formula.  For example "Unknown" could be changed to "Non-LC Call number."  A filter applied within column formula is a filter from another column.  These alter the sets that the column produces.

Filter: Whatever the column generates, a filter chooses a part of that content to show.  It does not alter the content of the column formula, it just determines what will be shown from based on column's properties.

Budget String Filters

See also: Budget String Components

TUB (filters)

SUBSTRING("Fund Ledger"."Fund Ledger Code" FROM 1 FOR 3)

ORG (filters)

SUBSTRING("Fund Ledger"."Fund Ledger Code" FROM 5 FOR 5)

OBJECT (none)

The OBJECT designator needs no SQL - use the Reporting Code in Analytics.

FUND (filters)

SUBSTRING("Fund Ledger"."Fund Ledger Code" FROM 12 FOR 6)

ACTIVITY (filters)

SUBSTRING("Fund Ledger"."Fund Ledger Code" FROM 19 FOR 6)

SUB_ACTIVITY (filters)

SUBSTRING("Fund Ledger"."Fund Ledger Code" FROM 26 FOR 4)

ROOT (filters)

SUBSTRING("Fund Ledger"."Fund Ledger Code" FROM 31 FOR 6)

Time Period Manipulation

Budget Year (filters)

CAST(RIGHT("Fiscal Period"."Fiscal Period Description", 4) AS integer)

Current Fiscal Year (filters)

"Fiscal Period"."Fiscal Period Start Date" >= ((YEAR(CURRENT_DATE)-1) * 10000 + MONTH(CURRENT_DATE) * 100 + DAY(CURRENT_DATE))

RANK(CAST(RIGHT("Fiscal Period"."Fiscal Period Description", 4) AS integer),1) = 1

Last Three Fiscal Years (filters)

"Fiscal Period"."Fiscal Period Start Date" 
BETWEEN 
((YEAR(CURRENT_DATE) -4) * 10000 + MONTH(CURRENT_DATE) * 100 + DAY(CURRENT_DATE)) 
AND 
((YEAR(CURRENT_DATE) -1) * 10000 + MONTH(CURRENT_DATE) * 100 + DAY(CURRENT_DATE))

Previous Fiscal Year (filters)

"Fiscal Period"."Fiscal Period Start Date" 
BETWEEN 
((YEAR(CURRENT_DATE) -2) * 10000 + MONTH(CURRENT_DATE) * 100 + DAY(CURRENT_DATE)) 
AND 
((YEAR(CURRENT_DATE) -1) * 10000 + MONTH(CURRENT_DATE) * 100 + DAY(CURRENT_DATE))

Prior/Future XXX Time Period Formula and Between XXX Time Periods (filters)

[Insert Alma Column SQL] = TIMESTAMPADD(SQL_TSI_DAY,-1,CURRENT_DATE)

[Insert Alma Column SQL] = TIMESTAMPADD(SQL_TSI_[DAY,MONTH,WEEK,YEAR],-1,CURRENT_DATE)

[Insert Alma Column SQL] = TIMESTAMPADD(SQL_TSI_YEAR, -10, CURRENT_DATE)

"Due Date"."Due Date" = TIMESTAMPADD(SQL_TSI_YEAR, -10, CURRENT_DATE)

MONTH("Invoice Line"."Invoice-Date") = MONTH(TIMESTAMPADD(SQL_TSI_MONTH,3,CURRENT_DATE))

"Due Date"."Due Date" 
BETWEEN 
TIMESTAMPADD(SQL_TSI_WEEK, -14, CURRENT_DATE) 
AND 
TIMESTAMPADD(SQL_TSI_DAY, -1, CURRENT_DATE)

BETWEEN TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD(SQL_TSI_DAY, DAYOFMONTH(CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY, -(1), TIMESTAMPADD(SQL_TSI_DAY, DAYOFMONTH(CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

Difference Between Dates (column formula)

TIMESTAMPDIFF([interval], [Insert Alma Column SQL], [timestamp]) 

TIMESTAMPDIFF(SQL_TSI_DAY, "Creation Date"."Creation Date", TIMESTAMP '2023-06-30 23:59:59') 

TIMESTAMPDIFF(SQL_TSI_DAY, "Creation Date"."Creation Date", CURRENT_DATE) 

EVALUATE and EVALUATE_AGGR Database Functions

EVALUATE and EVALUATE_AGGR functions allow database functions not otherwise available in Alma Analytics (OBIEE) to be used. These database functions allow advanced calculations and formatting and regular expression searching.

EVALUATE

EVALUATE('database function(%1,%2 ... %N)' [AS datatype], "Alma Column SQL", 'regular expression' [, %N parameters separated by commas])

Required parameters:

• %1 is the Alma column SQL (for example, ,"Holding Details"."852 MARC").
• %2 is the regular expression.

Other common parameters (parameter number varies depending on database function and these parameters are often optional):

• position: This is the starting position. The default is 1 (starting with the first character).
• occurrence: The default is 1 (first occurrence).
• match_parameter: This is the parameter for matching. NULL means no changes are made to the default. Other options are 'i' (case-insensitive matching), 'c' (case-sensitive matching), 'n' (allow '.' to match the newline character), 'm' (treat string as multiple lines), and 'x' (ignore whitespace characters).
• subexpr: This is for specifying capture groups within a regular expression. The default is 1 (first subexpression), and the value must be an integer from 0 to 9.

EVALUATE_AGGR

Enables aggregate functions with a GROUP BY clause

LISTAGG

Orders data in a group and concatenates values from a column

EVALUATE_AGGR('listagg(%1,%2) WITHIN GROUP (ORDER BY %3)', [Insert Alma Column SQL], 'delimiter', [Insert Alma Column SQL])

• %1 measure_expr: Alma column SQL to be concatenated (for example, "Physical Item Details"."Barcode")
• %2 delimiter_expr: Delimiter to separate values in the concatenated list (for example, ' | ' or ', ')
• %3 order_by_clause: Alma column SQL for ordering the list

The default order is ascending. For descending order, use "(ORDER BY %3 DESC)".

REGEXP_COUNT

Counts the number of times a pattern occurs

EVALUATE('regexp_count(%1,%2,%3,%4)', [Insert Alma Column SQL], 'regular expression', 1, 'c')

Optional parameters:

• %3 position
• %4 match_parameter

REGEXP_INSTR

An extension of the INSTR function

EVALUATE('regexp_instr(%1,%2,%3,%4,%5,%6)', [Insert Alma Column SQL], 'regular expression', 1, 1, 0, 'c')

Optional parameters:

• %3 position
• %4 occurrence
• %5 return_option: This can be either 0 or 1, with 0 as the default. 0 returns the character position of the occurrence, and 1 returns the character position following the occurrence.
• %6 match_parameter

The Ex Libris Tech Blog post Alma Analytics Regular Expression Examples shows how this function can be used for filtering. The examples in that post are formatted with only the Alma column as a parameter. Either format is valid:

EVALUATE('regexp_instr(%1, ''^DISC+'', 1, 1, 0, ''c'')', "Physical Item Details"."Public Note")
EVALUATE('regexp_instr(%1,%2,%3,%4,%5,%6)', "Physical Item Details"."Public Note", '^DISC+', 1, 1, 0, 'c')

REGEXP_REPLACE

An extension of the REPLACE function

EVALUATE('regexp_replace(%1,%2,%3,%4,%5,%6)', [Insert Alma Column SQL], 'regular expression', 'replace string', 1, 0, 'c')

• %3 replace_string: The string or regular expression to replace the value found by the regular expression in %2.

Optional parameters:

• %4 position
• %5 occurrence: The occurrence to be replaced. If 0, all occurrences will be replaced.
• %6 match_parameter

REGEXP_SUBSTR

An extension of the SUBSTR function

EVALUATE('regexp_substr(%1,%2,%3,%4,%5,%6)', [Insert Alma Column SQL], 'regular expression', 1, 1, NULL, 1)

Optional parameters:

• %3 position
• %4 occurrence
• %5 match_parameter
• %6 subexpr

Miscellaneous SQL

Modifying a Portion of a Column with a Case Statement (column formula)

CASE WHEN [Insert Alma Column SQL] = ['Result you want replaced'] THEN  ['New result'] ELSE [Repeat Insert Alma Column SQL] END

CASE 
WHEN "LC Classifications"."Classification Code" = 'Unknown' 
THEN  
'Unclassed or mis-coded' 
ELSE "LC Classifications"."Classification Code" 
END

Listing Repeated Fields in Different Columns (column formula)

EVALUATE('regexp_substr(%1,%2,%3,%4)', [Insert Alma Column SQL], '[^\; ]+', 1, 1)

The regular expression locates all characters up to the first occurrence of a semicolon followed by a space. The delimiter for concatenated values in Alma is "; " (semicolon followed by a space). The semicolon needs to be escaped for the regular expression.

This formula retrieves the first ISBN value.

EVALUATE('regexp_substr(%1,%2,%3,%4)', "Bibliographic Details"."ISBN", '[^\; ]+', 1, 1)

Repeat the formula and increment %4 to create columns for multiple ISBN values.

EVALUATE('regexp_substr(%1,%2,%3,%4)', "Bibliographic Details"."ISBN", '[^\; ]+', 1, 2)

The results are like this:

Adding One or More Filters to a Column (column formula)

FILTER([Insert Alma Column SQL to be filtered] USING ([Insert Alma Column SQL for filter] = [Content of filter]))

FILTER
("Fund Transactions"."Sum" 
USING 
("Fund Transactions Details"."Transaction Item Type" = 'ALLOCATION'))

FILTER(FILTER(SUM([Insert Alma Column SQL]) USING ([Insert Alma Column SQL Filter 1] = '[Content of filter 1]'))  USING ([Insert Alma Column SQL Filter 2] = [Content of filter 2]))

FILTER(FILTER(SUM
("Fund Transactions"."Transaction Amount") 
USING 
("Fund Transaction Details"."Transaction Item Type" = 'ALLOCATION'))
USING 
("Transaction Date"."Transaction Date Fiscal Year" = 2015))

Counting (column formula)

COUNT([Insert Alma Column SQL])

COUNT("Bibliographic Details"."MMS Id")

COUNT(DISTINCT "Holding Details"."Holding Id")

Count Repeated Fields in a Concatenated Field (column formula)

IFNULL(CAST(EVALUATE('REGEXP_COUNT(%1,%2)', [Insert Column SQL], '\;') AS INT), -1) + 1

REGEXP_COUNT returns the number of times that a semicolon occurs. IFNULL replaces the returned NULL value of zero (0) with minus one (-1) instead of zero (0). One (1) is added to count the field that is not followed by a semicolon.

This formula counts the number of OCNs in the OCLC Control Number (035a) field.

IFNULL(CAST(EVALUATE('REGEXP_COUNT(%1,%2)',"Physical Items"."Bibliographic Details"."OCLC Control Number (035a)", '\;') AS INT), -1) + 1

Row Numbers (column formula)

This code will provide a column that will count the number of rows in an analysis.

RCOUNT(1)

This formula will give you the total number of lines. Use this in the header/title area.

MAX(RCOUNT(1))

This formula will give you the total number of MMS ID lines including any duplicates.

MAX(RCOUNT("Bibliographic Details"."MMS Id"))

Concatenate Two Fields with a Separator (column formula)

[Insert Alma Column SQL 1st position] || '[Separator]' || [Insert Alma Column SQL 2nd position]

"User Details"."Last Name" 
||
',' 
|| "User Details"."First Name"

CONCAT(CONCAT([Insert Alma Column SQL 1st position],' '),[Insert Alma Column SQL 2nd position])

CONCAT(CONCAT
("User Details"."First Name",' ')
,"User Details"."Last Name")

The result of the above looks like this:

You can use this function to solve the problem with the way Excel treats long numbers, such as the MSSID and the Barcode. One way to ensure that MMSIDs and Barcodes (or any other long numbers) translate properly when exported to Excel, is to prepend a non-numeric character or string to the numeric value.

'_' 
|| 
"Bibliographic Details"."MMS Id"

Result: _990107328710203941

'_' || "Physical Item Details"."Barcode"
Result: _32044100361559

This will convert the columns in Excel to text and the numbers will not be rounded off by Excel. When you open the file in Excel, first remove the added character using the Excel function Find and Replace, and then you have full numbers. Note: do not use mathematical symbols.

Percentages (column formula)

[Insert Alma Column SQL 1] / [Insert Alma Column SQL 2] * 100

"Physical Item Details"."Num of Loans - not sum" 
/ 
"Physical Item Details"."Num of Items" 
*
100

Converting to Day of the Week (e.g., Monday, Tuesday, etc.) (column formula)

CASE  
WHEN DAYOFWEEK("Loan Date"."Loan Date") = 1 THEN 'Sun' 
WHEN DAYOFWEEK("Loan Date"."Loan Date") = 2 THEN 'Mon' 
WHEN DAYOFWEEK("Loan Date"."Loan Date") = 3 THEN 'Tue' 
WHEN DAYOFWEEK("Loan Date"."Loan Date") = 4 THEN 'Wed' 
WHEN DAYOFWEEK("Loan Date"."Loan Date") = 5 THEN 'Thu' 
WHEN DAYOFWEEK("Loan Date"."Loan Date") = 6 THEN 'Fri' 
WHEN DAYOFWEEK("Loan Date"."Loan Date") = 7 THEN 'Sat' 
ELSE '' END

Network Number [035, OCLC number] for only OCLC numbers (column formula)

This code will provide a column that will show only the OCLC in the 035, the Network Number.

This code will retrieve the first OCLC number:

EVALUATE('regexp_substr(%1,%2,%3,%4)',"Bibliographic Details"."Network Number", '(\(?[oO0][cC]\w+\)?\W?\d+)', 1, 1)

If you suspect that there are more than one OCLC number in the 035, and there often are, then you modify one number for each OCLC number you may have.

EVALUATE('regexp_substr(%1,%2,%3,%4)',"Bibliographic Details"."Network Number", '(\(?[oO0][cC]\w+\)?\W?\d+)', 1, 1)
EVALUATE('regexp_substr(%1,%2,%3,%4)',"Bibliographic Details"."Network Number", '(\(?[oO0][cC]\w+\)?\W?\d+)', 1, 2)
EVALUATE('regexp_substr(%1,%2,%3,%4)',"Bibliographic Details"."Network Number", '(\(?[oO0][cC]\w+\)?\W?\d+)', 1, 3)
EVALUATE('regexp_substr(%1,%2,%3,%4)',"Bibliographic Details"."Network Number", '(\(?[oO0][cC]\w+\)?\W?\d+)', 1, 4)

Extracting a subfield (column formula)

EVALUATE('regexp_substr(%1,%2,%3,%4)', [Insert Alma Column SQL], '\$[subfield code][^\$]+', 1, 1)

The regular expression searches for the subfield delimiter and code ($f, for example) followed by one or more characters that are not a dollar sign ($), meaning from the start of the subfield until the end of that subfield.

EVALUATE('regexp_substr(%1,%2,%3,%4)', "Holding Details"."Holding Local Param 03", '\$f[^\$]+', 1, 1)

To extract the subfield text without the subfield delimiter and code ("abc123" instead of "$f abc123"), edit the regular expression to include a capture group using parentheses and add match and subexpression parameters to the regexp_substr function. The "i" (in double single quotes) means case-insensitive matching, and the third 1 specifies the first capture group (the "([^\$]+)" following "\$f " in the regular expression).

EVALUATE('regexp_substr(%1,%2,%3,%4,%5,%6)', "Holding Details"."Holding Local Param 03", '\$f ([^\$]+)', 1, 1, 'i', 1)

Use trim functions to remove leading or trailing white-space and trailing punctuation ("abc123" instead of " abc123; ").

TRIM(TRAILING ';' FROM TRIM(BOTH FROM EVALUATE('regexp_substr(%1,%2,%3,%4,%5,%6)', 
"Holding Details"."Holding Local Param 03", '\$f ([^\$]+)', 1, 1, 'i', 1)))

Comments in SQL

You can add explanatory comments in SQL formulas or comment out any portion of a formula that you don't want to delete. One way to enter a comment in SQL is to surround the comment text with asterisks and slashes in this order:

/* Comment */

If you comment out a whole filter in Analytics, add 1=1 (which is always true), so that the filter will not return an error.

/*"Bibliographic Details"."MMS Id" = '99153760346403941'*/1=1

MAX / MIN / Count Distinct "Subtotal" Values in a List Row 

MAX(“xxx.column”  BY “yyy.column")
  Example: MAX("Invoice Line"."Invoice-Date" BY "PO Line"."PO Line Reference") [i.e., most recent invoice date associated with a given PO Line Reference]


MIN(“xxx.column”  BY “yyy.column")
  Example: MAX("Invoice Line"."Invoice-Date" BY "PO Line"."PO Line Reference") [i.e., oldest invoice date associated with a given PO Line Reference]


COUNT(DISTINCT “zzz.column” BY “xyz.column”)
  Example: COUNT(DISTINCT "Invoice Line"."Invoice-Number" BY "PO Line"."PO Line Reference") [i.e., count of invoice numbers associated with a given PO Line Reference

Additional Formulas/SQL Queries from Ex Libris

Ex Libris has a list of other calculations that may be useful.

They also have regular expression examples to use with filters.

Containers for Additional Formulas/SQL Queries

These blank code blocks are here to help the Analytics & Reporting Working Group add more SQL queries to this list. Other staff should ignore them.

Placeholder for next formula

Placeholder for next formula

Placeholder for next formula