(System) Alma Analytics Voyager Historical Browses and Charges Formula
This page documents the formula for parsing the Voyager historical charge and browse counts in the Alma Analytics reporting environment.
To add the Voyager historical charges count to a report:
- Create a new report in Alma Analytics with access to the Physical Item Details dimension (such as Physical Items or Fulfillment)
- Add the Physical Item Details > Internal Note 3 field to the report
- Click on the Edit Formula link for the field
- Paste the formula below into the Column Formula input box
Alma Formula
CASE
WHEN Evaluate('regexp_instr(%1, %2)', "Physical Item Details"."Internal Note 3", 'HISTORICAL_CHARGES: (\d+)') != 0
THEN CAST(
Evaluate(
'regexp_substr(%1, %2, %3, %4, %5, %6)',
"Physical Item Details"."Internal Note 3",
'HISTORICAL_CHARGES: (\d+)',
1,
1,
'c',
1
)
as INT)
ELSE CAST('0' as INT)
END
WHEN Evaluate('regexp_instr(%1, %2)', "Physical Item Details"."Internal Note 3", 'HISTORICAL_CHARGES: (\d+)') != 0
THEN CAST(
Evaluate(
'regexp_substr(%1, %2, %3, %4, %5, %6)',
"Physical Item Details"."Internal Note 3",
'HISTORICAL_CHARGES: (\d+)',
1,
1,
'c',
1
)
as INT)
ELSE CAST('0' as INT)
END
Details
This formula uses an OBIEE case expression to use conditional logic to parse out charge and browse counts when they exist. The formula relies upon the Evaluate() function to make calls to the regular expression (regex) functions. The Evaluate() function takes an initial argument of an Oracle function and uses positional arguments (%1, %2, etc) to pass arguments to the evaluated expressions.
Step 1: Verify the data is parseable:
WHEN Evaluate('regexp_instr(%1, %2)', "Physical Item Details"."Internal Note 3", 'HISTORICAL_CHARGES: (\d+)') != 0
This condition confirms that the historical charge pattern exists in the item record's Internal Note 3 field. It uses a regular expression match against the field to verify the key/value pairing is present. The numerical part of the value must be at least 1 digit. This regex function in Oracle will return the character position of a match or zero if no match is found.
See also: Oracle REGEXP_INSTR Documentation
Step 2: Parse the numerical portion of the pattern.
Evaluate(
'regexp_substr(%1, %2, %3, %4, %5, %6)',
"Physical Item Details"."Internal Note 3",
'HISTORICAL_CHARGES: (\d+)',
1,
1,
'c',
1
)
'regexp_substr(%1, %2, %3, %4, %5, %6)',
"Physical Item Details"."Internal Note 3",
'HISTORICAL_CHARGES: (\d+)',
1,
1,
'c',
1
)
This regexp_substr() function pulls out the numeric part of the historical charges (key) and loan count (value) pairing. (\d+) is a grouping referred to by position %6 as group 1.
See also: Oracle REGEXP_SUBSTR Documentation
Step 3: Cast the result as a number (integer)
CAST( ... as INT)
The result of this expression should return an integer so that the values can be summed up. Both the parsed expression or the default case (zero) are cast to integers.