Reporting

Reporting is primarily done by the Alma Tech Team, based on formal requests submitted via the Alma Data Report Request form (found on the Support and Request Forms page). One of the following user profiles is needed to create analytics reports: Selector View Only and Analytics, Cataloging Admin, Cataloging Extended Plus, or Cataloging Extended. 

While you may have the ability to create analytics reports in Alma, please note that you are responsible for the accuracy of any reports you create. If you would like to ensure the accuracy of a report, submit the request form noted above.

Analytics is a system outside of Alma, which points to a nightly archive of our Alma data. This means that the data available to Analytics is from the previous day (it is not real time).

Parsing Data with Formulas

Harvard's Alma wiki provides very helpful documentation for parsing fields in local parameters: https://wiki.harvard.edu/confluence/display/LibraryStaffDoc/Advanced+Formulas#AdvancedFormulas-Extractingasubfield(columnformula)

See also: https://knowledge.exlibrisgroup.com/Alma/Community_Knowledge/A_Few_Helpful_Alma_Analytics_Queries

Other useful formula examples:

• To get the value of 902$a choose 902 - Local Param 01  > Edit Formula >
  EVALUATE('regexp_substr(%1,%2,%3,%4,%5,%6)', "Bibliographic Details"."Local Param 01", '\$a ([^\$]+)', 1, 1, 'i', 1)

• Get the LCCN choose Bibliographic Id > Edit Formula > EVALUATE('regexp_substr(%1,%2,%3,%4)', "Bibliographic Details"."Bibliographic Id", '[^\/a-z ]+', 1, 1)

• Get count of constituent records for a bound-with: IFNULL(CAST(EVALUATE('REGEXP_COUNT(%1,%2)', "Bibliographic Details"."Local Param 06", '\;') AS INT), -1) + 1

• Normalize the publication date: Evaluate('regexp_substr(%1,''[0-9]{4}'', 1,1)',"Bibliographic Details"."Publication Date")

• Get bibs with multiple locations (filter): COUNT(DISTINCT "Location"."Location Code" BY "Bibliographic Details"."MMS Id") > 1)

• Convert a calendar date field to a year-month key (YYYY-MM) (note: this is not the same as "formatting" which is for display):

  • ^{TRIM(TRAILING ' ' FROM CAST(YEAR("Physical Item Details"."Receiving Date (Calendar)") AS CHAR))
    || '-' ||
    case when
    length(cast(MONTH("Physical Item Details"."Receiving Date (Calendar)") as char)) = 1  then '0'||
    cast(MONTH("Physical Item Details"."Receiving Date (Calendar)") as char)
    else
    cast(MONTH("Physical Item Details"."Receiving Date (Calendar)") as char)
    end}

• Equate the end of a hold shelf "event" (Physical Items Historical Changes subject area) with the beginning of a loan (Fulfillment subject area):

• find same date and overlapping time (to the minute, because there can be a few seconds difference)
  
  

  • ^{CASE WHEN  (CAST("Fulfillment"."Loan Details"."Loan Date" as DATE) = "Physical Items Historical Events"."Event End Date"."Event End Date")
    THEN 'YES'  ELSE 'NO'  END}

  • ^{CASE WHEN SUBSTRING("Fulfillment"."Loan Details"."Loan Time" FROM 1 FOR 5) = SUBSTRING(CAST("Event End Date"."Event End Time" AS CHAR) FROM 12 FOR 5)
    THEN 'YES' ELSE 'NO' END}

• Test for current fiscal period

  • ^{CASE WHEN TRIM(TRAILING ' ' FROM CAST(YEAR(CURRENT_DATE) AS CHAR)) ||
    case when length(cast(MONTH(CURRENT_DATE) as char)) = 1  then '0' ||
    cast(MONTH(CURRENT_DATE) as char)  else    cast(MONTH(CURRENT_DATE) as char) end
    BETWEEN SUBSTRING("Fund Fiscal Period"."Fund Fiscal Period Start Date" FROM 1 FOR 6)
    and SUBSTRING("Fund Fiscal Period"."Fund Fiscal Period End Date" FROM 1 FOR 6)
    THEN 'YES'
    ELSE 'NO'
    END}