All,
Attached is the report template that Diane is referencing. The insert
may not work for you if you do not have a folder ID 1 or actor.usr ID 1.
-Blake-
Conducting Magic
MOBIUS
On 11/12/2018 9:45 AM, Diane Disbro wrote:
We use this one.
List Patrons with Outstanding Balances Displays patron information
with total of balance owed. Filtered by patrons home library.
Diane Disbro
Branch Manager/Circulation Coordinator
Union Branch
Scenic Regional Library
308 Hawthorne Drive
Union, MO 63084
(636) 583-3224
ddis...@scenicregional.org <mailto:ddis...@scenicregional.org>
"If you only read the books that everyone else is reading, you can
only think what everyone else is thinking." -- Haruki Murkami
On Fri, Nov 9, 2018 at 9:51 AM Elizabeth Davis <eda...@albright.org
<mailto:eda...@albright.org>> wrote:
Hello Everyone,
Does anyone have a report template that looks at current
outstanding fines and lost item costs? I think I have it, but I
wanted to double check with another report. We want to look at
fines and lost items that have not been paid for yet.
Thanks,
Elizabeth Davis
Head of Digital Services
Scranton Public Library
500 Vine Street
Scranton, PA 18509
570-348-3000 ext. 3050
eda...@albright.org <mailto:eda...@albright.org>
BEGIN;
INSERT INTO reporter.template (name,description,data,owner,folder)
values(
$$List Patrons with Outstanding Balances$$,
$$Displays patron information with total of balance owed. Filtered by patrons
home library.$$,
$datacolumn${"version":4,"doc_url":"","core_class":"mobts","select":[{"alias":"Last
Name","field_doc":"","column":{"colname":"family_name","transform":"Bare","transform_label":"Raw
Data"},"path":"mobts-usr-au-family_name","relation":"372aecb08d8fd4cd60d9cc1bb8934edf"},{"alias":"First
Name","field_doc":"","column":{"colname":"first_given_name","transform":"Bare","transform_label":"Raw
Data"},"path":"mobts-usr-au-first_given_name","relation":"372aecb08d8fd4cd60d9cc1bb8934edf"},{"alias":"Patron
Barcode","field_doc":"","column":{"colname":"barcode","transform":"Bare","transform_label":"Raw
Data"},"path":"mobts-usr-au-cards-ac-barcode","relation":"224c8e96d8b0fdf42856adb3b1f78d3d"},{"alias":"Street
(1)","field_doc":"","column":{"colname":"street1","transform":"Bare","transform_label":"Raw
Data"},"path":"mobts-usr-au-mailing_address-aua-street1","relation":"8337f51e7b9fbecdcf1bedeabbcb32a4"},{"alias":"Street
(2)","field_doc":"","column":{"colname":"street2","transform":"Bare","transform_label":"Raw
Data"},"path":"mobts-usr-au-mailing_address-aua-street2","relation":"8337f51e7b9fbecdcf1bedeabbcb32a4"},{"alias":"City","field_doc":"","column":{"colname":"city","transform":"Bare","transform_label":"Raw
Data"},"path":"mobts-usr-au-mailing_address-aua-city","relation":"8337f51e7b9fbecdcf1bedeabbcb32a4"},{"alias":"State","field_doc":"","column":{"colname":"state","transform":"Bare","transform_label":"Raw
Data"},"path":"mobts-usr-au-mailing_address-aua-state","relation":"8337f51e7b9fbecdcf1bedeabbcb32a4"},{"alias":"Postal
Code","field_doc":"","column":{"colname":"post_code","transform":"Bare","transform_label":"Raw
Data"},"path":"mobts-usr-au-mailing_address-aua-post_code","relation":"8337f51e7b9fbecdcf1bedeabbcb32a4"},{"alias":"Balance
Owed","field_doc":"","column":{"colname":"balance_owed","transform":"sum","transform_label":"Sum"},"path":"mobts-balance_owed","relation":"ae3578afd60590f04b45a941451bbcde"}],"from":{"path":"mobts-usr","table":"money.open_billable_xact_summary","label":"Open
Billable Transaction
Summary","alias":"ae3578afd60590f04b45a941451bbcde","idlclass":"mobts","template_path":"mobts","join":{"usr-ae3578afd60590f04b45a941451bbcde":{"key":"id","type":"left","path":"mobts-usr-au-mailing_address","table":"actor.usr","alias":"372aecb08d8fd4cd60d9cc1bb8934edf","join":{"id-ac-usr-5a23fa0dc9911ea27fea6578b04b274a":{"type":"left","key":"usr","path":"mobts-usr-au-cards-ac","table":"actor.card","label":"Open
Billable Transaction Summary -> ILS User :: All Library
Cards","alias":"224c8e96d8b0fdf42856adb3b1f78d3d","idlclass":"ac","template_path":"mobts-usr.au-cards"},"mailing_address-372aecb08d8fd4cd60d9cc1bb8934edf":{"type":"left","key":"id","path":"mobts-usr-au-mailing_address-aua","table":"actor.usr_address","label":"Open
Billable Transaction Summary -> ILS User :: Mailing
Address","alias":"8337f51e7b9fbecdcf1bedeabbcb32a4","idlclass":"aua","template_path":"mobts-usr.au-mailing_address"}},"label":"Open
Billable Transaction Summary ::
usr","idlclass":"au","template_path":"mobts-usr"}}},"where":[{"alias":"Home
Library","field_doc":"","column":{"colname":"home_ou","transform":"Bare","transform_label":"Raw
Data"},"path":"mobts-usr-au-home_ou","relation":"372aecb08d8fd4cd60d9cc1bb8934edf","condition":{"in":"::P0"}}],"having":[{"alias":"balance_owed","field_doc":"","column":{"colname":"balance_owed","transform":"sum","transform_label":"Sum"},"path":"mobts-balance_owed","relation":"ae3578afd60590f04b45a941451bbcde","condition":{">":"0"}}],"order_by":[],"rel_cache":{"ae3578afd60590f04b45a941451bbcde":{"label":"Open
Billable Transaction
Summary","alias":"ae3578afd60590f04b45a941451bbcde","path":"mobts","join":"","reltype":"","idlclass":"mobts","table":"money.open_billable_xact_summary","fields":{"dis_tab":{"balance_owed":{"colname":"balance_owed","transform":"sum","aggregate":true,"params":null,"transform_label":"Sum","alias":"Balance
Owed","join":"","datatype":"money","op":"=","op_label":"Equals","op_value":{},"field_doc":""}},"filter_tab":{},"aggfilter_tab":{"balance_owed":{"colname":"balance_owed","transform":"sum","aggregate":"true","params":"undefined","transform_label":"Sum","alias":"balance_owed","field_doc":"","join":"","datatype":"money","op":">","op_label":"Greater
than","op_value":{"value":"0","label":"\"0\""}}}}},"order_by":[{"relation":"372aecb08d8fd4cd60d9cc1bb8934edf","field":"family_name"},{"relation":"372aecb08d8fd4cd60d9cc1bb8934edf","field":"first_given_name"},{"relation":"224c8e96d8b0fdf42856adb3b1f78d3d","field":"barcode"},{"relation":"8337f51e7b9fbecdcf1bedeabbcb32a4","field":"street1"},{"relation":"8337f51e7b9fbecdcf1bedeabbcb32a4","field":"street2"},{"relation":"8337f51e7b9fbecdcf1bedeabbcb32a4","field":"city"},{"relation":"8337f51e7b9fbecdcf1bedeabbcb32a4","field":"state"},{"relation":"8337f51e7b9fbecdcf1bedeabbcb32a4","field":"post_code"},{"relation":"ae3578afd60590f04b45a941451bbcde","field":"balance_owed"}],"224c8e96d8b0fdf42856adb3b1f78d3d":{"label":"Open
Billable Transaction Summary -> ILS User :: All Library
Cards","alias":"224c8e96d8b0fdf42856adb3b1f78d3d","path":"mobts-usr.au-cards","join":"","reltype":"has_many","idlclass":"ac","table":"actor.card","fields":{"dis_tab":{"barcode":{"colname":"barcode","transform":"Bare","aggregate":null,"params":null,"transform_label":"Raw
Data","alias":"Patron
Barcode","join":"","datatype":"text","op":"=","op_label":"Equals","op_value":{},"field_doc":""}},"filter_tab":{},"aggfilter_tab":{}}},"372aecb08d8fd4cd60d9cc1bb8934edf":{"label":"Open
Billable Transaction Summary ::
usr","alias":"372aecb08d8fd4cd60d9cc1bb8934edf","path":"mobts-usr","join":"","reltype":"has_a","idlclass":"au","table":"actor.usr","fields":{"dis_tab":{"first_given_name":{"colname":"first_given_name","transform":"Bare","aggregate":null,"params":null,"transform_label":"Raw
Data","alias":"First
Name","join":"","datatype":"text","op":"=","op_label":"Equals","op_value":{},"field_doc":""},"family_name":{"colname":"family_name","transform":"Bare","aggregate":null,"params":null,"transform_label":"Raw
Data","alias":"Last
Name","join":"","datatype":"text","op":"=","op_label":"Equals","op_value":{},"field_doc":""}},"filter_tab":{"home_ou":{"colname":"home_ou","transform":"Bare","aggregate":null,"params":null,"transform_label":"Raw
Data","alias":"Home
Library","join":"","datatype":"org_unit","op":"in","op_label":"In
list","op_value":{},"field_doc":""}},"aggfilter_tab":{}}},"8337f51e7b9fbecdcf1bedeabbcb32a4":{"label":"Open
Billable Transaction Summary -> ILS User :: Mailing
Address","alias":"8337f51e7b9fbecdcf1bedeabbcb32a4","path":"mobts-usr.au-mailing_address","join":"","reltype":"has_a","idlclass":"aua","table":"actor.usr_address","fields":{"dis_tab":{"street1":{"colname":"street1","transform":"Bare","aggregate":null,"params":null,"transform_label":"Raw
Data","alias":"Street
(1)","field_doc":"","join":"","datatype":"text","op":"=","op_label":"Equals","op_value":{}},"street2":{"colname":"street2","transform":"Bare","aggregate":null,"params":null,"transform_label":"Raw
Data","alias":"Street
(2)","field_doc":"","join":"","datatype":"text","op":"=","op_label":"Equals","op_value":{}},"city":{"colname":"city","transform":"Bare","aggregate":null,"params":null,"transform_label":"Raw
Data","alias":"City","field_doc":"","join":"","datatype":"text","op":"=","op_label":"Equals","op_value":{}},"state":{"colname":"state","transform":"Bare","aggregate":null,"params":null,"transform_label":"Raw
Data","alias":"State","field_doc":"","join":"","datatype":"text","op":"=","op_label":"Equals","op_value":{}},"post_code":{"colname":"post_code","transform":"Bare","aggregate":null,"params":null,"transform_label":"Raw
Data","alias":"Postal
Code","field_doc":"","join":"","datatype":"text","op":"=","op_label":"Equals","op_value":{}}},"filter_tab":{},"aggfilter_tab":{}}}}}$datacolumn$
,1,1
);
COMMIG;
