...
This is a list of ad hoc reports used to retrieve data from the CUL FOLIO LDP reporting database system. Ad hoc reports are different from canned reports in that they are specialized, "one-off" reports. These reports are typically designed for specific units, purposes, etc.
All Ad-Hoc Reports | |||||
CUL Report Code and Link to SQL Query | Last Updated | Report Name | Description | Setup | |
---|---|---|---|---|---|
AHR100 | 12/15/21 | New and Noteworthy | This reports lists all 'New and Noteworthy' books (where the call number prefix ='New and Noteworthy'. NOTE: There are no filters for this report, dates are hard-coded. Adjust loan_date at end of query in order to run this query correctly. | jl41, vp25 | |
AHR101 | 12/15/21 | Nestle Inventory | List all inventory in Nestle that is non-book, such as laptops, equipment, etc. NOTE: This query can be adjusted for other libraries. There are no filters. Adjust library name at end of query. | jl41, vp25 | |
AHR102 | 12/17/21 | Collection growth over time | This query finds a specified collection (in this case, the QC and QD collection) and the record created date for all the items in the collection, so that the collection in that range can be analyzed. NOTE: The record created date is only available from the historical voyager records, and this field has to brought in as a local table and then matched to the Folio records. | jl41, vp25 | |
AHR103 | 4/27/22 | bd_with_annex | Script is looking for records that have note_type_name 'Bound with item data' and not have item record created for that holding. Then it matches on an item with a matching barcode. If there is a matching item record then it looks at the parent item permanent_location which should be an "annex" location | np55 | |
AHR104 | 4/27/22 | OKU materials by LC Class and year acquired | Finds the last five fiscal years (7/1/16 - 6/30/21) of newly acquired items in OKU (Voyager data). Groups by LC class, bib format, year acquired, number of volumes and total checkouts. | jl41 | |
AHR105 | 08/03/22 | ILR Kneel Collection clean up | Gets the data for certain collection and puts subjects by column. | ||
AHR106 | 6/8/22 | east_asia_lang_subject | This report provides a list of titles for east | asia Asia languages and subjects along with different formats. | np55 |
AHR107 | 7/29/22 | item_level_details_with_circ_count | This report provides item-level details as well as total circulation counts (Voyager and Folio) for items based on location, LC class, LC class number, or title filter. | jl41, vp25 | |
AHR108 | 7/29/22 | summary_circ_counts_with_subject_level_details | This report finds the summary of the number of volumes, the primary subject heading, pub date, year acquired and total circulation counts (Voyager and Folio) for all titles based on an LC class and/or location filter. | jl41, vp25 | |
AHR109 | 7/29/22 | mann_bee_collection_by_century | This query finds all items in two specific call number ranges related to the study of bees, located in Mann Library (all locations), Mann Annex or Geneva. It shows the century of publication. (Specialized ad-hoc report, created for a collection analysis project.) | jl41, vp25 | |
AHR110 | 7/29/22 | mann_bee_collection_group_and_sum | This query first finds all Mann Bee Collection items (see AHR109 for detailed description), then groups them by various fields (LC Class, Class Number, primary subject heading, century of publication, language, etc.) and counts the number of items in each group. | jl41, vp25 | |
AHR111 | 7/29/22 | voyager_purchases_by_fund_code | This query finds all Voyager purchases by fund code, order type and fiscal year. | jl41, vp25 | |
AHR112 | 12/16/22 | patron_purchase_requests_Folio | This report was created specifically for collection development needs and includes the following fields: requestor netid or other requestor information (where available), fiscal year of payment, title, instance hrid, subject, fund code, days until fulfilled, and total circ count in Folio. | jl41, vp25 | |
AHR113 | 12/16/22 | patron_purchase_requests_Voyager | This report was created specifically for collection development needs and includes the following fields: requestor netid or other requestor information (where available), fiscal year of payment, title, instance hrid, subject, fund code, days until fulfilled, and total circ count in Voyager. | jl41, vp25 | |
AHR114 | 1/6/23 | microforms_counts_details | This query was developed for the 2023 Olin Basement move project, and includes details of microform bibliographic information to help selectors make decisions about moving or de-accessioning the materials. | jl41, vp25 | |
AHR115 | 2/28/23 | olin_microforms_with_circ_counts | This is the second of three queries developed for the 2023 Olin Basement move project, and includes details of Olin and Kroch microforms, including bibliographic information and Voyager and Folio circ counts, to help selectors make decisions about moving or de-accessioning the materials. | jl41, vp25 | |
AHR116 | 2/28/23 | olin_microforms_with_circs_and_srs_marctab_fields | This is the third of three queries developed for the 2023 Olin Basement move project, and includes details of microform bibliographic information, srs marctab fields, and circ counts to help selectors make decisions about moving or de-accessioning the materials. | jl41, vp25 | |
AHR117 | 2/28/23 | olin_microforms_with_same_titles_in_other_libraries | This query gets all microforms in Olin Library and shows which other libraries have the same titles (in any format). It includes details of microform bibliographic information, and srs marctab fields. | jl41, vp25 | |
AHR118 | 3/1/23 | mismatched_locations_for_Annex | This query gets item records where the holdings location does not match the item location, and one of those locations is the Annex. | jl41, vp25 | |
AHR119 | 3/1/23 | patron_status_check_for_restricted_databases | This query checks patron status against list of users (supplied by a Library's staff member) for restricted databases. Shows department and patron group. | jl41, vp25 | |
AHR120 | 3/11/23 | vet_special_collections | This query finds books in the Vet Library that are located in special collections (companion animal hospital, anesthesia, etc.). Item details are included. | jl41, vp25 | |
AHR121 | 4/11/23 | holdings_records_with_bound_with_barcodes | This query looks for holdings records that have bound-with barcodes in the holdings notes field, and matches the barcodes from the notes field to a list of barcodes supplied by the Annex. Then it finds all the item records associated with the matched holdings records. | jl41, vp25 | |
AHR122 | 4/18/23 | Olin_childrens_lit_LC_class_PZ | This query finds Olin children's literature in LC class PZ and shows title, author, year of publication and circulation. | jl41, vp25 | |
AHR123 | 6/13/23 | Asia items difficult to replace | This query finds all items in Kroch Library Asia locations and shows the OCLC number, year of publication and item status. | jl41, vp25 | |
AHR124 | 6/13/23 | Olin K monographs with no holdings at the Annex | This query gets Olin Library K call number monographs, where there are no copies at the Annex. | jl41, vp25 | |
AHR125 | 6/13/23 | Mann S 599 call numbers with circs since 2015 | This query finds Mann S 599 items for possible transfer to the Annex. | jl41, vp25 | |
AHR126 | 6/13/23 | ILR Inventory | This is an inventory query that finds all items in call number range HD1 - HD30 at ILR | jl41, vp25 | |
AHR127 | 6/22/23 | Management and Hotel peripherals and supplies | This is an inventory query that finds Management and Hotel (Nestle) library items that show a material type of “Peripherals” or “Supplies”, and includes item status. | jl41, vp25 | |
AHR128 | 7/11/23 | Olin and Kroch microform guides | This query finds microform guides in Olin and Kroch libraries and shows additional copies in the stacks or at the Annex. (A guide is a paper publication that lists the microforms in a set and shows descriptive information.)
| jl41, vp25 | |
AHR129 | 7/11/23 | Voyager and Folio circ counts for specified titles | This query finds Voyager and Folio circulation counts for two titles in Olin. The information will be used to inform a cancellation decision (requested by Susette Newberry). | jl41, vp25 | |
AHR130 | 7/13/23 | Video Records in Africana | This query finds video records in the Africana library. Requested by Cammie Wyckoff for record cleanup. | jl41, vp25 | |
AHR131 | 7/25/23 | Folio patrons matched to Lexis users | This query finds Folio patrons matched by first and last name to a file of Lexis users. (requested by Suzanne Cohen, ILR). Custom field elements for College and Department are included in the results in order to help identify the correct user in the case of multiple matches in Folio.
| jl41, vp25 | |
AHR132 | 8/15/23 | No Library Records | This query finds records that have an owning library of "No Library" or a holdings or item location of "No Library". This will be used by the Annex for record cleanup. | jl41, vp25 | |
AHR133 | 8/16/23 | Median Year of Publication for Print Monographs | This query finds the median year of publication for the print monograph collection. First the query identifies the set of records that met the criteria for print monograph (and that had an actual numeric publication date), then it applies the expression for median value. Requested by Adam Chandler. * | jl41, vp25 | |
AHR134 | 8/31/23 | music_mt_candidates_for _transfer_to_the _annex | This query finds music mt candidate for possible transfer to the annex | jl41 | |
AHR135 | 10/5/23 | Faculty Development in Education collection, circ usage - 2019-2023 | This query finds the circ usage since 2019 for the Faculty Development in Education collection at the Vet Library (from supplied barcodes). | jl41, vp25 | |
AHR136 | 11/8/23 | cornell_tech_requests_by_fiscal year | This query finds filled requests to Cornell Tech by fiscal year, calendar year and month, and ownership (Cornell or Borrow Direct). | jl41 | |
AHR137 | 12/8/23 | mann_print_oa_electronic_titles | This query finds Mann items in call numbers A - E for possible transfer to the Annex. It is also used for record cleanup. | jl41 | |
AHR138 | 3/12/24 | Adelson_and_Adelson_Annex_checkouts_by_patron_group_and_fiscal_year | This query finds Adelson and Adelson Annex checkouts by patron group and fiscal year (no aggregation) | jl41 | |
AHR139 | 3/12/24 | asia_collections_with_umi_dissertation_services_in_descriptive_fields | This query gets bibliographic detail and circ and browse counts for Kroch Asia Collections and Asia Annex (Jeff Peterson request. It also finds a subset of asia materials that are dissertation copies and identifies Ann Arbor, UMI dissertation services notes. | jl41 | |
AHR140 | 3/12/24 | asia_collections_with_selected_publishers_and_circ_counts | This query finds Asia collections items for three publishers: Lap Lambert, VDM and Scholar's Press and shows the Voyager and Folio circs and browses. | jl41 | |
AHR141 | 4/16/24 | FAL print monographs | This query finds all print monographs in FAL stacks by LC Class and LC Class Number. It groups the books by year added to collection and years circulated. | jl41 | |
AHR142 | 4/16/24 | laptop_loans_by_academic_year_and_month | This query finds laptop loans by library and Academic Year, and breaks it down by month, type of loan (hourly or extended loan) and type of laptop (Dell or Mac). The academic year runs from August 1 through May 31. The query compares the most recent two years; Nestle library is excluded. | jl41 | |
AHR143 | 5/8/24 | Vet library barcodes matched to Folio | This query finds matches to Folio data from a list of Vet library barcodes. The purpose is to update locations for special collections items. | jl41 | |
AHR144 | 5/15/24 | Annex items with lost or damaged item notes | This query finds Annex items with item status "Available" and item notes indicating the item was lost, billed for replacement or otherwise damaged. Query requested for Annex cleanup. | jl41 | |
AHR145 | 5/31/24 | WorldCat_OCLC_numbers_matched_to_catalog | This query finds catalog matches to a list of WorldCat OCLC numbers, and finds Folio circulation totals by patron group. For checkouts after 2-9-23, shows the patron department and college (when that data existed in the patron record). | jl41 | |
AHR146 | 5/31/24 | Mann_Special_Collections_inventory_showing_holdings_in_other_locations | This query shows Mann special collections inventory with holdings in other locations. | jl41 |