*** This version of Confluence is for testing only and contains a copy of content from June 29th 2026. No changes will be preserved. ***
-
Created by
Sharon Helen Markus, last updated by Vandana Suresh Shah on Jun 29, 2026
14 minute read
The following list shows the canned report SQL queries written to run on the Metadb reporting database created by the CUL FOLIO Core Reporting Team.
PLEASE NOTE: Click on the column headings to sort the listings. For instance, you might want to sort by functional area. This might help you find what you are looking for faster.
| Link to Query | Functional Area | Last Updated | Name | Description | Assignment |
|---|---|---|---|---|---|
| MCR103 | Collection Management | 12/6/24 | annex_items_with_nonCirculating_loan_types | This query creates a list of 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 |
| MCR107 | Access Services | 6/24/24 | patron_requests_by_type_and_status | This query creates a list of patron requests within a specified date range by request type and status | jl41 |
| MCR108 | Access Services | 5/22/24 | services_usage | This query provides the number of circulation transactions by service-point and transaction type, with time aggregated to date, day of week, and hour of day. | jl41 |
| MCR113Y | Accounting | 2/19/24 | daily_approved_invoice_exported_yesterday | This query provides the total amount of voucher_lines per account number and per approval date for transactions exported to accounting. The invoice status is hardcoded as 'Paid'. | |
| MCR113 | Accounting | 2/19/24 | daily_approved_invoice_exported | This query provides the total amount of voucher_lines per account number and per approval date for transactions exported to accounting. The invoice status is hardcoded as 'Paid'. | |
| MCR114Y | Accounting | 5/6/24 | daily_appr_inv_vendor_yesterday | This query provide the list of invoices paid by vendor along with voucher lines details. | |
| MCR114 | Accounting | 5/6/24 | daily_appr_inv_vendor | This query provide the list of invoices paid by vendor along with voucher lines details. | |
| MCR116 | Accounting | 2/2024 | payable_inv_not_fed_notes | This query provides the total amount of voucher lines not sent to accounting (manuals) per account number with notes | |
| MCR118 | Access Services | 6/7/24 | missing_in_transit | This query finds items that are still in transit after 10 days and shows requesters and designated pickup locations. | jl41 |
| MCR120Y | Accounting | 4/2024 | daily_appr_inv_control_yesterday | This query provides the total amount of voucher_lines per external account number along with the approval dates. It includes manuals and transactions sent to accounting. The invoice status is hardcoded as 'Paid'.*/ | |
| MCR120 | Accounting | 4/2024 | daily_appr_inv_control | This query provides the total amount of voucher_lines per external account number along with the approval dates. It includes manuals and transactions sent to accounting. The invoice status is hardcoded as 'Paid'.*/ | |
| MCR122 | Accounting | 7/25/24 | fund_details_summary | This query provides fund details summaries for active funds | |
| MCR126 | Access Services | 5/23/24 | borrow_direct_interlibrary_loan_recalls | This query finds books that are on loan to BD and ILL, were recalled and are now overdue | jl41 |
| MCR127 | Access Services | 5/23/24 | Borrow_direct_interlibrary_loan_overdue_items | Provides a list of items owned by CUL and borrowed by BD/ILL patrons, that are overdue. | jl41 |
| MCR132Y | Accounting | 11/18/24 | ytd_acct_bal_by_ledger_univ_acct_yesterday | 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. The "yesterday" version of the query pulls yesterday's data. | slm5, ama8 |
| MCR132 | Accounting | 11/18/24 | 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. The "current" version of the report can be used to get the most current data in the reporting database. | slm5, ama8 |
| MCR134 | Collection Development | 7/25/24 | approved_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, fund type, expense class, LC classification, LC class, LC class number, and bibliographic format. | |
| MCR134F | Accounting | 1/24/25 | approved_invoices_for_fbo | This is a version of the MCR134 approved invoices query using by accounting that has been modified to exclude the bibliographic data. Accounting approves invoices Mondays through Fridays, so it is best to run this query on Fridays if you want it to match the data in the FOLIO financial applications. | |
| MCR135 | Accounting | 5/8/24 | sum_appr_inv_ledger_scct | This query provides the total of transaction amount by account number along with the finance ledger and finance group within a date range. | |
| MCR139Y | Accounting | 1/21/25 | inv_appr_paid_diff_date_yesterday | 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. | slm5, ama8 |
| MCR139 | Accounting | 1/21/25 | 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. | slm5, ama8 |
| MCR140 | Accounting | 1/21/25 | 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. | slm5,ama8 |
| MCR141 | Access Services | 11/13/24 | lost_bursared_returned | This query 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. | |
| MCR150 | Access Services | 12/18/24 | Physical and E-reserve statistics | This query shows the physical checkouts and online clicks for items on reserve at all libraries, for the semester indicated. | |
| MCR154 | Cataloging | 1/21/25 | titles_held_by_loc_format_language_subject | This query shows titles held with format, languagge and subject info. Select a location. Also filter by language code, bibliographic format, and subject word. | |
| MCR157 | Accounting | 10/2/24 | funds_and_teams_with_expense_class | This query provides a detailed current date report of funds and teams with amounts spent, encumbered, and remaining. | lm15, np55 |
| MCR165 | Accounting | 7/25/24 | funds_and_teams | This query provides a current date report of funds and teams with amounts spent, encumbered, and remaining. | |
| MCR170 | Collection Management | 12/6/24 | item_status_in_process | This query finds In Process item status books that appear to be fully cataloged and should be checked in the stacks to see if they have arrived at the library without the status being updated (record cleanup). | |
| MCR171 | Collection Development | 7/15/24 | BD_ILL_loans_to_Cornell_patrons | Finds the items borrowed through ILL and BD, shows the patron group and dept when available, and shows the number of days on loan. Lists title, patron group and department (where available) for items borrowed from other universities on Borrow Direct and Interlibrary Loan. | jl41 |
| MCR172 | Collection Development | 5/22/25 | ILL_BD_items_lent_to_others | This query provides a list of items owned by CUL which have been loaned to other universities on BD and ILL. | jl41 |
| MCR174A | Access Services | 6/24/24 | ILL_BD_counts_loaned_to_others | BD/ILL loans and renewals counts of items loaned BY CUL to others (CUL is LENDER) | jl41 |
| MCR174B | Access Services | 6/24/24 | ILL_BD_counts_borrowed_by_CUL | ILL and BD counts of items where CUL is the BORROWER | jl41 |
| MCR175 | Accounting | 7/25/24 | split_funds | This query shows split fund payments for all finance groups. | |
| MCR176 | Collection Management | 12/6/24 | annex_items_to_be_accessioned | This query is used by the Annex staff for checking lists of items to be accessioned against holdings already at the Annex, to prevent duplication | |
| MCR180 | Annual Data Collection | 1/15/25 | 773_and_899_aggr_counts_adc | For the annual data collection, this query get counts of e-aggregators and other e-collections coded in 773 and 899 fields, to help track large changes in e-counts from year to year if needed. It merges the 773 and 899 counts, and sorts the counts in descending order. Excludes PDA/DDA titles unpurchased. | lm15, np55 |
| MCR181 | Collection Development | 12/6/24 | circ_counts_by_instanceHRID_for_Voyager_and_Folio | 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. | |
| MCR183 | Access Services | 6/7/24 | laptop_circ_counts | This query counts laptop circs and renewals by library, date, loan type, and laptop type (Mac or PC). Also counts how many laptops were used on any given day. | jl41 |
| MCR184 | Access Services | 6/24/24 | loans_and_renewals | Loans and Renewals by fiscal year. This query uses the loans_renewal_dates from local_shared. Change to folio_derived once the derived table is fixed. | jl41 |
| MCR184B | Annual Data Collection | 9/12/24 | orig_locs_for_phys_collections_no_library_charges | For counts in MCR184 with locations as "removed locations" or "no library," use this report to see what the locations now retired originally were. | lm15, jl41 |
| MCR185 | Collection Management | 6/10/24 | Ceased_cancelled_serials_at_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. Results will include any title (of any holdings type) that has a holdings receipt status or holdings note that indicates the title is no longer received or is ceased or cancelled. | jl41 |
| MCR186 | Collection Management | 6/10/24 | ceased_cancelled_serials_at_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. Results will include any title (of any holdings type) that has a holdings receipt status or holdings note that indicates the title is no longer received or is ceased or cancelled. | jl41 |
| MCR190 | Access Services | 11/19/24 | expired_patrons_with_open_fines | This query finds expired patrons with open fines. This query references the CIT file of patrons, Please check which schema this file resides in and make changes as needed to the schema name. | jl41 |
| MCR192 | Annual data collection | 11/12/24 | LEGACY volumes_withdrawn_or_transferred See MCR422 for latest version | This query extracts holdings administrative note data to allow counts of physical items withdrawn by location AND to allow the identification of transfers that go from endowed to contract units, and vice-versa. These counts are used for volumes withdrawn figures needed by the Division of Financial Services each quarter. | jl41 |
| MCR193 | Access Services | 5/8/24 | 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. | jl41 |
| MCR194 | Access Services | 1/29/25 | checkouts_and_checkins_by_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 null. Because most deleted records are equipment records, these items have been categorized as "Equipment" collection type as a best guess. | |
| MCR195 | Accounting | 7/29/24 | 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. | |
| MCR199 | Collection Management | 11/1/24 | serials_by_owning_library_and_annex_holdings | This query gets serials by owning library and LC class, and shows holdings at the Annex. | jl41, lm15 |
| MCR202 | Collection Management | 7/16/24 | patron_purchase_requests | This query finds purchase requests by fund code, location, library or fiscal year and shows requester information, number of days from request date to receipt date, and number of loans for that title (cannot get circulation information at item level) | |
| MCR204 | Access Services | 1/06/25 | missing_lost_claimed_returned | This query finds items whose status is missing, lost, or claimed returned. Changes from the LDP query: no Voyager data included. Also, extracted the 300 field separately and the most recent discharge and discharge location are from the from folio_inventory.item__ json blob; repositioned library filter to end of query. | jl41 |
| MCR205 | Access Services | 12/4/24 | libraries_locations_service_points_feefine_owners | This query finds libraries, locations, service points and fine owners associated with the service points. | jl41 |
| MCR206 | Access Services | 12/18/24 | Open fines older than X days | This query finds open (unpaid) fines that are older than the number of days specified; can also choose an owning libdrary | |
| MCR207 | Accounting | 2/6/25 | po_lines_no_expense_class | This query is used to get a listing of purchase order lines with no expense class selected when the PO was created. It includes fund code, purchase order line number, workflow status, order type, order format, and title. This is pulling purchase order line information that is not tied to a transaction. | slm5 |
| MCR208 | Collection Management | 12/19/24 | Identifying DVDs | This query lists the DVDs in a library's collection | |
| MCR209 | Collection Management | 12/19/24 | Identifying VHS | This query lists the VHS tapes ina library's collection | |
| MCR210 | Collection Development | 11/13/23 | missing_lost_items_for_selectors | This query is specifically for selectors and it shows Missing and Lost items with different fields included that are useful in making replacement decisions. | jl41 |
| MCR211 | Collection Management | 12/20/24 | 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. | |
| MCR212 | Collection Management | 12/20/24 | 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. | |
| MCR213 | Finance | 7/9/25 | Current encumbrances | This query finds the current encumbrances by fiscal year, fund or finance group | |
| MCR214 | Annual Data Collection | 6/4/24 | LEGACY query physical_item_counts See MCR418-420 for new versions of counts queries | This query provides item counts of physical materials, by format type. It excludes microforms (counted separately). | lm15 |
| MCR214B | Annual Data Collecction | 6/10/25 | LEGACY physical_item_counts_incl_items_rec_but_not_yet_cataloged | With FY25, A&P uses this query every quarter to get volumes added counts for Cornell's Division of Financial Services. | |
| MCR215 | Annual Data Collection | 6/4/24 | LEGACY instance_counts | This query provides counts of unique titles (instances) of physical items, by format type. It excludes microforms (counted separately). | lm15 |
| MCR216 | Annual Data Collection | 6/4/24 | LEGACY microform_counts | This query provides counts of microform titles, by format type. | lm15 |
| MCR217 | Annual Data Collection | 6/4/24 | LEGACY ematerial_counts | This query provides counts of ematerials, by format type. Formats are taken from 948 field, and if missing then taken from the stat code, and if both these fields are missing, formats are taken from the MARC format code. Titles with multiple codes are assinged based on the priority listed in the CASE clause. | lm15 |
| MCR217B | Annual Data Collection | 6/10/25 | HathiTrust_title_counts_to_add_to_ADC_e-counts | This query is used by A&P staff to get counts of titles CUL sent to Google to be digitized in one of the Google Books Projects, that are now fully accessible to all Cornell users. | lm15/vp25 |
| MCR218 | Access Services | 12/18/24 | checkouts_and_browses | This query finds the the most recent checkouts and browses by date range, location and LC class (total checkouts and most recent checkout in Voyager and in Folio). | |
| MCR219A | Collection Management | 11/21/24 | shelf_list_inventory_full | This query finds shelf list inventory information by library location. The full MCR219A version has more data fields than the brief MCR219B version, which is used for manual shelf list inventory work. | |
| MCR219B | Collection Management | 11/21/24 | shelf_list_inventory_brief | This query finds shelf list inventory information by library location. The full MCR219A version has more data fields than the brief MCR219B version, which is used for manual shelf list inventory work. | |
| MCR220 | Collection Development | 1/14/25 | 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. | |
| MCR222 | Troubleshooting | 8/29/24 | metadb_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 Metadb database. | slm5, jl41 |
| MCR223 | Acquisitions | 1/29/24 | approvals_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. | |
| MCR225 | Collection Development | 12/9/24 | purchase_requests_with_days_elapsed_till_checkout | This query finds the number of days between when a firm-ordered (one-time) fully-received item was ordered (item record created), then received at LTS, then received at the unit library and then first checked out (Folio only). If item was not discharged at the unit library upon receipt, or item did not have an "In process" status prior to discharge, time elapsed will show null. Includes bibliographic info, order info and fund information and can be limited to just requested purchases. | jl41 |
| MCR226 | Alumni Affairs | 11/6/24 | funds_for_stewardship | This query was modified from CR134 to accommodate alumni affairs needs for information on stewarded funds. It provides the list of approved invoices within a date range along with primary contributor name, publisher name, publication date, publication place, vendor name, LC classification, LC class, LC class number, finance group name, vendor invoice number, fund details, purchase order details, language, instance subject, fund type, and expense class. | |
| MCR229 | Collection Management | 11/19/24 | inventory_by_call_number_range | This query gets the holdings-level inventory for a library/location/call number range and shows item count and circ count since 2015. | jl41 |
| MCR230 | Collection Management | 11/19/24 | collection_inventory_by_date_range | This query finds all items in a given location or library, and shows all charges and browses within the specified date range. It Shows all record component locations, which is helpful for record cleanup. | jl41 |
| MCR231 | Acquisitions | 3/2024 | lts_receiving | TS Acquisition Statistic Dashboard, LTS Receiving StoryThis 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 location | |
| MCR232 | Acquisitions | 3/8/24 | inst_updated_date | This query pulls the updated_by_userid and the updated_date field data from the inventory instance record JSONB data array. This data is then joined to the MARC 245/a field to get the instance title and instance HRID. | np55 |
| MCR233 | Annual Data Collection | 3/12/24 | locations_libraries | Creates a table in local_static consisting of locations and corresponding libraries, whether contract or endowed unit, location status (active or inactive), and whether or not to include item and instance counts from each location for annual data collection purposes. | lm15, jl41 |
| MCR238 | Cataloging | 4/10/24 | get_instance_records | This report uses metadb function and gets MARC record field content for a particular subfield. After function is created it can be called by using provided call statement example at the end of the report. | np55 |
| MCR243 | Troubleshooting | 8/27/24 | metadb_key_created_counts_date_series | This query counts holdings, instances, items, and srs_records, then displays each set of counts in a date series. | slm5 |
| MCR400 | Access Services | 2/12/24 | calendar_settings_by_service_point_and_semester | This query displays the calendar settings for a given service point and semester. Service points using a "Universal" calendar. | jl41 |
| MCR401 | Batch | 4/10/24 | clean_up_100e | This report gets 100 field subfiled "e" value to check for the wrongly entered entries of the subfield. | np55 |
| MCR402 | Annual Data Collection | 6/12/24 | loan_policies_used_in_laptop_charges | This query can be used for the annual data collection to see which loan policy types units used for laptop charges, how frequently. | lm15, jl41 |
| MCR403 | Access Services | create_circ_snapshot | Creates the initial snapshot of loans, capturing patron demographics for each circ tranasaction | ||
| MCR404 | Access Services | 6/27/24 | update_circ_snapshot | This query creates the "insert into" portion of the circ_snapshot4 query. Updates the file created by MCR403. | |
| MCR405 | Access Services | 11/8/24 | items_with_content_by_MARC_field | This query finds items within a specified MARC field with a specified entry in the content field on the MARC table. | slm5 |
| MCR406 | Hathitrust | 12/3/24 | hathitrust_903_mappings | This query creates a table of mapping for instances with 903 values and publishes it to the local_digpres schema. It is automated to run daily at 5am. | slm5 |
| MCR407 | Annual Data Collection | 1/8/25 | Fine_Arts_ser_curr_rec_NAAB | This query estimates the number of physical serial titles currently received in the Mui Ho Fine Arts Library. It used two queries that search via purchase order and via holding records notes, combines instance HRIDs, and then dedupes. Used in fall of 2024 for NAAB reporting. | jl41, lm15 |
| MCR408 | Collection Management | 1/24/25 | annex_in_transit | This query finds items accessioned at the Annex with an "In transit" status, using an imported file of barcodes. | |
| MCR409 | Access Services | 9/9/25 | Active Patrons in Cornell Tech | This query finds all active patrons in Cornell Tech. | |
| MCR410 | Collection Management | 10/7/25 | Annex transfer candidates | This query finds candidates for transferring to the Annex by location, LC class, material type, holdings type, mode of issuance, size, circ history, pub date, year added to collection; excludes bound-withs and titles with holdings at the Annex; "Available" item status only | |
| MCR411 | Collection Management | 10/8/25 | Print serials with max payment date | This query uses an input list of PO numbers providedby the report user to find serials that we get in print. | |
| MCR412 | Collection Management | 10/8/25 | Print serials and cost by FY based on an input list of PO numbers | This query gets the payments for print serials (based on an input list of PO numbers) and shows finance group, fund, LC class, location. | |
| MCR413 | Collection Management | 11/10/25 | MARC field call number string match | This query retrieves MARC field data that matches items with a particular text string in the call number | |
| MCR414 | Collection Management | 2/9/26 | materials_circulated_in_folio | This query finds all item records created after a given start date at a given library, and shows circ counts by year circulated (Folio loans only). | jl41 |
| MCR415A | Access Services | 2/26/26 | missing_items_2_years_circ_desk_report | Long Missing Process query. List of items missing before two years ago to be flipped to Long missing. | jl41 |
| MCR415C | Access Services | 12/5/25 | long_missing_report_for_selectors_missing_status_only | Long Missing Process query. Based on MCR210 (which looks at many items statuses) but is revised to look only at "Long Missing" and uses derived tables rather than source table extracts. | jl41 |
| MCR416 | Access Services | 3/17/26 | loan_id_item_id_hist_bib_search | This query takes a loan_id and item_id as inputs and returns the matching bibliographic and location data for that loaned item. This is useful when the item no longer appears in current source or derived item tables, but can still be traced through historical inventory tables. | slm5 |
| MCR417 | 3/24/26 | item_count_for_insurance | Physical Items Count for University Insurance Purposes | ||
| MCR418 | Annual Data Collection | 6/22/26 | materials_counts_(1) | This query is the first of four queries used for annual materials count reporting. It creates the underlying data preparation table that serves as the foundation for subsequent materials count processing and reporting. | |
| MCR419 | Annual Data Collection | 6/22/26 | materials_counts_(2) | This query is the second of four queries used for annual materials count reporting. It applies format classification logic to the data preparation table created by MCR 418 and assigns one or more material formats to each record. | |
| MCR420 | Annual Data Collection | 6/22/26 | materials_counts_(3) | This query is the third of four queries used for annual materials count reporting. It creates a flattened version of the primary formats table generated by MCR 419. | |
| MCR421 | Annual Data Collection | 6/22/26 | materials_counts_(4) | This query is the final step in the materials count reporting process. It produces report-ready materials counts using the flattened formats table created by MCR 420. | |
| MCR422 | Annual Data Collection | 6/29/26 | withdrawn_transferred_with_finGroups | This query gets counts of withdrawn and transferred items by location, library, and college financial group (endowed or contract) for reporting to the Division of Financial Affairs. |