1 of 19

Reports for Acquisitions workflows and fund allocations

Koha-US conference

Round Rock, TX

August 16, 2024

Sarah B. Cornell

Supervisor of Technical Services

Portsmouth Public Library, New Hampshire

2 of 19

Invoices paid on [date] to [vendor]

Benefits of the report:

Reinforces the value of accuracy by making proofreading easy to access
Creates a printable list of each batch for accounts payable
It’s flexible - display everything on a date or limit by vendor using keyword search

SQL concepts:

Create a link to view invoice using invoiceid
CAST as decimal
two nested subqueries
two runtime variables
Create a variable that includes wildcard (%) using CONCAT

3 of 19

Invoices paid on [date] to [Vendor]

4 of 19

Invoices paid on [date] to [Vendor]

5 of 19

Invoices paid on [date] to [Vendor]

6 of 19

Electronic resources and micromedia renewals

Benefits:

Can be used for other annually renewed items like museum passes
Replaces Excel: you can stop double-entry
Links directly to other information about the orders
Provides one-click access to standard question “What have we paid?”
It’s similar to a fund’s “spent” page, but:

Includes multiple funds and fiscal years
Customizable columns

SQL concepts:

There are so many acquisitions tables – this uses eight of them
It’s hard to know where the data you see in the Acq module lives in the database
If it’s hard to link to the table with the right data (or it’s not there) you can rename using CASE WHEN
Use /* and */ to hide comments to tell future you how to do something
You can limit to exactly the right values using WHERE ... IN (‘value1’, ‘value2’,…)
MAX(date) grabs only the most recent entry with that value

7 of 19

Electronic Resources and micromedia renewals

8 of 19

Electronic Resources and micromedia renewals

9 of 19

Electronic Resources and micromedia renewals

10 of 19

Circulation statistics and expenditures from FY24 informing fund allocations for FY25

Benefits of the report:

Replaces Excel so we don’t have to export/manipulate/re-enter data
Displays the same columns as Acq homepage and adds circulation data
Calculates percent of total circulation
Displays and highlights variables entered by user

SQL concepts:

Variable displayed in column heading
Variables can be called on multiple times, text has to match exactly
Sometimes you just have to hard-code something (budget_parent_id, date range)
Create a many-to-one crosswalk from location to fund code using CASE WHEN

11 of 19

Circulation statistics and expenditures from FY24 informing fund allocations for FY25

12 of 19

Circulation statistics and expenditures from FY24 informing fund allocations for FY25

13 of 19

Circulation statistics and expenditures from FY24 informing fund allocations for FY25

14 of 19

Circulation statistics and expenditures from FY24 informing fund allocations for FY25

15 of 19

Circulation statistics and expenditures from FY24 informing fund allocations for FY25

This starts with the very much shortened crosswalk from locations to funds. It’s ALL of our locations, and all of them read like “When location then fund.” As I said before, this can only be many-locations-to-one-fund or the logic breaks down.

1a. Then the subquery totals the circulation in the date range and calculates each fund’s percentage of the total. It calls on the same variables as above because that’s how I got it to work right. Not sure why.

2. This subquery identifies all the checkout statistics in the whole date range for each location – the NUMERATOR of the calculation in lines 67 and 68 above

3. This subquery uses the same “IN” statement but what it’s doing differently is coming up with the DENOMINATOR of the calculation in lines 67 and 68 above

4. This is the tail end of one of the he higher-level queries which groups everything by fundcode, then the “LIMIT BY” just makes sure everything displays instead of the default of 20 lines.

16 of 19

Circulation statistics and expenditures from FY24 informing fund allocations for FY25

17 of 19

Circulation statistics and expenditures from FY24 informing fund allocations for FY25

18 of 19

Circulation statistics and expenditures from FY24 informing fund allocations for FY25

Outstanding problems:

A CASE WHEN crosswalk is a blunt instrument.

It only goes many-to-one. If you pay for some items in a location with different funds, it’ll only put the circ into one fund.

You can’t adjust your proposed allocations without entering all of them over again.
If you want to just carry over the last year’s spend, you have to enter it manually (my current efforts have failed but it’s probably possible in SQL).
I don’t understand why (and am kind of peeved that) the statistics location data has to be included twice.
I have forgotten many of the details of this complicated report because I am human.

19 of 19

Thank you!

Sarah B. Cornell

Supervisor of Technical Services

Portsmouth Public Library, New Hampshire

sbcornell@cityofportsmouth.com

SQL reports from this presentation

Acquisitions section on reports wiki