Voyager Reports Mini-Webinars

June 8, 2017

Ted Schwitzner presents details on ways that the annual collection statistics reports may be enhanced for the July 2017 cycle. In addition to presenting the queries, attendees were polled on naming guidelines for the new queries.

Queries Demonstrated

Revising the CARLI Annual Statistical Reports

May 11, 2017

Ted Schwitzner explains a technique for managing the reports generated by the Fiscal Period Close (i.e., "rollover") process. He also provides insight on the tables and joins needed to find open orders that might be problematic. Finally, he includes some queries for libraries to check that their rollover rules are set correctly.

Slides

Excel file of headings for rollover reports (Fund Snapshot, Open Orders, Rollover Status)

Queries Demonstrated

Open Orders Report – based on Voyager\'s standard Acq 2 Job

Purchase_Order_Line_Item_Fund_subquery

SELECT PURCHASE_ORDER.PO_NUMBER, PURCHASE_ORDER.PO_TYPE, 
       PO_TYPE.PO_TYPE_DESC, 
       PO_STATUS.PO_STATUS_DESC, PURCHASE_ORDER.PO_STATUS_DATE, 
       LINE_ITEM.LINE_ITEM_NUMBER, LINE_ITEM.BIB_ID, 
       LINE_ITEM.UPDATE_DATE, LINE_ITEM_TYPE.LINE_ITEM_TYPE_DESC, 
       LINE_ITEM_COPY_STATUS.STATUS_DATE, 
       LINE_ITEM_STATUS.LINE_ITEM_STATUS_DESC AS PoLineStatus, 
       InvoiceLineItemStatus.LINE_ITEM_STATUS_DESC AS InvoiceLineStatus, 
       LINE_ITEM_FUNDS.LEDGER_ID, LINE_ITEM_FUNDS.FUND_ID, 
       FISCAL_PERIOD.FISCAL_PERIOD_NAME, 
       LEDGER.LEDGER_NAME, FUND.FUND_NAME
FROM FISCAL_PERIOD INNER JOIN 
     (((((((((PO_STATUS INNER JOIN 
      (PO_TYPE INNER JOIN PURCHASE_ORDER 
      ON PO_TYPE.PO_TYPE = PURCHASE_ORDER.PO_TYPE) 
      ON PO_STATUS.PO_STATUS = PURCHASE_ORDER.PO_STATUS) 
      INNER JOIN LINE_ITEM ON PURCHASE_ORDER.PO_ID = LINE_ITEM.PO_ID) 
      INNER JOIN LINE_ITEM_COPY_STATUS 
      ON LINE_ITEM.LINE_ITEM_ID = LINE_ITEM_COPY_STATUS.LINE_ITEM_ID) 
      INNER JOIN LINE_ITEM_TYPE 
      ON LINE_ITEM.LINE_ITEM_TYPE = LINE_ITEM_TYPE.LINE_ITEM_TYPE) 
      INNER JOIN LINE_ITEM_FUNDS 
      ON LINE_ITEM_COPY_STATUS.COPY_ID = LINE_ITEM_FUNDS.COPY_ID) 
      INNER JOIN LINE_ITEM_STATUS 
      ON LINE_ITEM_COPY_STATUS.LINE_ITEM_STATUS 
      = LINE_ITEM_STATUS.LINE_ITEM_STATUS) 
      INNER JOIN LINE_ITEM_STATUS AS InvoiceLineItemStatus 
      ON LINE_ITEM_COPY_STATUS.INVOICE_ITEM_STATUS 
      = InvoiceLineItemStatus.LINE_ITEM_STATUS) 
      INNER JOIN FUND ON (LINE_ITEM_FUNDS.FUND_ID = FUND.FUND_ID) 
      AND (LINE_ITEM_FUNDS.LEDGER_ID = FUND.LEDGER_ID)) 
      INNER JOIN LEDGER ON FUND.LEDGER_ID = LEDGER.LEDGER_ID) 
      ON FISCAL_PERIOD.FISCAL_PERIOD_ID = LEDGER.FISCAL_YEAR_ID;

FPC Check Rollover Rules

SELECT ROLLOVER_RULES.RULE_NAME, 
       Switch([INITIALIZE_TYPE]='0','Zero',
              [INITIALIZE_TYPE]='1','Cash Balance',
              [INITIALIZE_TYPE]='2','Original Allocation',
              [INITIALIZE_TYPE]='3','Original Allocation + Cash Balance') 
       AS InitializeFundAs, 
       IIf([ACTION_INDICATOR],'Checked','Unchecked') AS ApplyRule, 
       FISCAL_PERIOD.FISCAL_PERIOD_NAME AS OldFiscalPeriod, 
       New_Fiscal_Period.FISCAL_PERIOD_NAME AS NewFiscalPeriod, 
       LEDGER.LEDGER_NAME, LEDGER.NEW_LEDGER_NAME, 
       PO_TYPE.PO_TYPE_DESC, PO_TYPE_RULES.SINGLE_PART, 
       PO_TYPE_RULES.SUBSCRIPTION, PO_TYPE_RULES.MEMBERSHIP, 
       PO_TYPE_RULES.STANDING_ORDER, PO_TYPE_RULES.BLANKET_ORDER, 
       PO_TYPE_RULES.MULTI_PART, PO_TYPE_RULES.APPROVAL
FROM ((((ROLLOVER_RULES INNER JOIN PO_TYPE_RULES 
      ON ROLLOVER_RULES.RULE_ID = PO_TYPE_RULES.RULE_ID) 
      INNER JOIN PO_TYPE ON PO_TYPE_RULES.PO_TYPE_ID = PO_TYPE.PO_TYPE) 
      INNER JOIN FISCAL_PERIOD 
      ON ROLLOVER_RULES.FISCAL_PERIOD_ID = FISCAL_PERIOD.FISCAL_PERIOD_ID) 
      LEFT JOIN FISCAL_PERIOD AS New_Fiscal_Period 
      ON ROLLOVER_RULES.NEW_FISCAL_PERIOD_ID 
      = New_Fiscal_Period.FISCAL_PERIOD_ID) 
      LEFT JOIN LEDGER ON ROLLOVER_RULES.RULE_ID = LEDGER.RULE_ID
WHERE (((FISCAL_PERIOD.FISCAL_PERIOD_NAME)=[Current Fiscal Period]))
ORDER BY LEDGER.LEDGER_NAME, PO_TYPE.PO_TYPE_DESC;

FPC Ledgers Assigned to Rollover Rules

SELECT FISCAL_PERIOD.FISCAL_PERIOD_NAME, LEDGER.LEDGER_NAME, 
       ROLLOVER_RULES.RULE_NAME, LEDGER.NEW_LEDGER_NAME
FROM FISCAL_PERIOD 
     INNER JOIN (LEDGER LEFT JOIN ROLLOVER_RULES 
     ON LEDGER.RULE_ID = ROLLOVER_RULES.RULE_ID) 
     ON FISCAL_PERIOD.FISCAL_PERIOD_ID = LEDGER.FISCAL_YEAR_ID
WHERE (((FISCAL_PERIOD.FISCAL_PERIOD_NAME)=[Current Fiscal Period]));

April 21, 2017

Ted Schwitzner identifies the tables where Voyager stores patron data, and he shares tips for creating queries to combine addresses with patron information, find problematic barcodes and patron groups, and identify patrons that may be purged from Voyager.

Slides

Queries Demonstrated

Bad E-mail Address

Patron Address Zip Code is null

Patrons with multiple active barcodes

patron_perm_address

SELECT PATRON_ADDRESS.ADDRESS_ID, PATRON_ADDRESS.PATRON_ID, 
       ADDRESS_TYPE.ADDRESS_DESC, PATRON_ADDRESS.ADDRESS_LINE1, 
       PATRON_ADDRESS.ADDRESS_LINE2, PATRON_ADDRESS.CITY, 
       PATRON_ADDRESS.STATE_PROVINCE, PATRON_ADDRESS.ZIP_POSTAL
FROM PATRON_ADDRESS INNER JOIN ADDRESS_TYPE 
     ON PATRON_ADDRESS.ADDRESS_TYPE = ADDRESS_TYPE.ADDRESS_TYPE
WHERE (((PATRON_ADDRESS.ADDRESS_TYPE)='1'));

patron_email_address

SELECT PATRON_ADDRESS.ADDRESS_ID, PATRON_ADDRESS.PATRON_ID, 
       ADDRESS_TYPE.ADDRESS_DESC AS EmailAddDesc, 
       PATRON_ADDRESS.ADDRESS_LINE1 AS EmailAddress
FROM PATRON_ADDRESS INNER JOIN ADDRESS_TYPE 
     ON PATRON_ADDRESS.ADDRESS_TYPE = ADDRESS_TYPE.ADDRESS_TYPE
WHERE (((PATRON_ADDRESS.ADDRESS_TYPE)='3'));

Patrons with Duplicate Email Addresses

SELECT Patron_Dup_email_address_sq.ADDRESS_LINE1, 
       PATRON.PATRON_ID, PATRON.LAST_NAME, 
       PATRON.FIRST_NAME, PATRON.MIDDLE_NAME, 
       PATRON.INSTITUTION_ID, PATRON.EXPIRE_DATE
FROM (PATRON_ADDRESS INNER JOIN PATRON 
      ON PATRON_ADDRESS.PATRON_ID = PATRON.PATRON_ID) 
      INNER JOIN Patron_Dup_email_address_sq 
      ON PATRON_ADDRESS.ADDRESS_LINE1 
      = Patron_Dup_email_address_sq.ADDRESS_LINE1
WHERE (((PATRON_ADDRESS.ADDRESS_TYPE)="3"));

Count of Patrons by purge_date

SELECT Year([purge_date]) & '-' & Month([purge_date]) 
       & '-' & Day([purge_date]) AS PurgeDate, 
       Count(PATRON.PATRON_ID) AS CountOfPATRON_ID
FROM PATRON
GROUP BY Year([purge_date]) & '-' 
         & Month([purge_date]) & '-' & Day([purge_date]);

March 23, 2017

Ted Schwitzner demonstrates the use of make table queries as a way to make snapshots of current data, to make remote data local for easier processing, and to use the same table multiple times. The session also includes a review of the ELINK_INDEX table, used to track URLs that are found in different record types. The Elink_Bibs and Elink_Mfhds queries are covered as well.

Slides

Queries Demonstrated

Bib Record 035 contains second OCLC number

Bibliographic Record with Multiple 245 Fields

Elink_Bibs

SELECT ELINK_RECORD_TYPE.RECORD_TYPE, 
       ELINK_INDEX.RECORD_ID AS BIB_ID, 
       ELINK_INDEX.ELINK_ID, 
       ELINK_INDEX.SEQNUM, 
       ELINK_INDEX.LINK_TYPE, 
       ELINK_INDEX.LINK, 
       ELINK_INDEX.LINK_TEXT, 
       ELINK_INDEX.LINK_TEXT_NORMAL, 
       ELINK_INDEX.LINK_SUBTYPE, 
       ELINK_INDEX.URL_HOST, 
       ELINK_INDEX.URL_PORT, 
       ELINK_RECORD_TYPE.RECORD_TYPE_ID, 
       ELINK_INDEX.UPDATE_DATE, 
       ELINK_INDEX.UPDATE_OPID
FROM ELINK_RECORD_TYPE 
     INNER JOIN ELINK_INDEX 
     ON ELINK_RECORD_TYPE.RECORD_TYPE_ID = ELINK_INDEX.RECORD_TYPE
WHERE (((ELINK_RECORD_TYPE.RECORD_TYPE_ID)="B"));

Elink_Mfhds

SELECT ELINK_RECORD_TYPE.RECORD_TYPE, 
       ELINK_INDEX.RECORD_ID AS MFHD_ID, 
       ELINK_INDEX.ELINK_ID, 
       ELINK_INDEX.SEQNUM, 
       ELINK_INDEX.LINK_TYPE, 
       ELINK_INDEX.LINK, 
       ELINK_INDEX.LINK_TEXT, 
       ELINK_INDEX.LINK_TEXT_NORMAL, 
       ELINK_INDEX.LINK_SUBTYPE, 
       ELINK_INDEX.URL_HOST, 
       ELINK_INDEX.URL_PORT, 
       ELINK_RECORD_TYPE.RECORD_TYPE_ID, 
       ELINK_INDEX.UPDATE_DATE, 
       ELINK_INDEX.UPDATE_OPID 
FROM ELINK_RECORD_TYPE 
     INNER JOIN ELINK_INDEX 
     ON ELINK_RECORD_TYPE.RECORD_TYPE_ID = ELINK_INDEX.RECORD_TYPE
WHERE (((ELINK_RECORD_TYPE.RECORD_TYPE_ID)="M"));

Presenter: Ted Schwitzner — CARLI Office

February 23, 2017

Ted Schwitzner provides a demonstration on modifying some of the collection statistics reports to account for bibliographic record suppression, as well as the presence of a title in more than one holding location. The How-to of the month includes details on the BIB_FORMAT_DISPLAY table, which the collection stat reports use. The session also includes a look at some experimental reports being developed by CARLI staff.

Slides

Queries Demonstrated

Collection Stat 1: Comprehensive Item Count by Bib Format Subq, modifed to include bib suppression.

SELECT BIB_TEXT.BIB_FORMAT, 
       LOCATION.LOCATION_CODE, 
       LOCATION.LOCATION_NAME, 
       Count(LOCATION.LOCATION_ID) AS CountOfLOCATION_ID
FROM (((BIB_TEXT 
     INNER JOIN BIB_ITEM ON BIB_TEXT.BIB_ID = BIB_ITEM.BIB_ID) 
     INNER JOIN ITEM ON BIB_ITEM.ITEM_ID = ITEM.ITEM_ID) 
     INNER JOIN LOCATION ON ITEM.PERM_LOCATION = LOCATION.LOCATION_ID) 
     INNER JOIN BIB_MASTER ON BIB_ITEM.BIB_ID = BIB_MASTER.BIB_ID
WHERE (((BIB_MASTER.SUPPRESS_IN_OPAC)="N"))
GROUP BY BIB_TEXT.BIB_FORMAT, 
         LOCATION.LOCATION_CODE, 
         LOCATION.LOCATION_NAME;

Collection Stat 6: Comprehensive Bibliographic Record Count, modified to include bib suppression and eliminate double-counting titles with holdings in different locations.

Step 1, change existing Collection Stat 6 into "Collection Stat 6 subq1"

SELECT IIf([bib_format_display] Is Null,
           [bib_text].[bib_format] & "=Unknown format",
           [bib_format_display].[bib_format] & "=" 
             & [bib_format_display].[bib_format_display]) AS BibFormat, 
       LOCATION.LOCATION_NAME, 
       BIB_TEXT.BIB_ID
FROM ((BIB_MASTER 
     INNER JOIN (BIB_TEXT 
     LEFT JOIN BIB_FORMAT_DISPLAY 
     ON BIB_TEXT.BIB_FORMAT = BIB_FORMAT_DISPLAY.BIB_FORMAT) 
     ON BIB_MASTER.BIB_ID = BIB_TEXT.BIB_ID) 
     INNER JOIN BIB_MFHD ON BIB_TEXT.BIB_ID = BIB_MFHD.BIB_ID) 
     INNER JOIN (MFHD_MASTER 
     INNER JOIN LOCATION 
     ON MFHD_MASTER.LOCATION_ID = LOCATION.LOCATION_ID) 
     ON BIB_MFHD.MFHD_ID = MFHD_MASTER.MFHD_ID
WHERE (((BIB_MASTER.SUPPRESS_IN_OPAC)='N') 
  AND ((MFHD_MASTER.SUPPRESS_IN_OPAC)='N'));

Step 2. Apply aggregate functions Group By and Min() to the first subquery to select only the first listed (alphabetically) location for a given bib record.

SELECT [Collection Stat 6 subq1].BibFormat, 
       Min([Collection Stat 6 subq1].LOCATION_NAME) AS MinOfLOCATION_NAME, 
       [Collection Stat 6 subq1].BIB_ID
FROM [Collection Stat 6 subq1]
GROUP BY [Collection Stat 6 subq1].BibFormat, 
         [Collection Stat 6 subq1].BIB_ID;

Step 3. Apply aggregate functions Group By and Count to get the total number of titles (i.e., unique bib records) in each location and format.

SELECT [Collection Stat 6 subq2].BibFormat, 
       [Collection Stat 6 subq2].MinOfLOCATION_NAME AS Location, 
       Count([Collection Stat 6 subq2].BIB_ID) AS TitleCount
FROM [Collection Stat 6 subq2]
GROUP BY [Collection Stat 6 subq2].BibFormat, 
         [Collection Stat 6 subq2].MinOfLOCATION_NAME;

Presenter: Ted Schwitzner — CARLI Office

January 18, 2017

For the launch of this series, Ted Schwitzner provides a demonstration on finding items that have no circulation transactions in Voyager. The session also includes a brief look at the Web Reports system and the Bibliographic Maintenance reports for OCLC control number maintenance.

Slides

Presenter: Ted Schwitzner — CARLI Office

Queries Demonstrated

Subquery named "Items_No_Circ_Subquery"

SELECT [Circulation Transactions (Charges)].CIRC_TRANSACTION_ID,
       [Circulation Transactions (Charges)].ITEM_ID
FROM [Circulation Transactions (Charges)];

Side query to find locations

SELECT LOCATION.LOCATION_ID, LOCATION.LOCATION_CODE, LOCATION.LOCATION_NAME
FROM LOCATION
ORDER BY LOCATION.LOCATION_CODE;

Main query, which uses "Shelflist information for specific location" query and the "Items_No_Circ_Subquery" above.

SELECT [Shelflist information for specific location].LOCATION_CODE, 
       [Shelflist information for specific location].Title_Brief, 
       [Shelflist information for specific location].DISPLAY_CALL_NO, 
       [Shelflist information for specific location].ITEM_ENUM, 
       [Shelflist information for specific location].CHRON, 
       [Shelflist information for specific location].YEAR, 
       [Shelflist information for specific location].COPY_NUMBER, 
       [Shelflist information for specific location].ITEM_TYPE_CODE, 
       [Shelflist information for specific location].ITEM_TYPE_NAME, 
       [Shelflist information for specific location].ITEM_BARCODE, 
       [Shelflist information for specific location].ITEM_STATUS_DESC
FROM [Shelflist information for specific location] 
     LEFT JOIN Items_No_Circ_Subquery 
     ON [Shelflist information for specific location].ITEM_ID 
      = Items_No_Circ_Subquery.ITEM_ID
WHERE (((Items_No_Circ_Subquery.ITEM_ID) Is Null));