[Evergreen-reports] Cancelled Holds Report Template

[Willis, Beth]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://list.evergreen-ils.org/pipermail/evergreen-reports/attachments/20201221/674d03b1/attachment.html>