[Evergreen-reports] Cancelled Holds Report Template

Tue Dec 29 10:58:38 EST 2020

Hi Beth,

I've got several SQL queries that compare columns like your example that
I've tried to translate into the reporter, and I have found the answer to
be "no, but..." The only way to get around it is to figure out how to
reframe the comparison to use an actual value in the filter, which tends to
be either terrifically specific or less accurate. For example, I have a
report that looks at hold transits filtered to exclude transactions where
the hold fulfillment library matches the copy owning/circ library - in the
reporter I would basically need a separate template for each library (ugh).

For your report, are you trying to find hold requests that were cancelled
after being made available, or hold items that are still on a hold shelf
after the hold request was cancelled?

If the former, Rogan's suggestion to compare capture/shelf time and cancel
time, using just the hold request source would be the most direct, and also
sort of achievable in the reporter. If you use the filters "shelf time less
than" with a specific date (or "shelf time is not null" returns similar
results) and "cancel time between", that will return a mostly accurate list
of holds cancelled after they were made available. I also suggest adding
fulfillment time is null (if you use the related items can fulfill holds
setting). Adding the copy status info would skew your results by
eliminating items that had subsequent holds/circulation.

If the latter, filtering on copy status 8 and cancel time between (or is
not null) should be sufficient. I'm not sure you need to match the shelf
time to the copy status changed time.

Good luck!

Lindsay

*Lindsay Stratton*
*Systems Librarian*
Westchester Library System
570 Taxter Rd., 4th Floor
Elmsford, NY 10523
lstratton at wlsmail.org

On Mon, Dec 21, 2020 at 10:43 AM Willis, Beth <willis at noblenet.org> wrote:

> Thanks, Terran.
>
> I should have been more specific in my initial post.   And, the query I
> want to run might have helped (I have pasted it below).  It is the last
> filter that is tripping me up.  Is it possible to translate that into
> reporter-speak?
>
> Thanks!
> Beth
>
>
> SELECT
>
> ahr.shelf_time as ShelfTime,
> ac.status_changed_time as StatusChange,
>
> date(ahr.cancel_time) AS HoldCancelDate,
>
> ahr.id as hold_id,
> ahr.current_copy,
> ac.barcode AS Barcode,
> st.name,
>
> sr.title AS Title,
> au.first_given_name AS FirstName,
> au.family_name AS LastName
>
> FROM action.hold_request ahr
> JOIN reporter.hold_request_record  rhr ON ahr.id = rhr.id
> JOIN biblio.record_entry re ON rhr.bib_record = re.id
> LEFT OUTER JOIN reporter.materialized_simple_record sr  ON re.id = sr.id
>
> INNER JOIN actor.usr au ON ahr.usr = au.id
>
> LEFT OUTER JOIN action.hold_copy_map hcm ON ahr.id = hcm.hold
> JOIN asset.copy ac ON ahr.current_copy = ac.id
> JOIN config.copy_status st on ac.status = st.id
>
> WHERE
> date(ahr.cancel_time) BETWEEN '2020-12-01' AND '2020-12-17'
> AND ahr.pickup_lib IN (46)
> AND date(ahr.capture_time) is not NULL
> and ac.status = 8
> and ahr.shelf_time = ac.status_changed_time
>
> On Mon, Dec 21, 2020 at 9:49 AM Terran McCanna <
> tmccanna at georgialibraries.org> wrote:
>
>> It sounds like you are looking for something like this one which we used
>> to find holds that were captured (pulled off the shelf) but not fulfilled
>> (not picked up):
>>
>> -----------------------------------------
>> Uses Hold Request source.
>>
>> Displayed Fields:
>>
>> Hold Request -> Pickup Library -> Short (Policy) Name as "Pickup Library"
>> Hold Request -> Hold Type
>> Hold Request -> Request Date/Time
>> Hold Request -> Capture Date/Time
>> Hold Request -> Bib Record Link -> Bib Record -> Simple Record Extracts -> Title Proper (normalized) as "Title"
>> Hold Request -> Bib Record Link -> Bib Record -> Simple Record Extracts -> Author (normalized) as "Author"
>> Hold Request -> Hold User -> Current Library Card -> Barcode as "Patron Barcode"
>> Hold Request -> Currently Frozen
>> Hold Request -> Thaw Date (if frozen)
>> Hold Request -> Hold Expire Date/Time
>>
>> Base Filters:
>>
>> Hold Request -> Pickup Library -> Organizational Unit ID (In list)
>> Hold Request -> Capture Date/Time (Date) (Between)
>> Hold Request -> Fulfillment Date/Time (Is NULL)
>> Hold Request -> Request Date/Time (Date) (Between)
>> Hold Request -> Hold Cancel Date/Time (Is NULL)
>>
>>
>> Terran McCanna, PINES Program Manager
>> ------------------------------
>>
>> Georgia Public Library Service | University System of Georgia
>>
>> 2872 Woodcock Blvd, Suite 250 l Atlanta, GA 30341
>>
>> (404) 235-7138 | tmccanna at georgialibraries.org
>>
>> http://help.georgialibraries.org | help at georgialibraries.org
>>
>> <https://www.facebook.com/georgialibraries>
>> <https://www.twitter.com/georgialibs>
>> <https://www.instagram.com/georgialibraries/>
>> <https://www.twitter.com/georgialibs>
>>
>> Join our email list <http://georgialibraries.org> for stories of Georgia
>> libraries making an impact in our communities.
>>
>>
>>
>> On Mon, Dec 21, 2020 at 9:30 AM Willis, Beth <willis at noblenet.org> wrote:
>>
>>> Hi everyone,
>>>
>>> I am trying to create a report template that includes a filter with a
>>> default value, but where that default value references the value of a
>>> different field.
>>>
>>> For example, I am working on a template to show holds that have been
>>> cancelled after the targeted items have been put on the hold shelf.  I want
>>> to include the action.hold_request.shelf_time as a filter and set its
>>> default value to that of the asset.copy.status_chhnged_time.
>>>
>>> Does anyone know how to do this?  Is it possible in the reporter?
>>>
>>> Does anyone have a template for  a cancelled holds report like this?
>>>
>>> Thanks in advance!
>>> Beth
>>> --
>>> Beth Willis
>>> Digital & Catalog Librarian
>>> NOBLE, Inc.
>>> 42 Cherry Hill Drive
>>> Danvers, MA 01923
>>> _______________________________________________
>>> Evergreen-reports mailing list
>>> Evergreen-reports at list.evergreen-ils.org
>>> http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports
>>>
>> _______________________________________________
>> Evergreen-reports mailing list
>> Evergreen-reports at list.evergreen-ils.org
>> http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports
>>
>
>
> --
> Beth Willis
> Digital & Catalog Librarian
> NOBLE, Inc.
> 42 Cherry Hill Drive
> Danvers, MA 01923
> _______________________________________________
> Evergreen-reports mailing list
> Evergreen-reports at list.evergreen-ils.org
> http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://list.evergreen-ils.org/pipermail/evergreen-reports/attachments/20201229/48bd4024/attachment.html>