/*CR193_filled_delivery_requests How used for ADC readme
*
* Output from query CR193 Filled Delivery Requests
* (https://github.com/cul-it/cul-folio-analytics/tree/main/canned_reports/CR193) is used to get Library-to-library Delivery
* and Contactless pickup couts by unit, and, for Ithaca, by patron group, for the annual data collection (ADC) as per below.
* The same query is used to create CUL's related Tableau dashboard (created/maintained by Vandana Shah).
*
* Find links to the dashboard and data set at https://confluence.cornell.edu/x/faTjGw .
*
* General filters used to get Library-to-library Delivery counts for the ADC:
* Filter fiscal_year_of_request to remove FYs not wanted
* Filter request_type for "Page" only
* Filter patron_group_name to exclude "SPEC (Library Dept Card)"
* (No need to filter pickup-type; includes both Circ Desk Pickup and Contactless Pickup)
* (This query automatically removes collection_type "BD*" and "ILL*")
*
* General fitlers used to get Contactless Pickup counts for the ADC:
* Filter fiscal_year_of_request to remove FYs not wanted
* Filter pickup_type to include only "Contactless pickup"
* Filter patron_group_name to exclude "SPEC (Library Dept Card)"
* (No need to filter request_type: is "Page" or "Recall" or "Hold")
* (This query automatically removes collection_type "BD*" and "ILL*")
*
* Location/user type grouping notes:
* Bailey Hortorium is counted with Mann
* Kroch Asia, No Library and RMC (RMC has been a very small count) are counted with Olin
* Faculty and Proxy Borrower counts are summed for Faculty
* Library Card and Priviledge card are summed for Library card
* BD, ILL and Carrel are summed for Other
*
* For those pulling data for mutliple or all units, using pivot tables in Excel is helpful. Pull the following measures
* to the following boxes in the Pivotchart Field box:
*
* For filled Library-to-library Delivery requests by owning library, and pickup location:
* Value box: number_of_requests (change from count to sum)
* Filter box: request_type; filter to only "Page"
* Filter box: fiscal_year_of_request; filter to remove FYs not wanted
* Filter box: patron_group_name; filter to exclude "SPEC (Library Dept Card)"
* (No need to filter pickup-type; includes both Circ Desk Pickup and Contactless Pickup)
* (this query automatically removes collection_type "BD*" and "ILL*")
* Axis (categories) box: owning_library
* Axis (categories) box: pickup_service_point_name
*
* For filled Library-to-library Delivery requests, by patron type (tracked at the Ithaca level only):
* Value box: number_of_requests (change from count to sum)
* Filter box: request_type; filter to only "Page"
* Filter box: fiscal_year_of_request; filter to remove FYs not wanted
* (No need to filter pickup-type; includes both Circ Desk Pickup and Contactless Pickup)
* (this query automatically removes collection_type "BD*" and "ILL*")
* Axis (categories) box: patron_group_name; filter to exclude "SPEC (Library Dept Card)"
*
* For filled Contactless Pickup requests, by owning library and pickup location:
* Value box: number_of_requests (change from count to sum)
* Filter box: fiscal_year_of_request; filter to remove FYs not wanted
* Filter box: pickup_type; filter to only "Contactless pickup"
* Filter box: patron_group_name; filtered to exclude "SPEC (Library Dept Card)"
* (No need to filter: request_type is "Page" or "Recall" or "Hold")
* (this query automatically removes collection_type "BD*" and "ILL*")
* Axis (categories) box: owning_library
* Axis (categories) box: pickup_service_point_name
*
* For filled Contactless Pickup requests by patron type (tracked at the Ithaca level only):
* Value box: number_of_requests (change from count to sum)
* Filter box: fiscal_year_of_request; filter to remove FYs not wanted
* Filter box: pickup_type; filter to only "Contactless pickup"
* (No need to filter: request_type is "Page" or "Recall" or "Hold")
* (this query automatically removes collection_type "BD*" and "ILL*"
* Axis (categories) box: patron_group_name; filter to exclude "SPEC (Library Dept Card)"
*
* National requests: there are no national requests for these particular counts.
*
*
* About the query:
* - Update the start and end dates each year.
* - This query pulls user names to exclude SPEC transactions that we are sure are internal processing transactions,though
* we still are not sure about those left over. Because of this, when we filter data for the ADC, we exclude all SPEC;
* this is consistent with past stats (SPEC was automatically excluded in past LTLD counts as they were tracked through
* Call Slip requests in Voyager,which excluded SPEC transactions). The query also filters out any unfilled requests, and
* any BD and ILL items from others institutions loaned to CU users.
* - The query uses both material_type_name and item_effective_location_name_at_check_out to identify BD and ILL materials:
* e.g., WHEN li.material_type_name ilike 'BD%' OR li.item_effective_location_name_at_check_out ILIKE 'Borr%' THEN 'Borrow Direct'
* - The query pulls pickup service point names and request types, so one can distinguish bewteen LTLD and CP. It converts
* pickup_service_point_names into either 'Contactless Pickup' or 'Circ Desk Pickup'
* - It makes use of item effective location; we are assuming there were no big changes within the year.
* - The Tableau dashboard limits records to those with the 'Request statuses' of: 'Closed - Filled,' 'Closed - Pickup expired,' 'Open - In transit,' and 'Open - Awaiting pickup.'
* About using pivot tables:
* - Don't hesitate to contact Linda Miller for help.
* - Add the measure to be counted to the Values box. Change how that data is processed by clicking on the measure and
* changing the value fields setting to "Sum".
* - Add measures you only want to filter by to the Filter box.
* - Add measures you want to slice and dice data by and in some cases filter by to the Axis (categories) box. (You can
* change the order of how data is sliced and diced by dragging measures in that box to higher or lower spots.)
* - If the Pivot fields selection box is not showing, click on the chart or the data table. If that does not work,
* right click on the chart or data table and click on Show field list.