[Evergreen-reports] Cancelled Holds Report Template

[Terran McCanna]

I don't think there's a way to set a default value in the Report Template
builder - you can set a value or leave it blank to be set at run time.



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 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/20201228/d1ec6c8d/attachment-0001.html>