...
All Reports | ||||||
Link to SQL Query | Last Updated | Report Name | Description | ~Time to run | Related JIRA REP Issues | Setup |
---|---|---|---|---|---|---|
CR100 | 1/14/22 | loans_counts | loans by location | REP-103 | vp25 | |
CR101 | 7/11/22 | fund_expenditures_by_po_line_invoice_payments | shows 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-285 | slm5 | |
CR102 | 7/27/21 | title_count *WARNING: long-running query! | provides title counts for non-electronic resources cataloged in inventory by various filters | REP-230 | slm5 | |
CR103 | 4/12/22 | annex_non_circulating_items | This 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 | |
CR104 | 11/19/21 | claimed_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-203 | vp25 | |
CR105 | 11/16/21 | item_related_exceptions | shows 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-278 | vp25 | |
CR106 | 09/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/22 | requests | list of patron requests within a specified date range, and by request type. | REP105; REP-143; REP-274; REP-275 | vp25, jl41 | |
CR108 | 7/27/21 | services_usage | number of circulation transactions by service-point and transaction type, with time aggregated to date, day of week, and hour of day. | REP-243 | vp25 | |
CR109 | 7/1/21 | standing_order_purchase_orders | This 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-283 | np55 | |
CR110 | 7/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 | |
CR111 | 6/21/21 | duplicate orders on ISBN | used by Acquisitions to identify recent orders that duplicate already held volumes or that lack standard numbers and require manual searching | REP-281 | np55 | |
CR112 | 7/8/21 | daily_appr_inv_manuals | used by Accounting for Endowed Daily Approve Invoice by Condition Flag - Manual Check | REP-10 | nb299 | |
CR113 | 7/8/21 | daily_appr_inv_exported | used by Accounting for Endowed Daily Approve Invoice by Condition Flag - Exported Check | REP-10 | nb299 | |
CR114 | 7/8/21 | daily_appr_inv_vendor | used by Accounting for Endowed Daily Approve Invoice by Group, Vendor, Invoice Number, and Invoice Lines to Check Record | REP-10 | nb299 | |
CR115 | 8/3/21 | daily_dist_by_account | used by Accounting for Endowed Payables Distribution by Account | REP-10 | nb299 | |
CR116 | 10/12/21 | payables_inv_not_fed_notes | used by Accounting for Endowed Payables Invoices Not Fed | REP-10 | nb299 | |
CR117 | 7/12/21 | acrl_circulation | used for ACRL circulation count reporting | UXPROD-1915 | vp25 | |
CR118 | 12/14/21 | items_lost_in_transit | List 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-190 | vp25 | |
CR119 | 7/28/21 updated 9/4/22 | patron_list | List of patrons including patron details and group type (faculty, undergrads, etc.) | REP-220 | vp25 jl41 | |
CR120 | 7/8/21 | daily_appr_inv_control | summary of vouchers by account number, including exported and manuals | REP-10, REP-284 | nb299 | |
CR121 | daily_appr_inv_vendor_fund | provides the voucher lines details per vendor invoice and funds and results show vendor invoice payments from transactions on the previous day | REP-10 | nb299 | ||
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-287 | nb299 | |
CR123 | 7/14/22 | open_orders | provides a list of open purchase orders and their amount paid, broken down by purchase order lines | REP-283 | nb299 | |
CR124 | 11/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_funds | Provides the list of approved invoices within a date range along with vendor name, invoice number, fund group and fund used. | REP-82 | nb299 | |
CR126 | 03/10/23 | Borrow_direct_interlibrary_loan_recalls | This 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 | |
CR127 | 12/10/21 | Borrow_direct_interlibrary_loan_overdue_items | Provides a list of items owned by CUL and borrowed by BD/ILL patrons, that are overdue. | REP-272 | vp25 | |
10/06/22 | ceased_or_cancelled_serials_with_annex_matches | Provides 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 | ||
CR128B | 10/11/22 | item_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-215 | jl41, vp25 | |
CR129 | 8/27/21 | pol_notes | Lists purchase order lines with internal notes associated with each of those lines | REP-280 | np55 | |
CR130 | 9/9/21 | fund_expenditures_by_po_line | Shows 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-285 | slm5 | |
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-281 | np55 | |
CR132 | 01/23/2023 | YTD_acct_bal_by_ledger_univ_acct | This 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 report | 12/10/21 | shelf_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 | New | jl41, 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-287 | nb299, jl41 | |
CR134_F | 11/22/22 | appr_invoices_for_finance_team | This 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-287 | nb299 | |
CR135 | 11/5/21 | sum_appr_inv_ledger_acct | This 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_range | This 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_lessthan1000 | This query searches for duplicate invoices within 180 days with less than $1,000 total amount of the invoice accordingly. | np55 | |||
CR138 | 6/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-98 | nb299 | |
CR139 | 11/5/21 | daily_FBO_inv_appr_paid_diff_date | This 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 | ||
CR140 | 11/8/21 | fbo_change_in_allocation | This 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 | ||
CR141 | 11/18/21 | lost_bursared_returned | This 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. | New | jl41, vp25 | |
CR142 | 12/1/21 | appr_inv_no_transac_date_range | This 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 report | 12/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-18 | np55 | |
CR143_B - BY REQUEST - please use the Request Form to get this report | 12/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-18 | np55 | |
CR143_C - BY REQUEST - please use the Request Form to get this report | 12/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-18 | np55 | |
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-21 | np55 | |
CR145 | 01/28/22 | Equipment_with_holdings_checkouts_statCodes | This 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-103 | jl41, vp25 | |
CR146 | 01/11/22 | missing_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 report | in review, not currently available | circulation_chargesrenewals_adc | Circulation 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-195 | jl41, lm15 | |
CR147_B - BY REQUEST - please use the Request Form to get this report | in review, not currently available | annual_circ_cons_pg_stats | Annual circulation statistics by consolidated patron group | REP-195 | jl41 | |
CR148 | 01/19/22 | Expired_patrons_with_unreturned_overdue books | Finds all expired patrons with unreturned overdue books (most are lost). Shows book details and some patron information (net ID, patron group). | REP-292 | jl41, vp25 | |
CR149 | 01/28/22 | Checkouts_by_loan_policy_and_material_type _overview | Overview of checkouts by owning library, loan policy and material type. Allows you to see how libraries are circulating their equipment. | New | jl41, vp25 | |
CR150 - BY REQUEST - please use the Request Form to get this report | 01/28/22 | Physical_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-106 | Jl41, vp25 | |
CR151 | 01/28/22 | checkouts_by_userid | Finds checkouts by a specific userid. This report is used to get a list of checkouts for an individual patron. It also contains patron information. | New | jl41, vp25 | |
CR152 | 02/10/22 | patron_groups_with_demographics | This report groups and counts active patrons by college and department. | REP-271 | jl41, vp25 | |
CR153 | 02/10/22 | active_patrons_with_demographics | This report provides a list of all active patrons, along with their demographics (name, netid, college, department, patron group). | REP-271 | jl41, vp25 | |
CR154 | 06/09/22 | titles_by_subject_and_language | Shows titles held by subject, format, language, and location. | REP-229 | np55, jl41 | |
CR155 | 02/22/22 | Title_count_by_fiscal_year_Voyager | Provides 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. | New | jl41, vp25 | |
CR156 - BY REQUEST - please use the Request Form to get this report | 02/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. | New | jl41, vp25 | |
CR157 | 01/24/2023 | Funds_and_teams_with_expense_class | Provides a detailed current date report of funds and teams with amounts spent, encumbered, and remaining. It also shows the expense class. | New | nb299 | |
CR158 | 02/18/22 | Circ_stats_1_checkouts | Checkouts of the last month by library, patron group, and material type. | New | jl41, vp25 | |
CR159 | 03/09/23 | Circ_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. | New | jl41, 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. | 14 minutes | New | lm15 |
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. | 8 minutes | New | lm15 |
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. | 9 minutes | REP-230adc1 | lm15 |
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 minutes | REP-230adc2 | lm15 |
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. | 10 minutes | REP-230adc3 | np55, 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 minutes | REP-230adc3 | np55, lm15 |
CR165 | 01/23/2023 | funds_and_teams | Provides a current date report of funds and teams with amounts spent, encumbered, and remaining. This report does not include expense class (see CR157). | New | nb299 | |
CR166 | 4/14/22 | pcc_records_cataloged_by_user | lists PCC records cataloged by a given user | New | np55 | |
CR167 | 4/27/22 | expired_patron_with_unreturned_overdue_books | Finds all expired patrons with unreturned overdue books (most are lost). Shows book details and some patron info (net ID, patron group). | < 1 minute | New | jl41 |
CR168 | IN REVIEW 5/11/22 | physical_serial_titles_currently_received_adc | Count of physical serial titles currently received for the annual data collection. Blocked. | ~3 minutes | part of REP-228 | np55, lm15 |
CR169 | 6/24/22 | database_count_adc | Gets counts of databases for ACRL and NCES reporting. Note: This currently includes all CISER databases. May be revised. | ~6 minutes | part of REP-41 | np55, lm15 |
CR170 | 5/12/22 | item_status_in_process | Shows 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 minute | NEW | jl41, vp25 |
CR171 | 5/23/22 | BD_ILL_selectors_report -Part 1 | Lists 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 seconds | REP-156 | jl41 |
CR172 | 5/24/22 | BD_ILL_selectors_report - Part 2 | Lists 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 seconds | REP-156 | jl41, vp25 |
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). | a few seconds | NEW | jl41, vp25 | |
CR174 | 10/25/22 | BD_ILL_counts | Consists of two separate queries. The first one provides a count of BD and ILL loans from CUL to other universities. The second one provides a count of BD and ILL loans to CUL from other universities. The two queries must be run individually. | a few seconds | REP-156 | jl41, vp25 |
CR174A | 9/30/23 | BD_ILL_cul_lender | The 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-156 | jl41, vp25 | |
CR174B | 9/30/23 | BD_ILL_cul_borrower | The 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-156 | jl41, vp25 | |
CR175 | 6/6/22 | team_related_split_funds | Shows 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 minute | NEW | np55, vp25 |
CR176 | 6/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. | NEW | jl41 | |
CR177 | 6/7/22 | items_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. | NEW | jl41 | |
CR178 | 6/8/22 | cataloger_counts | Lists items catalogued (instance id and title) by cataloger netid, date, ttype, and unit (lts, law, etc.) | NEW | jmp8, jl41, vp25 | |
CR179 | 6/23/22 | bursared_items_by_fine_fee_date_and_material_type | This 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. | <1 minute | NEW | slm5 |
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. | ~24 minutes | NEW | np55, lm15 |
CR181 | 8/2/22 | circ_count_by_instanceHRID | This 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. | NEW | jl41, vp25 | |
CR182 | 8/17/22 | voyager_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. | NEW | jl41, vp25 | |
CR183 | 10/11/22 | laptop_circ_counts | This 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. | NEW | jl41, vp25 | |
CR184A | 11/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 | ||
CR184B | 10/24/22 | physical_collections_use_split | This 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 | NEW | jl41, vp25 | |
CR185 | 9/20/22 | ceased_cancelled_titles_holdings_level | This 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. | NEW | jl41, vp25 | |
CR186 | 9/20/22 | ceased_cancelled_titles_item_level | This 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. | NEW | jl41, vp25 | |
CR187 | 9/20/22 | acronyms_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. | NEW | jl41, vp25 | |
CR188 | 9/20/22 | acronyms_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. | NEW | jl41, vp25 | |
CR189 | 9/20/22 | book_list | This 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). | NEW | jl41, vp25 | |
CR190 | 10/11/22 | inactive_patrons_with_open_fines | This query finds inactive patrons with open fines. | NEW | jl41, vp25 | |
CR191 (under review) | 10/11/22 | physical_materials_counts | This 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. | NEW | vp25 | |
CR192 | 10/25/22 | withdrawn_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. | NEW | jl41, vp25 | |
CR193 | 10/25/22 | filled_delivery_requests | This 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. | NEW | jl41, vp25 | |
CR194 | 11/14/22 | checkouts_and_checkins_by_date_range_and_service_point | This 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. | ~3 seconds | NEW | jl41 |
CR195 | 07/19/23 | expense_transfer | This 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 seconds | NEW | jl41 |
CR196 | 12/02/22 | lost_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. | NEW | jl41, vp25 | |
CR197 | 12/15/22 | physical_materials_counts_by_marc_format_type | This 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. | NEW | jl41, vp25 | |
CR198 | 02/16/23 | lost_laptops_and_equipment | This query finds lost laptops and equipment items at a given library. | NEW | jl41, vp25 | |
CR199 | 2/23/23 | serials_by_owning_library_and_annex_holdings | This query gets serials by owning library and LC class, and shows holdings at the Annex. | NEW | jl41, vp25 | |
CR200 | 2/28/23 | newly_received_items_counts | This query provides a count of newly received items by library and date. Item details are not included. | NEW | jl41, vp25 | |
CR201 | 2/28/23 | coutts_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. | NEW | jl41, vp25 | |
CR202 | 3/1/23 | patron_purchase_requests_folio | This query lists all patron purchase requests in Folio, including patron netid (where available), item details, circ count, fund information, and item cost. | NEW | jl41, vp25 | |
CR203 | patron_purchse-requests_voyager_folio | |||||
CR204 | 5/3/23 | missing_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. | NEW | jl41, vp25 | |
CR205 | 4/11/23 | libraries_locations_service_points_owners | This query finds libraries, locations, service points and fine owners associated with the service points. | NEW | jl41, vp25 | |
CR206 | 4/11/23 | open_fines_older_than_x_days | This query finds open fines older than a given number of days old, for an owning library. | NEW | jl41, vp25 | |
CR207 | 4/17/23 | po_lines_no_expense_class | The query finds any purchase order line that doesn't have an expense class assigned. | NEW | ama8, nb299,slm5 | |
CR208 | 5/5/23 | identifying_DVDs | This query identifies DVDs in the library location specified. | NEW | jl41 | |
CR209 | 5/16/23 | Identifying_VHS | This query identifies VHS tapes in the library location specified. | NEW | jl41 | |
CR210 | 5/16/23 | Missing_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. | NEW | jl41 | |
CR211 | 5/16/23 | Locations_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. | NEW | jl41 | |
CR212 | 5/16/23 | Number_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. | NEW | jl41 | |
CR213 | 6/21/23 | current_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. | NEW | nb299, jl41,slm5 | |
CR214 | 9/30/23 | Physical Item Counts | This query provides counts of physical items (excluding microforms), by format type and holdings library and location. | NEW | vp25 | |
CR215 | 9/30/23 | Unique Title (Instance) Counts | This query provides counts of unique titles (instances) of physical items (excluding microforms), by format type. | NEW | vp25 | |
CR216 | 9/30/23 | Microform Counts | This query provides counts of microforms, by format type. | NEW | vp25 | |
CR217 | 6/26/23 | Ematerial Counts | This query provides counts of ematerials by format type. | NEW | vp25 | |
CR218 | 7/25/23 | Checkouts 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. | NEW | jl41, vp25 | |
CR219 (Draft) | 7/28/23 | shelf_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. | NEW | slm5, jl41 | |
CR220 | 8/15/23 | Voyager 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. | NEW | jl41, vp25 | |
CR221 | 9/15/23 | Microform 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. | NEW | jl41, vp25 | |
CR222 | 7/7/23 | key_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. | slm5 | ||
CR223 | 9/28/23 | approval_plan_payments | This query finds invoice payments made on approval plans using approved invoices as a starting point and the criteria of the "bill to" locations equal to "LTS Approvals" to identify those payments. It also includes "order type" to show ongoing or one-time order lines and "workflow status" to show open and closed orders. | jl41, np55 | ||
CR224 | 9/28/23 | approval_plan_titles_by_vendor | This query finds approval plan titles associated with 5 vendors for which this connection is possible using the data available. The Vendor code comes from MARC record. It also uses the source of acquisition statistical code of "Approval/Blanket Order" to identify these titles. | np55, jl41 | ||
CR225 | 9/28/23 | approval_plan_titles_by_statistical_code | This query uses the source of acquisition statistical code of "Approval/Blanket Order" to identify titles associated with approval plans. This title list will be longer thank the one generated by the "approval_plan_by_titleecause statistical code is available for every title, but vendor is not | np55, jl41 |
...