...
Access Services reports | ||||||
CUL Report Code and Link to 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 | |
CR102 | title_count | 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_items | creates a list of missing items, and includes all item details. | REP-152 | 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 | 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 | ||
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_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 | patron_list | List of active patrons including patron details and group type (faculty, undergrads, etc.) | REP-220 | vp25 | ||
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 | |
CR126 | 12/09/21 | Borrow_direct_interlibrary_loan_recalls | Provides a list of items owned by CUL and borrowed by BD/ILL patrons, that have been recalled. | REP-272 | vp25 | |
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 | ||
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 | |
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 | 02/02/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 cannot be run unless data from ARES are imported into the LDP. | 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 | |
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 | 02/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. | New | jl41, vp25 | |
CR158 | 02/18/22 | Circ_stats_1_checkouts | Checkouts of the last month by library, patron group, and material type. | New | jl41, vp25 | |
CR159 | 02/18/22 | 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 | |
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 |
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 | |
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 | |
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 | |
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 | |
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 |
Accounting Reports
Accounting Reports | ||||||
CUL Report Code and Link to Query | Last Updated | Report Name | Description | ~Time to run | Related JIRA REP Issues | Setup |
---|---|---|---|---|---|---|
CR101 | 07/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 | |
CR112 | daily_appr_inv_manuals | used by Accounting for Endowed Daily Approve Invoice by Condition Flag - Manual Check | REP-10 | nb299 | ||
CR113 | daily_appr_inv_exported | used by Accounting for Endowed Daily Approve Invoice by Condition Flag - Exported Check | REP-10 | nb299 | ||
CR114 | 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 | 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 | |
CR120 | daily_appr_inv_control | summary of vouchers by account number, including exported and manuals | REP-10 | 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 fund details summary for active funds | REP-98 | nb299 | |
CR123 | 7/14/22 | open_orders | provides a list of open purchase orders and their encumbrance and/or amount paid, broken down by purchase order lines | REP-283 | nb299 | |
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 | |
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 | |
CR132 | 01/24/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. | |||
CR134 | 9/3/22 | appr_invoices_bib_data | This 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-287 | nb299 | |
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 | 10/4/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 teh external account, vendor name, invoice number, and transaction amount. | 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 | ||
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 | ||
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 | |
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 | |
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 |
CR195 | 11/14/22 | 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 |
Acquisitions Reports
Acquisitions Reports | ||||||
CUL Report Code and Link to Query | Last Updated | Report Name | Description | ~Time to run | Related JIRA REP Issues | Setup |
---|---|---|---|---|---|---|
CR109 | standing order purchase orders | daily report run by acquisitions staff to review firm purchase orders against standing purchase orders | REP-283 | np55 | ||
CR111 | 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 | ||
CR123 | 9/22/21 | open_orders | provides a list of open purchase orders and their encumbrance and/or amount paid, broken down by purchase order lines | REP-283 | nb299 | |
CR134 | 10/26/2021 | appr_invoices_bib_data | This 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-287 | nb299 | |
CR195 | 11/14/22 | 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 |
...