<div dir="ltr">Hi Beth, <div><br></div><div>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).</div><div><br></div><div>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?</div><div><br></div><div>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. </div><div><br></div><div>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.</div><div><br></div><div>Good luck!</div><div><br></div><div>Lindsay</div><div><br></div><div><div><div dir="ltr" data-smartmail="gmail_signature"><div dir="ltr"><div><div dir="ltr"><b>Lindsay Stratton</b><div><b>Systems Librarian</b></div><div>Westchester Library System</div><div>570 Taxter Rd., 4th Floor</div><div>Elmsford, NY 10523</div><div><a href="mailto:lstratton@wlsmail.org" target="_blank">lstratton@wlsmail.org</a></div></div></div></div></div></div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Dec 21, 2020 at 10:43 AM Willis, Beth <<a href="mailto:willis@noblenet.org" target="_blank">willis@noblenet.org</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Thanks, Terran.<div><br></div><div>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?</div><div><br></div><div>Thanks!</div><div>Beth</div><div><br></div><div><br></div><div><span style="font-family:monospace">SELECT</span><br style="font-family:monospace"><br style="font-family:monospace"><span style="font-family:monospace">ahr.shelf_time as ShelfTime,</span><br style="font-family:monospace"><span style="font-family:monospace">ac.status_changed_time as StatusChange,</span><br style="font-family:monospace"><br style="font-family:monospace"><span style="font-family:monospace">date(ahr.cancel_time) AS HoldCancelDate,</span><br style="font-family:monospace"><br style="font-family:monospace"><a href="http://ahr.id/" style="font-family:monospace" target="_blank">ahr.id</a><span style="font-family:monospace"> as hold_id,</span><br style="font-family:monospace"><span style="font-family:monospace">ahr.current_copy,</span><br style="font-family:monospace"><span style="font-family:monospace">ac.barcode AS Barcode,</span><br style="font-family:monospace"><a href="http://st.name/" style="font-family:monospace" target="_blank">st.name</a><span style="font-family:monospace">,</span><br style="font-family:monospace"><br style="font-family:monospace"><span style="font-family:monospace">sr.title AS Title,</span><br style="font-family:monospace"><span style="font-family:monospace">au.first_given_name AS FirstName,</span><br style="font-family:monospace"><span style="font-family:monospace">au.family_name AS LastName</span><br style="font-family:monospace"><span style="font-family:monospace">       </span><br style="font-family:monospace"><span style="font-family:monospace">FROM action.hold_request ahr</span><br style="font-family:monospace"><span style="font-family:monospace">JOIN reporter.hold_request_record  rhr ON </span><a href="http://ahr.id/" style="font-family:monospace" target="_blank">ahr.id</a><span style="font-family:monospace"> = </span><a href="http://rhr.id/" style="font-family:monospace" target="_blank">rhr.id</a><br style="font-family:monospace"><span style="font-family:monospace">JOIN biblio.record_entry re ON rhr.bib_record = </span><a href="http://re.id/" style="font-family:monospace" target="_blank">re.id</a><br style="font-family:monospace"><span style="font-family:monospace">LEFT OUTER JOIN reporter.materialized_simple_</span><span style="font-family:monospace">record sr  ON </span><a href="http://re.id/" style="font-family:monospace" target="_blank">re.id</a><span style="font-family:monospace"> = </span><a href="http://sr.id/" style="font-family:monospace" target="_blank">sr.id</a><br style="font-family:monospace"><br style="font-family:monospace"><span style="font-family:monospace">INNER JOIN actor.usr au ON ahr.usr = </span><a href="http://au.id/" style="font-family:monospace" target="_blank">au.id</a><br style="font-family:monospace"><br style="font-family:monospace"><span style="font-family:monospace">LEFT OUTER JOIN action.hold_copy_map hcm ON </span><a href="http://ahr.id/" style="font-family:monospace" target="_blank">ahr.id</a><span style="font-family:monospace"> = hcm.hold</span><br style="font-family:monospace"><span style="font-family:monospace">JOIN asset.copy ac ON ahr.current_copy = </span><a href="http://ac.id/" style="font-family:monospace" target="_blank">ac.id</a><br style="font-family:monospace"><span style="font-family:monospace">JOIN config.copy_status st on ac.status = </span><a href="http://st.id/" style="font-family:monospace" target="_blank">st.id</a><br style="font-family:monospace"><span style="font-family:monospace"> </span><br style="font-family:monospace"><span style="font-family:monospace">WHERE</span><br style="font-family:monospace"><span style="font-family:monospace">date(ahr.cancel_time) BETWEEN '2020-12-01' AND '2020-12-17'</span><br style="font-family:monospace"><span style="font-family:monospace">AND ahr.pickup_lib IN (46)</span><br style="font-family:monospace"><span style="font-family:monospace">AND date(ahr.capture_time) is not NULL</span><br style="font-family:monospace"><span style="font-family:monospace">and ac.status = 8</span><br style="font-family:monospace"><span style="font-family:monospace">and ahr.shelf_time = ac.status_changed_time</span>  <br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Dec 21, 2020 at 9:49 AM Terran McCanna <<a href="mailto:tmccanna@georgialibraries.org" target="_blank">tmccanna@georgialibraries.org</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div>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):</div><div><br></div><div>
<pre>-----------------------------------------
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)</pre>

</div><div><div><div><div dir="ltr"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><br><div dir="ltr" style="margin-left:0pt"><table style="border:medium none;border-collapse:collapse"><colgroup><col width="94"><col width="530"></colgroup><tbody><tr style="height:123pt"><td style="vertical-align:top;padding:5pt"><p dir="ltr" style="line-height:1.2;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:Arial;color:rgb(0,0,0);background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap"><span style="border:none;display:inline-block;overflow:hidden;width:75px;height:75px"><img src="https://lh6.googleusercontent.com/fJoF6o0xl9XByQZmykva1sv2n_cKGUXL_BH4crkpJgSqDu-yU7-5jIypuXBsEuoXCCBAAUQ2jUztJlZDNUVOlwl1bWZIL1W4wmFuEEzCLVMxUyhzdkV7jkcRcdZZni2z8OPz4xJA" style="margin-left: 0px; margin-top: 0px;" width="75" height="75"></span></span></p></td><td style="vertical-align:top;padding:5pt"><p dir="ltr" style="line-height:1.2;margin-top:0pt;margin-bottom:0pt;margin-left:9pt"><span style="font-size:11pt;font-family:Verdana;color:rgb(30,67,155);background-color:transparent;font-weight:700;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap">Terran McCanna, PINES Program Manager</span></p><hr><p dir="ltr" style="line-height:1.2;margin-top:0pt;margin-bottom:0pt;margin-left:9pt"><span style="font-size:10pt;font-family:Verdana;color:rgb(0,0,0);background-color:transparent;font-weight:700;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap"></span><span style="font-size:9pt;font-family:Verdana;color:rgb(30,67,155);background-color:transparent;font-weight:700;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap">Georgia Public Library Service | University System of Georgia</span></p><p dir="ltr" style="line-height:1.2;margin-top:0pt;margin-bottom:0pt;margin-left:9pt"><span style="font-size:9pt;font-family:Verdana;color:rgb(30,67,155);background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap">2872 Woodcock Blvd, Suite 250 l Atlanta, GA 30341</span></p><p dir="ltr" style="line-height:1.2;margin-top:0pt;margin-bottom:0pt;margin-left:9pt"><span style="font-size:9pt;font-family:Verdana;color:rgb(30,67,155);background-color:rgb(255,255,255);font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap">(404) 235-7138</span><span style="font-size:9pt;font-family:Verdana;color:rgb(30,67,155);background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap"> | <a href="mailto:tmccanna@georgialibraries.org" target="_blank">tmccanna@georgialibraries.org</a> <br></span></p><p dir="ltr" style="line-height:1.2;margin-top:0pt;margin-bottom:0pt;margin-left:9pt"><span style="font-size:9pt;font-family:Verdana;color:rgb(30,67,155);background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap"><a href="http://help.georgialibraries.org" target="_blank">http://help.georgialibraries.org</a> | <a href="mailto:help@georgialibraries.org" target="_blank">help@georgialibraries.org</a><br></span></p><br><p dir="ltr" style="line-height:1.2;margin-top:0pt;margin-bottom:0pt;margin-left:9pt"><a href="https://www.facebook.com/georgialibraries" style="text-decoration:none" target="_blank"><span style="font-size:9pt;font-family:Verdana;color:rgb(17,85,204);background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:underline;vertical-align:baseline;white-space:pre-wrap"><span style="border:none;display:inline-block;overflow:hidden;width:36px;height:36px"><img src="https://lh4.googleusercontent.com/77DYzuoZ-zqC0pyQuYNKCrWaPuOANHsYTJze0JxE_iK4yW2sCswywdPCC8hCHOIyhO7KKIO-L6dlN1wIqdHFcyJmTa1fuc5Ok34dR8FQTtZHawpgNmovGxjg9RS0-WI74dFDHw0_" style="margin-left: 0px; margin-top: 0px;" width="36" height="36"></span></span></a><a href="https://www.twitter.com/georgialibs" style="text-decoration:none" target="_blank"><span style="font-size:10pt;font-family:Verdana;color:rgb(17,85,204);background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:underline;vertical-align:baseline;white-space:pre-wrap"><span style="border:none;display:inline-block;overflow:hidden;width:35px;height:35px"><img src="https://lh4.googleusercontent.com/1bKX66xWgHe-v5FD5l-hYX4FrZDXi1Y2aKr0DZLDA_oEHy7s01WVqgVilQcwGBzRQZAg3yOePmZncGwtiYGSgavOkb0T5Tj3c46iUJ7ZE7h-MUEhVwXQs7Oc3cahIYMN2SGlk_6b" style="margin-left: 0px; margin-top: 0px;" width="35" height="35"></span></span></a><a href="https://www.instagram.com/georgialibraries/" style="text-decoration:none" target="_blank"><span style="font-size:11pt;font-family:Verdana;color:rgb(17,85,204);background-color:transparent;font-weight:700;font-style:normal;font-variant:normal;text-decoration:underline;vertical-align:baseline;white-space:pre-wrap"><span style="border:none;display:inline-block;overflow:hidden;width:34px;height:34px"><img src="https://lh3.googleusercontent.com/U7PH5YIQnuRvbeMnGOlJAEPAre7Zqx_I6IDJAtJVwUDCp1-TmjSwP_x21qKx8ApsbcaILIuSpL3Pzau2Rz-phR_4IPcLR4LrIha_JMDHqombNtsfa1uhXcYS02QWTAyYqSf3ES_n" style="margin-left: 0px; margin-top: 0px;" width="34" height="34"></span></span></a><a href="https://www.twitter.com/georgialibs" style="text-decoration:none" target="_blank"><span style="font-size:10pt;font-family:Verdana;color:rgb(17,85,204);background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:underline;vertical-align:baseline;white-space:pre-wrap"></span></a></p></td></tr><tr style="height:21pt"><td colspan="2" style="vertical-align:top;padding:5pt"><p dir="ltr" style="line-height:1.2;margin-top:0pt;margin-bottom:0pt;text-align:center"><a href="http://georgialibraries.org" style="text-decoration:none" target="_blank"><span style="font-size:10pt;font-family:Verdana;color:rgb(17,85,204);background-color:rgb(255,255,255);font-weight:400;font-style:italic;font-variant:normal;text-decoration:underline;vertical-align:baseline;white-space:pre-wrap">Join our email list</span></a><span style="font-size:10pt;font-family:Verdana;color:rgb(0,0,0);background-color:rgb(255,255,255);font-weight:400;font-style:italic;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap"> </span><span style="font-size:10pt;font-family:Verdana;color:rgb(67,67,67);background-color:rgb(255,255,255);font-weight:400;font-style:italic;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap">for stories of Georgia libraries making an impact in our communities.</span></p></td></tr></tbody></table></div><br></div></div></div></div></div></div></div></div></div></div><br></div></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Dec 21, 2020 at 9:30 AM Willis, Beth <<a href="mailto:willis@noblenet.org" target="_blank">willis@noblenet.org</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Hi everyone,<div><br></div><div>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.<div><br></div><div>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.<br></div><div><div><br></div><div>Does anyone know how to do this?  Is it possible in the reporter?</div><div><br></div><div>Does anyone have a template for  a cancelled holds report like this?</div><div><br></div><div>Thanks in advance!</div><div>Beth<br>-- <br><div dir="ltr"><div dir="ltr"><div><div dir="ltr">Beth Willis
<br>Digital & Catalog Librarian
<br>NOBLE, Inc.
<br>42 Cherry Hill Drive
<br>Danvers, MA 01923 </div></div></div></div></div></div></div></div>
_______________________________________________<br>
Evergreen-reports mailing list<br>
<a href="mailto:Evergreen-reports@list.evergreen-ils.org" target="_blank">Evergreen-reports@list.evergreen-ils.org</a><br>
<a href="http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports" rel="noreferrer" target="_blank">http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports</a><br>
</blockquote></div>
_______________________________________________<br>
Evergreen-reports mailing list<br>
<a href="mailto:Evergreen-reports@list.evergreen-ils.org" target="_blank">Evergreen-reports@list.evergreen-ils.org</a><br>
<a href="http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports" rel="noreferrer" target="_blank">http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports</a><br>
</blockquote></div><br clear="all"><div><br></div>-- <br><div dir="ltr"><div dir="ltr"><div><div dir="ltr">Beth Willis
<br>Digital & Catalog Librarian
<br>NOBLE, Inc.
<br>42 Cherry Hill Drive
<br>Danvers, MA 01923 </div></div></div></div>
_______________________________________________<br>
Evergreen-reports mailing list<br>
<a href="mailto:Evergreen-reports@list.evergreen-ils.org" target="_blank">Evergreen-reports@list.evergreen-ils.org</a><br>
<a href="http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports" rel="noreferrer" target="_blank">http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports</a><br>
</blockquote></div>