Copy of FOLIO SQL Query Directory for Canned Reports

All Reports

shows a list of items that patrons claim they have returned, but are not showing as checked in by the system (where the item status is 'claimed returned').

missing_or_lost_items

Note: see CR198 for lost_laptops_and_equipment

jl41

vp25

item count

*WARNING: long-running query!

provides a summary of item and piece counts for non-electronic resources cataloged in the Inventory, by various filters

7/28/21

updated 9/4/22

vp25

jl41

Provides the list of all active funds along with their ledger, fund group and fund type.  It also provides allocations, transfers, encumbrances, expenditures, fund balance and percentage spent for each fund.

lost_paid_returned

NOTE: under review; contact jl41 or vp25

vp25

jl41

vp25

jl41

CR128

jl41, vp25

This query finds ceased or cancelled serials for a specified owning library and LC class, and shows if there are holdings at the Annex. The query shows all item records (at the owning library) for those titles. Annex item-level detail is not shown, but the Annex holdings statements are displayed.

Shows all holdings in a specified location along with holdings for the same titles in other libraries.

NOTE: Results are tricky to interpret; please contact jl41 or vp25

This query provides the list of approved invoices within a date range along with vendor name, finance group name, vendor invoice number, fund details, purchase order details, language, instance subject, LC classification, LC class, LC class number, and bibliographic format. 

This query searches for duplicate invoices within 360 days with $1,000+ total amount of the invoice accordingly.

orders_split_funds

hathitrust_serial_holdings

Note: long-running query, results post to local schema

hathitrust_monograph_holdings

Note: long-running query, results post to local schema

hathitrust_multivolume_holdings

Note: long-running query, results post to local schema

CR144 - BY REQUEST - please use the Request Form to get this report

 7/1/22

in review; not currently available

volume_count_adc*

Note: long-running query, results post to local schema

*adc = Annual Data Collection

NOTE: Need to add retro statistics

This report shows items where the item status is 'Missing'. It is different from CR106 in that it includes parameters  for filtering results by the last 2 weeks, the last 6 months, the last year, the last 2 years, and a freeform start date and end date.

slm5,

vp25,

jl41

Reserve statistics for a given semester, for both e-reserve and print reserve. Requires separate queries to pull out data from Ares, which is then imported into the LDP.

NOTE: This query is for Fall 2021, and needs to be updated for Spring 2022, as it needs fresh ARES data for each semester.

Shows titles held by subject, format, language, and location. 

Historical_charges_from_Voyager

Checkouts of the last month by library, patron group, and material type.

Compilation of requests grouped by date, owning library, location, type of requests, status of requests, pickup point,  type of service point, patron group name, and material type.

CR160 - BY REQUEST - please use the Request Form to get this report

Warning - currently requires manual processing of results

7/1/22

in review; not currently available

soundrec_piece_ct_adc

Note: long-running query, some results post to local schema

*adc = Annual Data Collection

CR161 - BY REQUEST - please use the Request Form to get this report

Warning - currently requires manual processing of results

7/1/22

in review; not currently available

vidrec_piece_ct_adc

Note: long-running query, some results post to local schema

*adc = Annual Data Collection

6/27/22

in review; not currently available

title_count_non-micr-physical_adc_stats

Note: long-running queries; some results post to local schema

5/10/22

in review; not currently available

title_count_micro_adc_stats

Note: long-running queries; some results post to local schema

8/11/22

in review; not currently available

title_count_servremo_adc_stats

Note: long-running queries; some results post to local schema

Count of serv,remo titles, by bib format, through CUL-applied codes (in previous version, leader format used).

PLEASE NOTE: this query does not yet exclude unpurchased PDA.

8/5/22

in review; not currently available

title_count_servremo_adc

Note: long-running queries; some results post to local schema

IN REVIEW

5/11/22

6/24/22

CR173

Patron_purchase_requests (see CR202 for newest version of this query)

Lists patron requests to purchase items, including requestor netid or other requestor information (where available) and item details including location name (from PO lines), fund name, fund code, and total circulation count.

NOTE:  For limited versions of this report that include fiscal year and days till fulfillment, see ad hoc reports  AHR112 (Folio data) and AHR113 (Voyager data).

annex_duplicates

NOTE: Requires a list of item barcodes to be brought into the local schema.

This report matches items from an imported list of barcodes to holdings at the Annex. The purpose is to identify duplicate holdings to prevent them from being sent to the Annex.

This report finds items with an 'In process' status. Typically, a newly cataloged book is sent to the owning library 1-3 days after being cataloged and bookmarked, where it is discharged, which changes the status from “In process” to “Available.” This report shows books that still have an 'In process' status (status date shown), which may be those that were received but haven’t been discharged before they were shelved. The only parameter is for owning library. The catalog link is shown to make it easy to view the display in the catalog.

6/24/22

in review; not currently available

773_and_899_aggr_counts_adc

Note: long-running queries;

This query counts Voyager checkouts at a given circ desk and date range by semester, year, month, weekday, hour, patron group, loan period, item type and collection group (regular collection, laptop, equipment or reserve). It is used to show a very granular level of detail of Voyager circ desk activity. The results are meant to be exported to Excel and then analyzed by pivot tables or other means.

physical_collections_use

This query finds conference proceedings that contain selected acronyms in the title or alternate title for a specified owning library at the holdings level, counts the number pf volumes, and shows matches to the Annex.

NOTE: This query is hard-coded for acronyms, marc fields, call number, and location. Please request assistance if needed. 

This query finds conference proceedings that contain selected acronyms in the title or alternate title for a specified owning library at the item level, counts the number pf volumes, and shows matches to the Annex.

NOTE: This query is hard-coded for acronyms, marc fields, call number, and location. Please request assistance if needed. 

This query finds the number of items withdrawn in the date range specified, as of 7-1-21 and going forward. From 7-1-21 through 8-14-22, the withdrawn information comes from the holdings note on specific holdings records. 

NOTE: As of 8-15-22, the count of withdrawn items comes from the administrative note on the instance record and is not linked to specific holdings records. In this case, the the query lists all the holdings locations, and the total number of pieces withdrawn, without being able to specify from which locations the withdrawals took place. 

The query lists title, holdings hrid, library name, location name, call number, holdings type, withdrawal date, and number of pieces withdrawn. 

This query identifies laptops and equipment with an item status of "Aged to lost," "Declared lost" or "Lost and paid" and displays the loan and borrower information.

This query gets Olin items that were purchased as "CouttsShelfReady" in LC classes A and K. An instance create date of 6/18/21 is a migrated record from Voyager.

This query creates a list of missing, lost, and claimed returned items (including equipment) at a given library, and includes item status date, last date and location of check in, and size/pagination. Sorts in call number order. 

This query finds libraries, locations, service points and fine owners associated with the service points.

This query finds open fines older than a given number of days old, for an owning library.

The query finds any purchase order line that doesn't have an expense class assigned.

This query identifies DVDs in the library location specified.

This query identifies VHS tapes in the library location specified.

This query is specififcally for selectors and it shows Missing and Lost items with different fields included that are useful in making replacement decisions. 
It is more extensive than the 'lost and missing' circulation query.

This query counts the total number of items in all locations (perm and temp locations), even if there are zero items, and includes suppressed and unsuppressed records. It was written for the Locations project, as requested by Tom Trutt.

This query counts the total number of holdings records in all locations (perm and temp locations), even if there are zero holdings, and includes suppressed and unsuppressed records.

This query provides a list of current encumbrances along with other encumbrances info per purchase order line. It can be filtered by fiscal year and fund code.

This query provides counts of physical items (excluding microforms), by format type and holdings library and location. 

This query provides counts of unique titles (instances) of physical items (excluding microforms), by format type.

This query provides counts of microforms, by format type. 

This query provides counts of ematerials by format type.

This query finds Folio charges and browses for a given location and date range (or semester) and LC class, and shows the total historical Voyager charges and browses.

This query finds shelf list inventory information by library location. The most_recent_patron_group field determines what patron group an item was assigned to most recently, which is important for assignments to the Borrow Direct and Inter Library Loan patron groups, which change frequently.

This query finds total Voyager and Folio circ usage for given parameters: LC class, library, title, instance hrid and/or language.

This query finds microform guides in Olin and Kroch libraries and shows additional copies in the stacks or at the Annex.

This query counts instances, holdings, items, loans, srs marctab instances, srs marctab records with "001" fields, srs marctab records with "999" fields that include "i" subfields, and srs record instances on the LDP Production database. If srs_marctab_001 and 999i counts are equal and within about 1,000 records of the count for SOURCE=MARC in the FOLIO Inventory application, then srs_marctab counts are acceptable.

This query finds all orders (not just approvals) showing the "bill to" and "ship to" locations in the purchase order or invoice. It also includes the purchase order location, workflow status, order type, order format, vendor name, fund, and fiscal year. 

This query uses the source of acquisition statistical code of "Approval/Blanket Order" to identify titles associated with approval plans.  It also shows approval plan titles associated with vendors using the vendor code from the MARC record 980 subfield "v" if it is available. 

This query gets purchase requests in Voyager and Folio and calculates the number of days between the request date and checkout date. 

This query was modified from CR134 to accommodate alumni affairs needs for information on stewarded funds. It provides all the same information as CR134 but includes primary contributor name, publisher name, publication date, publication place, and filter for Endowments funds.

This query shows titles in the CUL collection that were published by CU Press or one of its imprints and don't match by ISBN, or have no ISBNs at all and so can't be matched to the CU press list.

This query finds Brill print monographs publications at CUL and shows the LC class and class number, total circulation (Voyager and Folio), most recent checkout, cost and fund (Folio only for now). 

This is a generalized version of the query that finds Mann items in a specified call number range for possible transfer to the Annex and record cleanup.

The query finds any purchase order line that doesn't have an expense class assigned.

This query provides a list of current encumbrances along with other encumbrances info per purchase order line. It can be filtered by fiscal year and fund code.