You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

For information about standards for contributing report queries to the CUL FOLIO Canned Reports Directory, please
refer to the CUL FOLIO Report Development Standards


Click the link below to go to the reports list you wish to view.


Filter Directory 

For a list of parameters (options to use as a filter), see the Filter Directory. Options for filtering your report results are typically entered in the WITH statement at the top of the query. For example, to get report results associated with a specific location, such as items stored at Olin Library, you would enter "Olin" as the location parameter option in the WITH statement. This would limit your results set to items in Olin Library. For ad hoc queries that are written for specific needs and cannot be generalized, please see the Directory for Ad Hoc Reports


All Reports


All Reports

Link to SQL QueryLast
Updated
Report NameDescription

related Dashboard 

Related JIRA
REP Issues
Setup
CR1001/14/22loans_countsloans by location
REP-103vp25
CR101 7/11/22fund_expenditures_by_po_line_invoice_paymentsshows fund expenditures by purchase order line within a given invoice payment date range. For each purchase order line number, the fund data attributes are shown alongside the purchase order line transaction amount.
REP-157, REP-285slm5
CR1027/27/21title_count
*WARNING: long-running query!
provides title counts for non-electronic resources cataloged in inventory by various filters
REP-230slm5
CR1034/12/22annex_non_circulating_itemsThis query finds items at the Annex that have a "non-circulating" permanent loan type, excluding rare and special collections. It also finds items with an hourly loan type.

jl41
CR10411/19/21claimed_returned

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').


REP-203vp25
CR10511/16/21item_related_exceptionsshows a list of exceptions, which are actions taken by an operator (staff member) and are recorded in the circulation log. An example is when an item has been manually discharged or discharged by an operator.
REP-278vp25
CR10609/01/22

missing_or_lost_items

Note: see CR198 for lost_laptops_and_equipment


creates a list of missing or lost items at a given library, and includes missing date, size/pagination, last checkin date, and last checkin location. Sorts in call number order.
REP-152

jl41

vp25

CR107 01/31/22requestslist of patron requests within a specified date range, and by request type.
REP105; REP-143; REP-274; REP-275vp25, jl41
CR1087/27/21services_usagenumber of circulation transactions by service-point and transaction type, with time aggregated to date, day of week, and hour of day.
REP-243vp25
CR1097/1/21standing_order_purchase_ordersThis is a daily report run by acquisitions staff to review firm purchase orders against standing purchase orders. The report includes po number, po line number, po create date, vendor code, order status, BIB ID (from MARC), po line number, po line item id, series statement (490/830 tag from MARC).
REP-283np55
CR1107/27/21

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


REP-19
REP-20
REP-21
REP-229
REP-231
nb299
CR1116/21/21duplicate orders on ISBNused by Acquisitions to identify recent orders that duplicate already held volumes or that lack standard numbers and require manual searching
REP-281np55
CR1127/8/21daily_appr_inv_manualsused by Accounting for Endowed Daily Approve Invoice by Condition Flag - Manual Check
REP-10nb299
CR1137/8/21daily_appr_inv_exportedused by Accounting for Endowed Daily Approve Invoice by Condition Flag - Exported Check
REP-10nb299
CR1147/8/21daily_appr_inv_vendorused by Accounting for Endowed Daily Approve Invoice by Group, Vendor, Invoice Number, and Invoice Lines to Check Record
REP-10nb299
CR1158/3/21daily_dist_by_accountused by Accounting for Endowed Payables Distribution by Account
REP-10nb299
CR116

10/12/21

payables_inv_not_fed_notesused by Accounting for Endowed Payables Invoices Not Fed
REP-10nb299
CR1177/12/21acrl_circulationused for ACRL circulation count reporting
UXPROD-1915vp25
CR11812/14/21items_lost_in_transitList of items where item status is 'in transit' but they haven't been checked in at the home location (permanent location) after a specified time period. Therefore the items might have been lost or misplaced on their way back to their home location.
REP-190vp25
CR119

7/28/21

updated 9/4/22

patron_listList of patrons including patron details and group type (faculty, undergrads, etc.)
REP-220

vp25

jl41

CR1207/8/21daily_appr_inv_controlsummary of vouchers by account number, including exported and manuals
REP-10, REP-284nb299
CR121
daily_appr_inv_vendor_fundprovides the voucher lines details per vendor invoice and funds and results show vendor invoice payments from transactions on the previous day
REP-10nb299
CR122

9/12/22

fund_detail

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.


REP-98, REP-286, REP-287nb299
CR123

7/14/22

open_ordersprovides a list of open purchase orders and their amount paid, broken down by purchase order lines
REP-283nb299
CR12411/19/21

lost_paid_returned

NOTE: under review; contact jl41 or vp25

list of items that were lost and paid for by the patron, but later found and returned. Identifies items for which patrons need to be reimbursed. 
REP-88

vp25

jl41

CR125

7/14/22 

appr_invoices_vendor_fundsProvides the list of approved invoices within a date range along with vendor name, invoice number, fund group and fund used.
REP-82nb299
CR12603/10/23Borrow_direct_interlibrary_loan_recallsThis query provides a list of overdue CUL-owned items checked out to Borrow Direct or Interlibrary loan patrons that have been recalled (these items have not yet been received by CUL, based on the current due date). 
REP-272

vp25

jl41

CR12712/10/21Borrow_direct_interlibrary_loan_overdue_itemsProvides a list of items owned by CUL and borrowed by BD/ILL patrons, that are overdue.
REP-272vp25

CR128


10/06/22ceased_or_cancelled_serials_with_annex_matchesProvides a list of ceased or cancelled serials based on the specified location and shows matches to the annex where they exist. The holdings summary for both the specified location and the annex location are displayed.
REP-215

jl41, vp25

CR128B10/11/22item_level_ceased_or_cancelled_serials

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.


REP-215jl41, vp25
CR1298/27/21pol_notesLists purchase order lines with internal notes associated with each of those lines
REP-280np55
CR1309/9/21fund_expenditures_by_po_lineShows fund expenditures by purchase order line. Fund data attributes such as fund group, fund type, fund code, transaction type, and transaction type are provided.
REP-157, REP-285slm5
CR131

9/24/21 

duplicate instances on ISBNsThis report checks existing instances created within the last 5 days for duplicate records in the system. It shows duplicate records with date created for new duplicates along with local identifiers (899 and 773 MARC fields). It shows both valid and invalid ISBNs.
REP-281np55
CR132

01/23/2023

YTD_acct_bal_by_ledger_univ_acctThis report provides the year-to-date external account cash balance along with total_expenditures, initial allocation, and net allocation. This is an accounting report used for monthly reconciliation.

nb299
CR133  - BY REQUEST - please use the Request Form to get this report12/10/21shelf_list_duplicate_flagging

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


Newjl41, vp25
CR134

8/31/23

appr_invoices_bib_data

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. 


REP-39, REP-83, REP-287nb299, jl41
CR134_F11/22/22appr_invoices_for_finance_teamThis query provides the list of approved invoices within a date range along with vendor name, finance group name, vendor invoice number, fund details, and purchase order details.
REP-39, REP-83, REP-287nb299
CR135

11/5/21 

sum_appr_inv_ledger_acctThis query provides the total of transaction amount by account number along with the finance ledger and finance group within a date range

nb299
CR136

10/4/21

appr_inv_per_univ_acct_detail_date_rangeThis query provides the list of approved invoices within a date range along with the external account, vendor name, invoice number, and transaction amount.

nb299
CR137_1
duplicate_invoices_1000+

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



np55
CR137_2
duplicate_invoices_lessthan1000This query searches for duplicate invoices within 180 days with less than $1,000 total amount of the invoice accordingly.

np55
CR1386/7/22

orders_split_funds

This query provides the list of split funds purchases for an invoice line that has multiple funds used and the transaction amount for each fund and the percentage spent.
REP-98nb299
CR13911/5/21daily_FBO_inv_appr_paid_diff_dateThis query provides a list of approved invoices that have been paid at a different date.
Sometimes Folio won't allow an invoice to get paid and the invoice will only be paid at a
later day, after changes have been made.


nb299
CR14011/8/21fbo_change_in_allocationThis report provides a list of change in allocation per date range. A negative transaction amount is for an increase in allocation and a positive amount is for a decrease in allocation.

nb299
CR14111/18/21lost_bursared_returnedThis report finds all items that were billed as lost, then bursared, and later returned. Folio marks such bills as "refunded fully," but in fact refunds have to be processed manually. This report allows you to identify charges that must get manual refunds.
Newjl41, vp25
CR14212/1/21appr_inv_no_transac_date_rangeThis query provides the list of approved invoices within a date range for which transactions has not been created. This query should not return any data if the system is working properly.

nb299
CR143_A - BY REQUEST - please use the Request Form to get this report12/6/21

hathitrust_serial_holdings

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

This set of queries creates the list of serials holdings in conforming to this specification that CUL sends to HathiTrust. The queries create 11 intermediate tables that write to the local schema before creating the final dataset.
REP-18np55
CR143_B - BY REQUEST - please use the Request Form to get this report12/13/21

hathitrust_monograph_holdings

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

This set of queries creates the list of single volume monographic (SVM) holdings conforming to this specification that CUL sends to HathiTrust.
REP-18np55
CR143_C - BY REQUEST - please use the Request Form to get this report12/13/21

hathitrust_multivolume_holdings

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

This set of queries creates the list of multivolume monographic (MVM) holdings conforming to this specification that CUL sends to HathiTrust.
REP-18np55

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 set of queries provides volume count statistics for Cornell annual statistics reports by unit and by "on campus" versus the Annex. The counts generated include total volumes held, volumes added, and volumes added retrospectively.
REP-21np55
CR14501/28/22Equipment_with_holdings_checkouts_statCodesThis report shows all laptops and equipment for a given library, with total checkouts and renewals as of today's date. It includes patron group name as well as stat codes to indicate whether the item has been archived or not. 
REP-291, REP-103jl41, vp25
CR14601/11/22missing_items_date_ranges

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.


REP-152

slm5,

vp25,

jl41

CR147_A  - BY REQUEST - please use the Request Form to get this reportin review, not currently availablecirculation_chargesrenewals_adcCirculation statistics (charges and renewals) by library, patron group and collection type (regular, reserve, laptop, equipment) used for the annual data collection. Excludes SPEC and ILL/BD owned by other institutions.
REP-195jl41, lm15
CR147_B  - BY REQUEST - please use the Request Form to get this reportin review, not currently availableannual_circ_cons_pg_statsAnnual circulation statistics by consolidated patron group
REP-195jl41
CR14801/19/22Expired_patrons_with_unreturned_overdue booksFinds all expired patrons with unreturned overdue books (most are lost). Shows book details and some patron information (net ID, patron group).
REP-292jl41, vp25
CR14901/28/22Checkouts_by_loan_policy_and_material_type _overviewOverview of checkouts by owning library, loan policy and material type. Allows you to see how libraries are circulating their equipment.
Newjl41, vp25
CR150  - BY REQUEST - please use the Request Form to get this report01/28/22Physical_and_Ereserve_stats

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.


REP-106Jl41, vp25
CR15101/28/22checkouts_by_useridFinds checkouts by a specific userid. This report is used to get a list of checkouts for an individual patron. It also contains patron information. 
Newjl41, vp25
CR15202/10/22patron_groups_with_demographicsThis report groups and counts active patrons by college and department.
REP-271jl41, vp25
CR15302/10/22active_patrons_with_demographicsThis report provides a list of all active patrons, along with their demographics (name, netid, college, department, patron group).
REP-271jl41, vp25
CR154 06/09/22titles_by_subject_and_language

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


REP-229np55, jl41
CR15502/22/22Title_count_by_fiscal_year_VoyagerProvides a count of titles acquired by fiscal year in a given location, using pre-Folio data from Voyager. This report does not contain subject headings. Needs access to VGER data.
Newjl41, vp25
CR156  - BY REQUEST - please use the Request Form to get this report02/28/22

Historical_charges_from_Voyager


Provides a count of historical charges. This query can be modified to get item level details. Although it is possible to use filters such as location, call number, item type, item create date, material type, and Voyager bib ids, the filters in this report are hard-coded, as they are a complex set of filters. Please ask Joanne Leary for assistance.  Details at the item level are not available. Needs access to VGER data.
Newjl41, vp25
CR15701/24/2023Funds_and_teams_with_expense_classProvides a detailed current date report of funds and teams with amounts spent, encumbered, and remaining. It also shows the expense class.
Newnb299
CR15802/18/22Circ_stats_1_checkouts

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


New jl41, vp25
CR15903/09/23Circ_stats_2_requests

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.


Newjl41, vp25

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

This set of queries provides sound recording piece count statistics for Cornell annual statistics reports, by unit "on campus," and by unit at the Annex.
Newlm15

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

This set of queries provides video recording piece count statistics for Cornell annual statistics reports,  by unit "on campus," and by unit at the Annex.
Newlm15
CR162   - BY REQUEST - please use the Request Form to get this report

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

Count of all physical titles except those in microform format, by bib format.
REP-230adc1lm15
CR163   - BY REQUEST - please use the Request Form to get this report

5/10/22

in review; not currently available

title_count_micro_adc_stats

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

Count of microform titles, by bib format.
REP-230adc2lm15
CR164   - BY REQUEST - please use the Request Form to get this report

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.


REP-230adc3np55, lm15
CR164alt   - BY REQUEST - please use the Request Form to get this report

8/5/22

in review; not currently available

title_count_servremo_adc

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

Count of serv,remo titles, by bib format, through leader coding.  An alt query for limited use. Contact Linda Miller for more info.
REP-230adc3np55, lm15
CR16501/23/2023funds_and_teamsProvides a current date report of funds and teams with amounts spent, encumbered, and remaining. This report does not include expense class (see CR157).
Newnb299
CR1664/14/22pcc_records_cataloged_by_userlists PCC records cataloged by a given user
Newnp55
CR1674/27/22expired_patron_with_unreturned_overdue_booksFinds all expired patrons with unreturned overdue books (most are lost). Shows book details and some patron info (net ID, patron group).
Newjl41
CR168

IN REVIEW

5/11/22

physical_serial_titles_currently_received_adcCount of physical serial titles currently received for the annual data collection. Blocked.
part of REP-228np55, lm15
CR169

6/24/22


database_count_adcGets counts of databases for ACRL and NCES reporting. Note: This currently includes all CISER databases. May be revised.
part of REP-41np55, lm15
CR1705/12/22item_status_in_processShows items that still have an "in Process" status, which may indicate that they were not properly discharged when they were received at the owning library (after being cataloged). 
NEWjl41, vp25
CR1715/23/22BD_ILL_selectors_report -Part 1Lists title, patron group and department (where available) for items borrowed by CUL patrons from other universities on Borrow Direct and Interlibrary Loan. Parameters for loan date range included. 
REP-156jl41
CR1725/24/22BD_ILL_selectors_report - Part 2Lists item details for items borrowed from CUL by other universities' patrons on Borrow Direct and Interlibrary Loan. Parameters for loan date range included. 
REP-156jl41, vp25

CR173


7/29/22

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).


NEWjl41, vp25
CR174A9/30/23BD_ILL_cul_lenderThe 2 queries CR174A and CR174B are both Borrow Direct (BD) and Inter Library Loan (ILL) count queries. The first one, BD_ILL_cul_lender, provides a count of BD and ILL loans from CUL to other universities. These loans are identified via patron type, where the patron type is Borrow Direct, or InterLibrary Loan.
REP-156jl41, vp25
CR174B9/30/23BD_ILL_cul_borrowerThe 2 queries CR174A and CR174B are both Borrow Direct (BD) and Inter Library Loan (ILL) count queries. The second one, BD_ILL_cul_borrower, provides a count of BD and ILL loans to  CUL from other universities. These loans are identified via material type, where the material type is Borrow Direct, or InterLibrary Loan.
REP-156jl41, vp25
CR1756/6/22team_related_split_fundsShows split funds by PO line that enables selectors to filter by the funds for their team while also seeing what other team funds split a purchase order payment with them.
NEWnp55, vp25
CR1766/7/22

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.


NEWjl41
CR1776/7/22items_in_process

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.


NEWjl41
CR1786/8/22cataloger_countsLists items catalogued (instance id and title) by cataloger netid, date, ttype, and unit (lts, law, etc.)
NEWjmp8, jl41, vp25
CR1796/23/22bursared_items_by_fine_fee_date_and_material_typeThis report finds bursared items with fines or fees using the payment method of "CUL Transfer Account" and a fine fee type action of "Transferred Fully." Filters for fine date range and material type are included.
NEWslm5
CR180 - BY REQUEST - please use the Request Form to get this report

6/24/22

in review; not currently available

773_and_899_aggr_counts_adc

Note: long-running queries;

Gets counts of aggregators and other collections coded in 773 and 899 fields. Used to help track changes in e-counts from year to year. First version.
NEWnp55, lm15
CR1818/2/22circ_count_by_instanceHRIDThis report returns circ counts from both Voyager and Folio for an individual instance HRID, as selected in the parameters. Title, location, and number of items are also included.  
NEWjl41, vp25
CR1828/17/22voyager_checkouts_by_circ_desk

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.


NEWjl41, vp25
CR18310/11/22laptop_circ_countsThis query counts laptop circs by library, date, loan type, and laptop type (Mac or PC).  Please note that until August 2022, Mann laptop laptop loans were made through LibCal as well as Folio, and this report includes only Folio counts. As of early August 2022, all Mann laptop loans are in Folio. 
NEWjl41, vp25
CR184A11/21/22

physical_collections_use


This query counts loans and renewals by date range, owning library, patron group, material type and collection type.

jl41, vp25
CR184B10/24/22physical_collections_use_splitThis report splits up CR184A into two parts: one query for loans, and one query for renewals, with a fiscal year filter for each. These two queries are specifically constructed for use in Tableau dashboards
NEWjl41, vp25
CR1859/20/22ceased_cancelled_titles_holdings_levelThis query finds ceased or cancelled titles at the holdings level for a specified owning library and LC class, and shows if there are holdings at the Annex.
NEWjl41, vp25
CR1869/20/22ceased_cancelled_titles_item_levelThis query finds ceased or cancelled titles at the item level, for a specified owning library and LC class, and shows if there are holdings at the Annex.
NEWjl41, vp25
CR1879/20/22acronyms_holdings_level

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. 


NEWjl41, vp25
CR1889/20/22acronyms_item_level

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. 


NEWjl41, vp25
CR1899/20/22book_listThis query creates a list of books at a specified library showing title, author, call number, year of publication and number of circs (Voyager plus Folio).
NEWjl41, vp25
CR19010/11/22inactive_patrons_with_open_finesThis query finds inactive patrons with open fines. 
NEWjl41, vp25
CR191 (under review)10/11/22physical_materials_countsThis query is an annual count query of all physical materials holdings. The query can be run as a whole and then filtered for material type based on the details provided at the end of the query, or specified 'Where' statements can be included in the query to obtain data subsets by material type. A fiscal year filter is included, based on the date the item was created. 
NEWvp25
CR19210/25/22withdrawn_items

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. 



NEWjl41, vp25
CR19310/25/22filled_delivery_requestsThis query provides a count of all contactless delivery and circulation desk pickup requests, by fiscal year. Patron group, material type, request type, and location details are included. 
NEWjl41, vp25
CR19411/14/22checkouts_and_checkins_by_date_range_and_service_pointThis query finds checkouts and checkins by month for a given service point and date range. Item records that have been deleted will show a material type name of "Unknown". Because most deleted records are equipment records, these items have been categorized as "Equipment" collection type as a best guess.
NEWjl41
CR1951/5/24expense_transferThis query is a customization of CR-134 (paid invoices with bib data) for the purpose of identifying expenditures that can be transferred from unrestricted funds to restricted funds.
NEWjl41
CR19612/02/22lost_laptops_and_equipment

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.


NEWjl41, vp25
CR19712/15/22physical_materials_counts_by_marc_format_typeThis query provides a count of all distinct instances, holdings, and items, by marc format type as classified using the 06 and 07 fields in the Marc leader.  It also includes format types based on the old Voyager bib format table, for comparison purposes. 
NEWjl41, vp25
CR19802/16/23lost_laptops_and_equipmentThis query finds lost laptops and equipment items at a given library.
NEWjl41, vp25
CR1992/23/23serials_by_owning_library_and_annex_holdingsThis query gets serials by owning library and LC class, and shows holdings at the Annex. 
NEWjl41, vp25
CR2002/28/23newly_received_items_countsThis query provides a count of newly received items by library and date. Item details are not included. 
NEWjl41, vp25
CR2012/28/23coutts_shelf_ready_items

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.



NEWjl41, vp25
CR2023/1/23patron_purchase_requests_folioThis query lists all patron purchase requests in Folio, including patron netid (where available), item details, circ count,  fund information, and item cost. 
NEWjl41, vp25
CR203
patron_purchse-requests_voyager_folio





CR2045/3/23missing_lost_claimed_returned

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. 


NEWjl41, vp25
CR2054/11/23libraries_locations_service_points_owners

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


NEWjl41, vp25
CR2064/11/23open_fines_older_than_x_days

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


NEWjl41, vp25
CR2074/17/23po_lines_no_expense_class

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


NEWama8, nb299,slm5
CR2085/5/23identifying_DVDs

This query identifies DVDs in the library location specified.


NEWjl41
CR2095/16/23Identifying_VHS

This query identifies VHS tapes in the library location specified.


NEWjl41
CR2105/16/23Missing_Lost_items_for_selectors

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.


NEWjl41
CR2115/16/23Locations_with_item_counts_for_permanent_and_temporary_locations

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.


NEWjl41
CR2125/16/23Number_of_holdings_records_in_permanent_and_temporary_locations 

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.


NEWjl41
CR2136/21/23current_encumbrances

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.


NEWnb299, jl41,slm5
CR2149/30/23Physical Item Counts

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


NEWvp25
CR2159/30/23Unique Title (Instance) Counts

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


NEWvp25
CR2169/30/23Microform Counts

This query provides counts of microforms, by format type. 


NEWvp25
CR2176/26/23Ematerial Counts

This query provides counts of ematerials by format type.


NEWvp25
CR2187/25/23Checkouts and Browses by location, date range and LC class

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.

 


NEWjl41, vp25
CR219 (Draft)7/28/23shelf_list_inventory

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.


NEWslm5, jl41
CR2208/15/23Voyager and Folio circ counts with parameters

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


NEWjl41, vp25
CR2219/15/23Microform Guides in Olin and Kroch showing copies in stacks or at the Annex

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

 


NEWjl41, vp25
CR2227/7/23key_counts

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.


NEWslm5
CR2232/7/24approvals_and_firm_orders

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. 


NEWjl41, np55, slm5
CR2242/7/24approval_plan_titles_by_vendor_and_statistical_code

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. 


NEWnp55
CR22511/22/23purchase_requests_with_days_elapsed_until_checkout

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


NEWjl41
CR22612/20/23funds_for_stewardship

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.


NEWama8, jl41, slm5
CR22712/20/23cu_press_cul_collections_match

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.


NEWjl41
CR22812/18/23brill_publications

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). 


NEWjl41
CR2291/10/24inventory_by_call_number_range_with_links_to_online_access

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.


NEWjl41
CR2302/5/24collection_inventory_with_checkouts_and_browses_by_date_rangeThis query finds all items in a given location or library, and shows all charges and browses within the specified date range.
NEWjl41
CR2313/8/24LTS This report gets serials received by LTS personal, on which day it was received, with bill_to location "Law Technical Services not included", item format as "Physical, receiving status as "received", po_number, po_number prefix, order_format, ship_to locationLTS Acquisition Statistic Dashboard, LTS Receiving Story
np55
CR2323/8/24inst_updated_dateThis query pulls the updated_by_userid and the updated_date field data from the inventory instance record. This data is then joined to the MARC 245/a field to get the instance title and instance HRID. 

np55
CR2333/12/24ADC Location Translation TableThis query creates the location translation table that is used for Annual Data Collection (ADC) queries.
NEWjl41
CR2343/18/24LTS Firm Orders CreatedThis reports looks at new LTS Purchase orders created for new titles, print and electronic (Law titles are not included)LTS Acquisition Statistic Dashboard, LTS Firm Orders
np55
CR2353/18/24LTS Approvals Titles AddedThis report gets new titles that were received on approval plan via statistical code.LTS Acquisition Statistic Dashboard, LTS Approval Orders
np55
CR2363/18/24LTS Miscellaneous Titles AddedThis report pulls new titles added by instance statistical codeLTS Acquisition Statistic Dashboard, LTS Miscellaneous Titles Added
np55
CR2373/28/24Campus Store matches by ISBNThis report matches a list of ISBN numbers from the Campus Store to library holdings

jl41
CR238/MCR23804/10/24Get Instance recordsThis report uses LDP/metadb function to get a particular MARC record field content

np55
CR239




slm5, jl41
CR2415/15/24cjkt_language_by_fiscal_yearThis query finds all item or holdings records in Chinese, Japanese, Korean and Tibetan languages that were added to the collection during or after the fiscal year specified. All libraries and locations are included. 

slm5, jl41
MCR400





MCR4014/10/24clean_up_100_e.sqlThis report pulles value of 100/ sf "e"LTS Data_clean up Dashboard
np55

Access Services Reports

Access Services reports
CUL Report Code
and Link to Query
Last UpdatedReport NameDescription~Time to runRelated JIRA
REP Issues
Setup                        
CR1001/14/22loans_countsloans by location
REP-103vp25
CR102
title_countprovides title counts for non-electronic resources cataloged in inventory by various filters
REP-230slm5
CR1034/12/22annex_non_circulating_itemsThis query finds items at the Annex that have a "non-circulating" permanent loan type, excluding rare and special collections. It also finds items with an hourly loan type.~1 minute
jl41
CR10411/19/21claimed_returned

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').


REP-203vp25
CR10511/16/21item_related_exceptionsshows a list of exceptions, which are actions taken by an operator (staff member) and are recorded in the circulation log. An example is when an item has been manually discharged or discharged by an operator.
REP-278vp25
CR10609/01/22

missing_items


creates a list of missing items, and includes all item details.
REP-152vp25
CR10701/31/22requestslist of patron requests within a specified date range, and by request type.
REP105; REP-143; REP-274; REP-275vp25, jl41
CR108
services_usagenumber of circulation transactions by service-point and transaction type, with time aggregated to date, day of week, and hour of day.
REP-243vp25
CR110
item count

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


REP-19
REP-20
REP-21
REP-229
REP-231
nb299
CR117
acrl_circulationused for ACRL circulation count reporting
UXPROD-1915vp25
CR11812/14/21items_lost_in_transitList of items where item status is 'in transit' but they haven't been checked in at the home location (permanent location) after a specified time period. Therefore the items might have been lost or misplaced on their way back to their home location.
REP-190vp25
CR119

patron_list


List of active patrons including patron details and group type (faculty, undergrads, etc.)
REP-220vp25
CR12411/19/21

lost_paid_returned

NOTE: under review; contact jl41 or vp25

list of items that were lost and paid for by the patron, but later found and returned. Identifies items for which patrons need to be reimbursed. 
REP-88vp25
CR12603/10/23Borrow_direct_interlibrary_loan_recallsThis query provides a list of overdue CUL-owned items checked out to Borrow Direct or Interlibrary loan patrons that have been recalled (these items have not yet been received by CUL, based on the current due date). 
REP-272vp25, jl41
CR12712/10/21Borrow_direct_interlibrary_loan_overdue_itemsProvides a list of items owned by CUL and borrowed by BD/ILL patrons, that are overdue.
REP-272vp25

CR128


10/06/22ceased_or_cancelled_serials_with_annex_matchesProvides a list of ceased or cancelled serials based on the specified location and shows matches to the annex where they exist. The holdings summary for both the specified location and the annex location are displayed. 
REP-215

jl41, vp25

CR14501/28/22Equipment_with_holdings_checkouts_statCodesThis report shows all laptops and equipment for a given library, with total checkouts and renewals as of today's date. It includes patron group name as well as stat codes to indicate whether the item has been archived or not. . 
REP-291, REP-103jl41, vp25
CR14801/19/22Expired_patrons_with_unreturned_overdue booksFinds all expired patrons with unreturned overdue books (most are lost). Shows book details and some patron information (net ID, patron group).
REP-292jl41, vp25
CR14901/28/22Checkouts_by_loan_policy_and_material_type _overviewOverview of checkouts by owning library, loan policy and material type. Allows you to see how libraries are circulating their equipment.
Newjl41, vp25
CR15002/02/22Physical_and_Ereserve_stats

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 cannot be run unless data from ARES are imported into the LDP.


REP-106jl41, vp25
CR15101/28/22checkouts_by_useridFinds checkouts by a specific userid. This report is used to get a list of checkouts for an individual patron. It also contains patron information. 
Newjl41, vp25
CR15202/10/22patron_groups_with_demographicsThis report groups and counts active patrons by college and department.
REP-271jl41, vp25
CR15302/10/22active_patrons_with_demographicsThis report provides a list of all active patrons, along with their demographics (name, netid, college, department, patron group).
REP-271jl41, vp25
CR15502/22/22Title_count_by_fiscal_year_VoyagerProvides a count of titles acquired by fiscal year in a given location, using pre-Folio data from Voyager. This report does not contain subject headings. Needs access to VGER data.
Newjl41, vp25
CR15602/28/02

Historical_charges_from_Voyager


Provides a count of historical charges. This query can be modified to get item level details. Although it is possible to use filters such as location, call number, item type, item create date, material type, and Voyager bib ids, the filters in this report are hard-coded, as they are a complex set of filters. Please ask Joanne Leary for assistance.  Details at the item level are not available. Needs access to VGER data.
Newjl41, vp25
CR15802/18/22Circ_stats_1_checkouts

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


New jl41, vp25
CR15903/09/23Circ_stats_2_requests

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.


Newjl41, vp25
CR1705/12/22item_status_in_processShows items that still have an "in Process" status, which may indicate that they were not properly discharged when they were received at the owning library (after being cataloged). < 1 minuteNEWjl41, vp25
CR1715/23/22BD_ILL_selectors_report -Part 1Lists title, patron group and department (where available) for items borrowed by CUL patrons from other universities on Borrow Direct and Interlibrary Loan. Parameters for loan date range included. a few secondsREP-156jl41
CR1725/24/22BD_ILL_selectors_report - Part 2Lists item details for items borrowed from CUL by other universities' patrons on Borrow Direct and Interlibrary Loan. Parameters for loan date range included. a few secondsREP-156jl41, vp25
CR1776/7/22items_in_process

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.


NEWjl41
CR1828/17/22voyager_checkouts_by_circ_desk

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.


NEWjl41, vp25
CR18310/11/22laptop_circ_countsThis query counts laptop circs by library, date, loan type, and laptop type (Mac or PC).  Please note that until August 2022, Mann laptop laptop loans were made through LibCal as well as Folio, and this report includes only Folio counts. As of early August 2022, all Mann laptop loans are in Folio. 
NEWjl41, vp25
CR1899/20/22book_listThis query creates a list of books at a specified library showing title, author, call number, year of publication and number of circs (Voyager plus Folio).
NEWjl41, vp25
CR19010/11/22inactive_patrons_with_open_finesThis query finds inactive patrons with open fines. 
NEWjl41, vp25
CR19310/25/22filled_delivery_requestsThis query provides a count of all contactless delivery and circulation desk pickup requests, by fiscal year. Patron group, material type, request type, and location details are included. 
NEWjl41
CR19411/14/22checkouts_and_checkins_by_date_range_and_service_pointThis query finds checkouts and checkins by month for a given service point and date range. Item records that have been deleted will show a material type name of "Unknown". Because most deleted records are equipment records, these items have been categorized as "Equipment" collection type as a best guess.
NEWjl41
CR19612/02/22lost_laptops_and_equipment

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.


NEWjl41
CR19712/15/22physical_materials_counts_by_marc_format_typeThis query provides a count of all distinct instances, holdings, and items, by marc format type as classified using the 06 and 07 fields in the Marc leader.  It also includes format types based on the old Voyager bib format table, for comparison purposes. 
NEWjl41, vp25
CR19802/16/23lost_laptops_and_equipmentThis query finds lost laptops and equipment items at a given library.
NEWjl41, vp25
CR1992/23/23serials_by_owning_library_and_annex_holdingsThis query gets serials by owning library and LC class, and shows holdings at the Annex. 
NEWjl41, vp25
CR2002/28/23newly_received_items_countsThis query provides a count of newly received items by library and date. Item details are not included. 
NEWjl41, vp25
CR2012/28/23coutts_shelf_ready_items

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.



NEWjl41, vp25
CR2023/1/23patron_purchase_requests_folioThis query lists all patron purchase requests in Folio, including patron netid (where available), item details, circ count,  fund information, and item cost. 
NEWjl41, vp25














Accounting Reports

Accounting Reports
CUL Report Code and Link to QueryLast
Updated
Report NameDescription~Time to runRelated JIRA
REP Issues
Setup
CR101 07/11/22fund_expenditures_by_po_line_invoice_paymentsshows fund expenditures by purchase order line within a given invoice payment date range. For each purchase order line number, the fund data attributes are shown alongside the purchase order line transaction amount.
REP-157, REP-285slm5
CR112
daily_appr_inv_manualsused by Accounting for Endowed Daily Approve Invoice by Condition Flag - Manual Check
REP-10nb299
CR113
daily_appr_inv_exportedused by Accounting for Endowed Daily Approve Invoice by Condition Flag - Exported Check
REP-10nb299
CR114
daily_appr_inv_vendorused by Accounting for Endowed Daily Approve Invoice by Group, Vendor, Invoice Number, and Invoice Lines to Check Record
REP-10nb299
CR115
daily_dist_by_accountused by Accounting for Endowed Payables Distribution by Account
REP-10nb299
CR116

10/12/21 

payables_inv_not_fed_notesused by Accounting for Endowed Payables Invoices Not Fed
REP-10nb299
CR120
daily_appr_inv_controlsummary of vouchers by account number, including exported and manuals
REP-10nb299
CR121
daily_appr_inv_vendor_fundprovides the voucher lines details per vendor invoice and funds and results show vendor invoice payments from transactions on the previous day
REP-10nb299
CR122

9/12/22

fund_detailprovides fund details summary for active funds
REP-98nb299
CR123

7/14/22 

open_ordersprovides a list of open purchase orders and their encumbrance and/or amount paid, broken down by purchase order lines
REP-283nb299
CR125

7/14/22

appr_invoices_vendor_fundsProvides the list of approved invoices within a date range along with vendor name, invoice number, fund group and fund used.
REP-82nb299
CR130

9/9/21 

fund_expenditures_by_po_lineShows fund expenditures by purchase order line. Fund data attributes such as fund group, fund type, fund code, transaction type, and transaction type are provided.
REP-157, REP-285slm5
CR13201/24/2023YTD_acct_bal_by_ledger_univ_acctThis report provides the year-to-date external account cash balance along with total_expenditures, initial allocation, and net allocation. This is an accounting report used for monthly reconciliation.


CR134

9/3/22

appr_invoices_bib_dataThis query provides the list of approved invoices within a date range along with vendor name, invoice number, fund details and purchase order data. It also provides some Bib data, which will be more complete after the release of Folio Kiwi scheduled for November 2021. The elements that will be added are commented out in the main query.
REP-39, REP-83, REP-287nb299
CR134_F11/22/22appr_invoices_for_finance_teamThis query provides the list of approved invoices within a date range along with vendor name, finance group name, vendor invoice number, fund details, and purchase order details.
REP-39, REP-83, REP-287nb299
CR135

10/4/21

sum_appr_inv_ledger_acctThis query provides the total of transaction amount by account number along with the finance ledger and finance group within a date range

nb299
CR136

10/4/21 

appr_inv_per_univ_acct_detail_date_rangeThis query provides the list of approved invoices within a date range along with teh external account, vendor name, invoice number, and transaction amount.

nb299
CR13911/5/21daily_FBO_inv_appr_paid_diff_dateThis query provides a list of approved invoices that have been paid at a different date.
Sometimes Folio won't allow an invoice to get paid and the invoice will only be paid at a
later day, after changes have been made.


nb299
CR14011/8/21fbo_change_in_allocationThis report provides a list of change in allocation per date range. A negative transaction amount is for an increase in allocation and a positive amount is for a decrease in allocation.

nb299
CR14212/1/21appr_inv_no_transac_date_rangeThis query provides the list of approved invoices within a date range for which transactions has not been created.This query should not return any data if the system is working properly.

nb299
CR15701/24/2023Funds_and_teams_with_expense_classProvides a detailed current date report of funds and teams with amounts spent, encumbered, and remaining. It also shows the expense class.
Newnb299
CR16501/23/2023funds_and_teamsProvides a current date report of funds and teams with amounts spent, encumbered, and remaining. This report does not include expense class (see CR157).
Newnb299
CR1796/23/22bursared_items_by_fine_fee_date_and_material_typeThis report finds bursared items with fines or fees using the payment method of "CUL Transfer Account" and a fine fee type action of "Transferred Fully." Filters for fine date range and material type are included.
NEWslm5
CR19501/05/24 (revised)expense_transferThis query is a customization of CR-134 (paid invoices with bib data) for the purpose of identifying expenditures that can be transferred from unrestricted funds to restricted funds.
NEWjl41
CR2074/17/2023po_lines_no_expense_class

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


NEWama8, nb299,slm5
CR21306/21/2023current_encumbrances

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.


NEWnb299, jl41,slm5

Acquisitions Reports

Acquisitions Reports

CUL Report Code

and Link to Query

Last UpdatedReport NameDescription~Time to runRelated JIRA
REP Issues
Setup
CR109
standing order purchase ordersdaily report run by acquisitions staff to review firm purchase orders against standing purchase orders
REP-283np55
CR111
duplicate orders on ISBNused by Acquisitions to identify recent orders that duplicate already held volumes or that lack standard numbers and require manual searching
REP-281np55
CR123

9/22/21 

open_ordersprovides a list of open purchase orders and their encumbrance and/or amount paid, broken down by purchase order lines
REP-283nb299
CR13410/26/2021appr_invoices_bib_dataThis query provides the list of approved invoices within a date range along with vendor name, invoice number, fund details and purchase order data. It also provides some Bib data, which will be more complete after the release of Folio Kiwi scheduled for November 2021. The elements that will be added are commented out in the main query.
REP-39, REP-83, REP-287nb299
CR19501/05/24 (revised)expense_transfer

NEWjl41
CR21306/21/2023current_encumbrances

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.


NEWnb299, jl41,slm5
CR2313/8/24LTS ReceivingThis report gets serials received by LTS personal, on which day it was received, with bill_to location "Law Technical Services not included", item format as "Physical, receiving status as "received", po_number, po_number prefix, order_format, ship_to locationLTS Acquisition Statistic Dashboard, LTS Receiving Story
np55
CR2323/8/24inst_updated_dateThis query pulls the updated_by_userid and the updated_date field data from the inventory instance record. This data is then joined to the MARC 245/a field to get the instance title and instance HRID. 

np55
CR2343/18/24LTS Firm Orders CreatedThis reports looks at new LTS Purchase orders created for new titles, print and electronic (Law titles are not included)LTS Acquisition Statistic Dashboard, LTS Firm Orders

CR2353/18/24LTS Approvals Titles AddedThis report gets new titles that were received on approval plan via statistical code.LTS Acquisition Statistic Dashboard, LTS Approval Orders

CR236


3/18/24LTS Miscellaneous Titles AddedThis report pulls new titles added by instance statistical codeLTS Acquisition Statistic Dashboard, LTS Miscellaneous Titles Added

Annual Statistics Reports

Documentation (in progress) on how these reports are being used for the CUL annual data collection and for national reporting is available on the FOLIO Annual Statistics Documentation page. 


Annual Statistics Reports

CUL Report Code

and Link to Query

Last UpdatedReport NameDescription~Time to runRelated JIRA
REP Issues
Setup
CR144 - BY REQUEST - please use the Request Form to get this report

7/1/22

in review; not currently available

volume_count_stats

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

newly created and total existing volume count statistics for Cornell annual statistics reports by unit and on campus versus the annex



np55

CR147_A  - BY REQUEST - please use the Request Form to get this reportin review, not currently availablecirculation_chargesrenewals_adcCirculation statistics (charges and renewals) by library, patron group and collection type (regular, reserve, laptop, equipment) used for the annual data collection. Excludes SPEC and ILL/BD owned by other institutions.
REP-195jl41, lm15
CR147_B  - BY REQUEST - please use the Request Form to get this reportin review, not currently availableannual_circ_cons_pg_statsAnnual circulation statistics by consolidated patron group
REP-195jl41

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

This set of queries provides soundrecording piece count statistics for Cornell annual statistics reports,  by unit "on campus," and by unit at the Annex.14 minutesNewlm15

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

This set of queries provides videorecording piece count statistics for Cornell annual statistics reports,  by unit "on campus," and by unit at the Annex.8 minutesNewlm15
CR162  - BY REQUEST - please use the Request Form to get this report

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

Count of all physical titles except those in microform format, by bib format9 minutesREP-230adc1lm15
CR163  - BY REQUEST - please use the Request Form to get this report

5/10/22

in review; not currently available


title_count_micro_adc_stats

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

Count of microform titles, by bib format.7 minutesREP-230adc2lm15
CR164   - BY REQUEST - please use the Request Form to get this report

8/11/22

in review; not currently available


title_count_servremo_viaculcodes_adc

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.10 minutesREP-230adc3np55, lm15
CR164alt   - BY REQUEST - please use the Request Form to get this report

8/5/22

in review; not currently available

title_count_servremo_adc

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

Count of serv,remo titles, by bib format, through leader coding.  An alt query for limited use. Contact Linda Miller for more info.10 minutesREP-230adc3np55, lm15
CR168IN REVIEW
5/11/22
physical_serial_titles_currently_received_adcCount of physical serial titles currently received for the annual data collection. Blocked.~3 minutespart of REP-228np55, lm15
CR169 IN REVIEW

6/24/33


database_count_adcGets counts of databases for ACRL and NCES reporting. Note: This currently includes all CISER databases. May be revised.~6 minutespart of REP-41np55, lm15
CR180 - BY REQUEST - please use the Request Form to get this report

6/24/22

in review; not currently available

773_and_899_aggr_counts_adc

Note: long-running query

Gets counts of aggregators and other collections coded in 773 and 899 fields. Used to help track changes in e-counts from year to year. First version.~24 minutesNEWnp55, lm15
CR19210/25/22withdrawn_items

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. 



NEWjl41
CR19310/25/22filled_delivery_requestsThis query provides a count of all contactless delivery and circulation desk pickup requests, by fiscal year. Patron group, material type, request type, and location details are included. 
NEWjl41


Cataloging Reports

Cataloging Reports                                                                                                                                                                                                                                  
CUL Report Code
and Link to Query
Last UpdatedReport NameDescription~Time to runRelated JIRA
REP Issues
Setup
CR102
title_countprovides title counts for non-electronic resources cataloged in inventory by various filters
REP-230slm5
CR110
item count

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


REP-19
REP-20
REP-21
REP-229
REP-231
nb299
CR1786/8/22cataloger_countsLists items catalogued (instance id and title) by cataloger netid, date, ttype, and unit (lts, law, etc.)
NEWjmp8, jl41, vp25


Collection Development Reports


Collection Development                                                                                                                                                                                                                                  
CUL Report Code
and Link to Query
Last
Updated
Report NameDescription~Time to runRelated JIRA
REP Issues
Setup
CR101 7/7/22fund_expenditures_by_po_line_invoice_paymentsshows fund expenditures by purchase order line within a given invoice payment date range. For each purchase order line number, the fund data attributes are shown alongside the purchase order line transaction amount.
REP-157, REP-285slm5
CR122

9/12/22

fund_detailprovides fund details summary for active funds
REP-98nb299
CR123

7/14/22

open_ordersprovides a list of open purchase orders and their encumbrance and/or amount paid, broken down by purchase order lines
REP-283nb299
CR125

7/14/22 

appr_invoices_vendor_fundsProvides the list of approved invoices within a date range along with vendor name, invoice number, fund group and fund used.
REP-82nb299
CR130

9/9/21 

fund_expenditures_by_po_lineShows fund expenditures by purchase order line. Fund data attributes such as fund group, fund type, fund code, transaction type, and transaction type are provided.
REP-157, REP-285slm5
CR131

9/24/21 

duplicate instances on ISBNs

This report checks existing instances created within the last 5 days for duplicate records in the system. It shows duplicate records with date created for new duplicates along with local identifiers (899 and 773 MARC fields). It shows both valid and invalid ISBNs.


REP-281np55
CR1348/31/23appr_invoices_bib_data

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. 


REP-39, REP-83, REP-287nb299, jl41
CR13811/5/21

orders_split_funds


This query provides the list of split funds purchases for a purchase order line that has multiple funds used along with the transaction amount for each fund and the percentage spent.
REP-98nb299
AHR10302/02/2022bd_with_annexThis query searches for holdings note type 'Bound with item data' and related item records matched on BC.

np55
CR1577/07/22Funds_and_teams_with_expense_classProvides a detailed current date report of funds and teams with amounts spent, encumbered, and remaining. It also shows the expense class.
Newjl41, vp25
CR1657/14/22funds_and_teamsProvides a current date report of funds and teams with amounts spent, encumbered, and remaining. This report does not include expense class (see CR157).
Newnb299
CR1715/23/22BD_ILL_selectors_report -Part 1Lists title, patron group and department (where available) for items borrowed by CUL patrons from other universities on Borrow Direct and Interlibrary Loan. Parameters for loan date range included. a few secondsREP-156jl41
CR1725/24/22BD_ILL_selectors_report - Part 2Lists item details for items borrowed from CUL by other universities' patrons on Borrow Direct and Interlibrary Loan. Parameters for loan date range included. a few secondsREP-156jl41, vp25
CR1735/24/22Patron_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 (all currently only for data in Folio). a few secondsNEWjl41, vp25
CR1756/6/22team_related_split_fundsShows split funds by PO line that enables selectors to filter by the funds for their team while also seeing what other team funds split a purchase order payment with them.1 minuteNEWnb299
CR1818/2/22circ_count_by_instanceHRIDThis report returns circ counts from both Voyager and Folio for an individual instance HRID, as selected in the parameters. Title, location, and number of items are also included.  
NEWjl41, vp25
CR19501/05/24 (revised)expense_transferThis query is a customization of CR-134 (paid invoices with bib data) for the purpose of identifying expenditures that can be transferred from unrestricted funds to restricted funds.~20 secondsNEWjl41
CR2023/1/23patron_purchase_requests_folioThis query lists all patron purchase requests in Folio, including patron netid (where available), item details, circ count,  fund information, and item cost. 
NEWjl41, vp25
CR21306/21/2023current_encumbrances

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.


NEWnb299, jl41,slm5

Collection Management Reports


Collection Management
CUL Report Code
and Link to Query
Last
Updated
Report NameDescription~Time to runRelated JIRA
REP Issues
Setup
CR1818/2/22circ_count_by_instanceHRIDThis report returns circ counts from both Voyager and Folio for an individual instance HRID, as selected in the parameters. Title, location, and number of items are also included.  
NEWjl41, vp25
CR1859/20/22ceased_cancelled_titles_holdings_levelThis query finds ceased or cancelled titles at the holdings level for a specified owning library and LC class, and shows if there are holdings at the Annex.
NEWjl41, vp25
CR1869/20/22ceased_cancelled_titles_item_levelThis query finds ceased or cancelled titles at the item level, for a specified owning library and LC class, and shows if there are holdings at the Annex.
NEWjl41, vp25
CR1899/20/22book_listThis query creates a list of books at a specified library showing title, author, call number, year of publication and number of circs (Voyager plus Folio).
NEWjl41, vp25
CR2085/5/23identifying_DVDs

This query identifies DVDs in the library location specified.


NEWjl41

HathiTrust


HathiTrust Reports

CUL Report Code
and Link to Query
Last
Updated
Report NameDescription~Time to runRelated JIRA
REP Issues
Setup
CR143_A - BY REQUEST - please use the Request Form to get this report12/6/21

hathitrust_serial_holdings

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

This set of queries creates the list of serials holdings in conforming to this specification that CUL sends to HathiTrust. The queries create 11 intermediate tables that write to the local schema before creating the final dataset.
REP-18np55
CR143_B - BY REQUEST - please use the Request Form to get this report12/13/21

hathitrust_monograph_holdings

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

This set of queries creates the list of single volume monographic (SVM) holdings conforming to this specification that CUL sends to HathiTrust.
REP-18np55
CR143_C - - BY REQUEST - please use the Request Form to get this report12/13/21

hathitrust_multivolume_holdings

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

This set of queries creates the list of multivolume monographic (MVM) holdings conforming to this specification that CUL sends to HathiTrust.
REP-18np55






  • No labels