/*CR184A_physical_collections_use How used for ADC readme
*
* Output from the query CR184A Physical_Collections_Use
* (https://github.com/cul-it/cul-folio-analytics/tree/main/canned_reports/CR184A) is used to get charge and
* renewal counts by unit and 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 counts for the ADC:
* Fitler fiscal_year to remove FYs not wanted
* Filter patron_group_name to exclude "SPEC (Library Dept Card)"
* Filter collection_type to exclude "BD*" and "ILL*"
* Note that counts of "General collections - Non-circulating, staff retrieved charges", which are currently not
* available in FOLIO, are manually added to the total sum of loans obtained from this report, so that the ADC
* general collections charges count will be larger than the count from this dataset. Contact A&P for more info.
*
* General filters used to get ILL/BD charges and rewewals of CUL materials for other institutions:
* Limit patron_group_name to ‘Borrow Direct’ and/or ‘Interlibrary Loan’
* Shouldn’t have to limit collection_type to ‘regular’
* Shouldn’t have to remove ‘BD MATERIAL’ AND ‘ILL MATERIAL’ from material_type_name
*
* 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 charges and renewals by collection type:
* Value box: total_loans (change from count to sum)
* Value box: total_renewals (change from count to sum)
* Filter box: fiscal_year; filter to remove FYs not wanted
* Filter box: patron_group_name; filter to exclude "SPEC (Library Dept Card)"
* Axis (categories) box: library_name
* Axis (categories) box: collection_type; filtered to exclude "BD*" and "ILL*"
*
* For charges and renewals by collection type and patron type:
* Value box: total_loans (change from count to sum)
* Value box: total_renewals (change from count to sum)
* Filter box: fiscal_year; filter to remove FYs not wanted
* Axis (categories) box: library_name
* Axis (categories) box: collection_type; fitler to exclude "BD*" or "ILL*"
* Axis (categories) box: patron_group_name; filter to exclude "SPEC (Library Dept Card)"
*
* National requests:
* ACRL/NCES: "Total physical circulation": Use the sum of Ithaca's counts for charges for General Collections and Physical
* Reserves. Includes the manually reported "General collections - Non-circulating, staff retrieved charges" counts from LibPAS.
* Includes ILL/BD loan use of CUL collections (despite the defintion; a note is made).
* ARL: "Initial Circulations (excludes reserves and equipment): Use the sum of Ithaca's counts for charges for General
* Collections. Includes the manually reported "General collections - Non-circulating, staff retrieved charges" counts from LibPAS.
* Includes ILL/BD loan use of CUL collections.
*
*
* About the query (created/maintained by Joanne Leary):
* - This query uses the derived table loans_items to get charges for the FY and the derived table
* loans_renewal_dates to get renewals made in the fiscal year no matter when the items were charged.
* It does some fancy footwork to put these two queries together so you can filter the fiscal year in one measure.
* - It makes use of the item effective location at check out.
* - 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'
*
* 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.
*/