Miscellaneous Analytics Details

Owned by Karen Kohn (Unlicensed)
Last updated: Sept 29, 2021
4 min read

Loan Count

If you want to include the total number of loans for an item in your Analytics report, you need to use a Physical Items report rather than a Fulfillment report. In the Physical Items report, go to Physical Item Details, then No of Loans. If you use a Fulfillment report, there is a measure called Loans that is actually year-to-date loans, not total.

Exporting MMS IDs

When you export a report to a csv file and open it in Excel, MMS IDs and Item IDs will get rounded, resulting in a number that ends in 000 rather than our institutional identifier of 811. To get around this,

  1. Export the data to csv, but do not open the file.
  2. Change the extension on the file to .txt.
  3. Open Excel.
  4. Go to File > Open, and find the .txt file. Excel will automatically start the Import Wizard.
  5. Select Delimited, click Next.
  6. Select Comma as the delimiter. Click Next.
  7. Highlight the column that has the ID in it. In the top left corner of the wizard, select Text as the data format.
  8. Click Finish.

Limits to Report Length

There is a limit to how many rows of a report can be exported to Excel. According to this page, the limit for a CSV file should be 500,000. Observation on 2/23/18 shows the limit seems to be 499,801.

Cataloging Date

The Cataloging Date can be extracted from Local Parameter 2 by using the SUBSTRING function.

  1. Include Bibliographic Details - Local Param 02 as a column in the Details tab
  2. In the menu options for Local Param 02, choose "edit formula"
  3. Click the f(...) button to Insert Function
  4. Navigate to String → Substring and click OK. You will now see the syntax SUBSTRING("Bibliographic Details"."Local Param 02" FROM startPos FOR length)
  5. We want the first 8 characters after the $$a (skip the first 4 characters), so edit the last part to FROM 5 to 8 and click OK. You can also rename the column to something like "CAT DATE"

Once the Cataloging Date is available in its own column you can filter data to get a report of records cataloged during a given date range, such as between 20170701 and 20180630 to report on FY18. Note that this only works from July 2017 forward (post Alma implementation).

Isolating MARC Subfields

Ex Libris now includes MARC subfields in Analytics Local Parameters. Particularly for Local Param 02, it is possible to split this field into separate columns by subfield.

  1. Include Bibliographic Details - Local Param 02 as a column in the Details tab
  2. In the menu options for Local Param 02, choose "edit formula"
  3. In Column Formula, paste:
    Evaluate('regexp_substr(%1,%2,%3,%4,%5,%6)', "Bibliographic Details"."Local Param 02", '\$\$a([^\$]+)', 1, 1, NULL, 1)
  4. The above example will isolate $$a. To isolate a different subfield, replace 'a' with the subfield of your choice. Repeat the process for additional subfields as needed.

Filtering a Report to Specific IDs

Prep:

  1. If the IDs are in Excel, start by copying the column and pasting into Word. Paste using the right-click and select the right-most icon that looks like a letter A. This will paste the IDs as text rather than a table.
  2. Replace all line breaks with semicolons. Use Find and Replace. Find ^p (this represents a line break) and Replace with ; (semicolon with no space after it)
  3. Some optional clean-up steps:
    1. If you have any whitespace, use Find ^w and replace it with either nothing, or with a semicolon, depending on whether the whitespace is between values or is trailing/leading
    2. If you had any blank lines in the data, you may end up with two semicolons in a row ;; You want to get rid of these using Find ;; Replace with ; Keep doing this until it doesn't find any repeat semicolons
  4. Make sure the data has no trailing semicolon at the end

In Analytics:

  1. Use the filter for the appropriate field, such as MMS ID or Item ID. If you are expecting an exact match, select "is equal to" and paste in your string of IDs
  2. If you are not expecting an exact match (e.g., if the identifier is an ISBN and the field probably contains multiple ISBNs) select "contains any"
  3. There is some limit to how many IDs you can paste into a filter. The limit is much higher if it is an exact match. I usually do four pages of ISBNs at a time and up to 26 pages of Item or MMS IDs. Analytics is sometimes inconsistent about how much it will accept, so if you get an error, trying using fewer IDs temporarily.
  4. When copying IDs from the Word document, make sure your string does not start or end with a semicolon!

Strategies for Identifying Malformed Barcodes

  1. Character length
    1. Duplicate the barcode column
    2. Edit the formula to
      CHAR_LENGTH("Physical Item Details"."Barcode") 
    3. In Filters, filter on
      CHAR_LENGTH(Barcode) is not equal to / is not in  14
  2. Contains non-numeric characters
    1. Duplicate the barcode column
    2. Edit the formula to
      evaluate('REGEXP_INSTR(%1, ''^[0-9]+$'')',"Physical Item Details"."Barcode") 
    3. In Filters, filter on
      EVALUATE('REGEXP_INSTR(%1, ''^[... is equal to / is in  0
  3. Barcodes that do not start with 39074 (this applies to Main campus; Ginsburg barcodes may start with 32875; other campuses may have other expected barcode prefixes)
    1. Duplicate the barcode column
    2. Edit the formula to
      SUBSTRING("Physical Item Details"."Barcode" FROM 0 FOR 5)
    3. In Filters, filter on
      SUBSTRING(Barcode FROM 0 FOR 5) is not equal to / is not in  39074