[Evergreen-reports] Balance Owed Reports

Tue Jan 7 16:23:18 EST 2025

Hello, a quick note on how the reporter builds the SQL that it runs will
help explain this one. To simplify a little bit, the reporter builds the
raw SQL from your display and filter fields and then wraps that in a
'SELECT DISTINCT * FROM ...' so any duplicate OUTPUT rows will just go
away. To really focus on it for the list, that select distinct is applied
only to the display fields of your report, so if for example you made a
report that showed only the amount from money.billing with the raw
transform you couldn't open the results in Excel and total up the amount
billed because the output would only have a single .05 entry, a .25 entry,
and so on for all of the various fine levels charged by your circ policies,
plus a single entry for each lost item price billed, and one entry for each
grocery amount manually entered by staff. A problem with this is when you
really do care about all of the rows in the database even if their display
fields would be the same, such as 100 25-cent fines becoming a $25 total.
(See also thought experiments involving 100 duck sized horses vs 1
horse-sized duck.)

This means, for better or worse, you may need to apply the sum transform to
all monetary values that you display, even if you don't *think* that you'll
be adding anything, because you can't tell when output rows that look the
same have been elided except by hand-editing and running the debug sql to
see if / how the row count changes.

Jason

-- 
Jason Boyer
Senior System Administrator
Equinox Open Library Initiative
JBoyer at equinoxOLI.org
+1 (877) Open-ILS (673-6457)
https://equinoxOLI.org/

On Tue, Jan 7, 2025 at 3:58 PM Elizabeth Davis via Evergreen-reports <
evergreen-reports at list.evergreen-ils.org> wrote:

> Hi Susan
> Thanks, I gave that a test.   I didn't change the Timestamp field
> transform but when I changed the Balance Owed Field Transform from Sum to
> Raw Data, I saw a significant different in totals.
>
> For example, it went from $1,412.24 with the SUM transform to $486.01 with
> the RAW transform for 2025
>
>
>
> *Elizabeth Davis* (she/her), *Program Manager*
>
> *Pennsylvania Integrated Library System **(PaILS) | SPARK*
>
> (717) 256-1627 | *elizabeth.davis at sparkpa.org
> <katherine.dannehl at sparkpa.org>*
> *support.sparkpa.org <https://support.sparkpa.org/>* | *support at sparkpa.org
> <support at sparkpa.org>*
>
>
>
> ------------------------------
> *From:* Susan Morrison <smorrison at georgialibraries.org>
> *Sent:* Tuesday, January 7, 2025 3:37 PM
> *To:* Evergreen Community Reports Email List <
> evergreen-reports at list.evergreen-ils.org>
> *Cc:* Elizabeth Davis <elizabeth.davis at sparkpa.org>
> *Subject:* Re: [Evergreen-reports] Balance Owed Reports
>
> Here are the paths for a report similar to what I've used in the past. I
> will mention that previously I had used Date for the Last Billing Timestamp
> transform, and the total is about a $25 difference from when I used Year,
> and I'm not quite sure why... If anyone sees a problem with this report,
> please let me know! 😬
>
> Source: Billable Transaction Summary with Billing Location
>
> Display Fields:
> Billable Transaction Summary with Billing Location > Billing Location >
> Short (Policy) Name as "Billing Location"
> Billable Transaction Summary with Billing Location > Last Billing
> Timestamp as "Last Bill Date" (Year)
> Billable Transaction Summary with Billing Location > Balance Owed (Sum)
>
> Filters:
> Billable Transaction Summary with Billing Location > Billing Location >
> Organizational Unit ID (In List)
> Billable Transaction Summary with Billing Location > Last Billing
> Timestamp (Between) (Year)
> Billable Transaction Summary with Billing Location > Transaction Finish
> Time (is NULL)
> Billable Transaction Summary with Billing Location > Balance Owed (Sum)
> (Greater than) 0
>
>
>
> On Tue, Jan 7, 2025 at 2:10 PM Elizabeth Davis via Evergreen-reports <
> evergreen-reports at list.evergreen-ils.org> wrote:
>
> Hello Everyone,
>
> Just curious if anyone has a patron bills balance owed report that they
> wouldn't mind sharing. One of our library's auditors needs a total of bill
> balances owed to the library broken down by year of billing.  I've been
> testing the Billable Transaction Summary with Billing Location and Open
> Billable Transaction Summary sources and I'm not happy with my results.
>
> Any tips or suggestions would be appreciated.
>
> Thank you,
>
> *Elizabeth Davis* (she/her), *Program Manager*
>
> *Pennsylvania Integrated Library System **(PaILS) | SPARK*
>
> (717) 256-1627 | *elizabeth.davis at sparkpa.org
> <katherine.dannehl at sparkpa.org>*
> *support.sparkpa.org
> <https://urldefense.proofpoint.com/v2/url?u=https-3A__support.sparkpa.org_&d=DwMFaQ&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=j4SWsMDXRVOGlMb-EfMRIp9IjAos-OG-ewOKBUjTNh8&m=kJM48NUjGTG2FF2mUrpqbDGjP4NpJlj5nwjBPQIUouOE7b6JfHLApF3XoaZzUxbO&s=3ntW5QV9eVe5H9RYNlCCKr_j6U7hRFpvMB9_oBDz6IY&e=>* |
> *support at sparkpa.org <support at sparkpa.org>*
>
>
> _______________________________________________
> Evergreen-reports mailing list
> Evergreen-reports at list.evergreen-ils.org
> http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports
> <https://urldefense.proofpoint.com/v2/url?u=http-3A__list.evergreen-2Dils.org_cgi-2Dbin_mailman_listinfo_evergreen-2Dreports&d=DwMFaQ&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=j4SWsMDXRVOGlMb-EfMRIp9IjAos-OG-ewOKBUjTNh8&m=kJM48NUjGTG2FF2mUrpqbDGjP4NpJlj5nwjBPQIUouOE7b6JfHLApF3XoaZzUxbO&s=bn5bECtOfAGZ5_ABxEzCNTE8PyZ7tSCUo3A4AK5Eqgo&e=>
>
>
>
> --
>
> [image: logo with link to Georgia Public Library Service website]
> <https://urldefense.proofpoint.com/v2/url?u=https-3A__georgialibraries.org_&d=DwMFaQ&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=j4SWsMDXRVOGlMb-EfMRIp9IjAos-OG-ewOKBUjTNh8&m=kJM48NUjGTG2FF2mUrpqbDGjP4NpJlj5nwjBPQIUouOE7b6JfHLApF3XoaZzUxbO&s=9IlrtL8GR4qINSRtrAqYtS1ETzz8g4vW6fOoxsddpX4&e=>
>
> Susan Morrison, PINES Operations Analyst
>
> ------------------------------
>
> Georgia Public Library Service
>
> 2872 Woodcock Blvd, Suite 250 | Atlanta, GA 30341
>
> (404) 235-7142 | smorrison at georgialibraries.org
>
> https://help.georgialibraries.org
> <https://urldefense.proofpoint.com/v2/url?u=https-3A__help.georgialibraries.org&d=DwMFaQ&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=j4SWsMDXRVOGlMb-EfMRIp9IjAos-OG-ewOKBUjTNh8&m=kJM48NUjGTG2FF2mUrpqbDGjP4NpJlj5nwjBPQIUouOE7b6JfHLApF3XoaZzUxbO&s=fTtEYtWDQ4Y5GJh5r_Slx8h0O_ra-h29Hfh6rDL0zcE&e=>
> | help at georgialibraries.org
>
> [image: logo with link to Georgia Public Library Service Facebook page]
> <https://urldefense.proofpoint.com/v2/url?u=https-3A__www.facebook.com_georgialibraries&d=DwMFaQ&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=j4SWsMDXRVOGlMb-EfMRIp9IjAos-OG-ewOKBUjTNh8&m=kJM48NUjGTG2FF2mUrpqbDGjP4NpJlj5nwjBPQIUouOE7b6JfHLApF3XoaZzUxbO&s=rt9VZKnKoQwAiaK4xaKe3UZ7kkvHmauxrZbEjFoUKLE&e=>[image:
> logo with link to Georgia Public Library Service Instagram page]
> <https://urldefense.proofpoint.com/v2/url?u=https-3A__www.instagram.com_georgialibraries_&d=DwMFaQ&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=j4SWsMDXRVOGlMb-EfMRIp9IjAos-OG-ewOKBUjTNh8&m=kJM48NUjGTG2FF2mUrpqbDGjP4NpJlj5nwjBPQIUouOE7b6JfHLApF3XoaZzUxbO&s=i3KjRP4P2knulYanwVfCHlhRaRi6Dvwl7tEW0dnhHUE&e=>[image:
> logo with link to Georgia Public Library Service LinkedIn page]
> <https://urldefense.proofpoint.com/v2/url?u=https-3A__www.linkedin.com_company_georgia-2Dpublic-2Dlibrary-2Dservice_&d=DwMFaQ&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=j4SWsMDXRVOGlMb-EfMRIp9IjAos-OG-ewOKBUjTNh8&m=kJM48NUjGTG2FF2mUrpqbDGjP4NpJlj5nwjBPQIUouOE7b6JfHLApF3XoaZzUxbO&s=IO9WxG2VxgHl0rxBxGuncAEFvtE69X4IlDgR469WDV8&e=>[image:
> logo with link to Georgia Public Library Service Threads page]
> <https://urldefense.proofpoint.com/v2/url?u=https-3A__www.threads.net_-40georgialibraries&d=DwMFaQ&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=j4SWsMDXRVOGlMb-EfMRIp9IjAos-OG-ewOKBUjTNh8&m=kJM48NUjGTG2FF2mUrpqbDGjP4NpJlj5nwjBPQIUouOE7b6JfHLApF3XoaZzUxbO&s=rMrMf-fT6ruA9KIJfjHydom9aEMpvAGMWBT1IEfIRRQ&e=>
>
> Join our email list
> <https://urldefense.proofpoint.com/v2/url?u=http-3A__georgialibraries.org_subscription&d=DwMFaQ&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=j4SWsMDXRVOGlMb-EfMRIp9IjAos-OG-ewOKBUjTNh8&m=kJM48NUjGTG2FF2mUrpqbDGjP4NpJlj5nwjBPQIUouOE7b6JfHLApF3XoaZzUxbO&s=zNtPFChxNoCZaeAYbArareJLo7YOTY0zF_RfG8-H-7U&e=> for
> stories of Georgia libraries making an impact in our communities.
> _______________________________________________
> 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/20250107/a3d8fc7c/attachment-0001.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Outlook-spppq5tb.png
Type: image/png
Size: 5402 bytes
Desc: not available
URL: <http://list.evergreen-ils.org/pipermail/evergreen-reports/attachments/20250107/a3d8fc7c/attachment-0002.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Outlook-kxuybqdt.png
Type: image/png
Size: 5402 bytes
Desc: not available
URL: <http://list.evergreen-ils.org/pipermail/evergreen-reports/attachments/20250107/a3d8fc7c/attachment-0003.png>