*** 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 Nov 13, 2024
7 minute read
Canned report SQL queries written to run on the Metadb reporting database
All Reports
All Reports | ||||||
| Report Code and Link to Query | Functional Area | Priority | Last Updated or Target Date | Report Name | Description | Assignment |
|---|---|---|---|---|---|---|
| MCR106 | Access Services | High | missing_or_lost_items | creates a list of missing or lost items at a given library | jl41 | |
| MCR107 | Access Services | High | 6/24/24 | patron_requests_by_type_and_status | list of patron requests within a specified date range by request type and status | jl41 |
| MCR108 | Access Services | High | 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 |
| 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' | ||
| 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. | jl41 | |
| 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 | High | 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 | High | 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 |
| MCR132 | Accounting | High | 8/12/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. | slm5, ama8 |
| MCR134 | Collection Management | High | 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. | |
| 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. | ||
| 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. | ||
| 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. | ||
| MCR171 | Collection Development | High | 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 | High | 8/9/24 | 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 | high | 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 | high | 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. | ||
| MCR183 | Access Services | High | 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 | High | 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 | High | 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 | High | 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 | High | 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 |
| MCR192 | Annual data collection | High | 11/12/24 | volumes_withdrawn_or_transferred | 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. | |
| MCR193 | Access Services | High | 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 |
| MCR195 | Accounting | High | 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 | High | 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 | High | 6/7/24 | 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 |
| 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. | ||
| MCR214 | Annual Data Collection | High | 6/4/24 | physical_item_counts | This query provides item counts of physical materials, by format type. It excludes microforms (counted separately). | lm15 |
| MCR215 | Annual Data Collection | High | 6/4/24 | 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 | High | 6/4/24 | microform_counts | This query provides counts of microform titles, by format type. | lm15 |
| MCR217 | Annual Data Collection | High | 6/4/24 | 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 |
| 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. | ||
| 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. | ||
| MCR231 | Acquisitions | 3/2024 | lts_receiving | TS Acquisition Statistic Dashboard, LTS Receiving Story 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 location | ||
| MCR232 | Acquisitions | High | 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 | high | 3/12/24 | adc_location_translation_table | CR233 and MCR233 is used to help update the lm_adc_location_translation_table. It pulls location code, location name, and shelving location names data from FOLIO’s inventory_locations and inventory_libraries tables, and corresponding data from the lm_adc_location_translation table. In Excel, one can then ensure the translation table data matches that from the FOLIO tables. | lm15, jl41 |
| MCR238 | ? | ? | 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 | High | 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 | High | 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. | ||
| MCR500 | Accounting | High | previous_day_appr_vouchers | This dashboard query creates a list of vouchers approved the previous day. Used in FBO Daily Reports dashboard | ||
| MCR501 | ||||||