Koha SQL Reports Generator

Koha can answer far more questions than the standard dashboards show, but writing a useful SQL report from scratch is not always straightforward. A librarian may know exactly what they need — overdue items for one branch, circulation by item type, memberships expiring next month, items with no barcode, or titles that circulated most often during a semester — without knowing the Koha database tables or the syntax for reusable runtime filters. The VWS Online Koha SQL Reports Generator turns those common reporting questions into reviewable read-only SQL templates that can be copied into Koha Reports.

This tool does not connect to your Koha server, does not ask for database credentials and does not execute SQL. It builds a query in your browser from a vetted report template and the options you choose. You can then inspect the SQL, check which tables and patron fields it uses, review the performance warning, and decide whether the report is appropriate for your own Koha release and local configuration.

What the Koha SQL Reports Generator does

The generator is designed for reusable reporting rather than one-off hard-coded queries. Choose a report, add only the filters you need, select the amount of patron information that is appropriate, and the tool generates a single SELECT statement. Where a report benefits from filters, the query uses Koha runtime-parameter syntax instead of forcing you to edit dates or branch codes directly inside the SQL.

Current report groups include circulation, items and inventory, patrons, and data quality. Examples include current checkouts, overdue items, circulation by branch, circulation by item type, most circulated titles, renewals by branch, recently accessioned items, lost or damaged items, items never circulated, missing barcode or call-number diagnostics, patron counts, expiring memberships and a carefully labelled inactivity heuristic.

Where to use the generated SQL in Koha

In Koha, staff with the required Reports permissions can create a report from SQL in the Reports module. The exact menu wording can vary slightly by release, but the normal workflow is to open Reports, choose the option for a new SQL report, paste the query, save it with a clear name and notes, and then run it with the required runtime parameters. Always start with a limited, reviewable report before giving a query broad staff access.

The official Koha Reports manual explains SQL reports, runtime parameters, permissions and report execution in more detail.

Why runtime parameters matter

A report that contains a fixed date such as 2026-08-01 is difficult to reuse. Koha supports runtime parameters that display a field, calendar or authorised-value selector when the report is run. The generator uses this feature so one saved report can serve many reporting periods or branches.

Examples include:

• <<Start date|date>> and <<End date|date>> for reusable date ranges.
• LIKE <<Library|branches:all>> to allow one branch or the special All option.
• IN <<Item type|itemtypes:in>> for multiple item-type selection.
• IN <<Patron category|categorycode:in>> where a patron-category filter is useful.

Runtime filters are not only convenient. They can reduce the amount of data scanned and returned by reports that would otherwise be expensive to run repeatedly.

Read-only does not mean risk-free

The generator deliberately blocks data-changing or privileged SQL keywords and creates a single SELECT statement. That means its templates are non-mutating: they are not designed to insert, update or remove Koha records. However, a read-only query can still create two important risks.

The first is privacy. A report that returns patron names, card numbers, email addresses, phone numbers, balances or circulation details may expose personally identifiable or sensitive library information. Such a report should not be made public simply because it uses SELECT. The official Koha reporting guidance also warns administrators to consider sensitive patron data when reports are shared or exposed.

The second is performance. A large aggregation over the statistics table can be resource-intensive on a busy, long-running Koha installation. A poorly constrained report can consume database resources even though it changes nothing. This tool therefore shows a performance-risk label, encourages date and branch filtering and applies numeric row limits to detailed row-level reports.

Patron privacy controls

Patron-related reports expose a data-level selector so you do not have to include more identity information than the task requires. The least-identifying option is the recommended default. Depending on the selected report, you can choose a summary or minimal output, Koha borrower and card identifiers, patron names, or contact details.

For example, a statistical count of patrons by branch and category does not need names at all. An overdue-notice preparation workflow may need a card number or patron name. Contact details should only be added when they are genuinely required for the report and the result will be handled appropriately.

Current checkouts and overdue reports

Current checkout information is stored in Koha's issues table. A useful report normally joins issues to items for barcode and call-number data, biblio for title information and, when patron details are requested, borrowers. The overdue template applies the additional condition that the current due date is earlier than the current time.

The generator can add runtime branch and item-type filters. It also applies a configurable row limit so a user cannot accidentally create an unlimited detailed checkout list from the interface.

Circulation statistics: checkouts and renewals are different events

For historical circulation reporting, the generator uses Koha's statistics table instead of relying only on old_issues. Koha records transaction types such as issue, return, renew, localuse, payment and writeoff in that table. This makes it useful for period-based statistical reports.

A key reporting decision is whether a renewal should count with a checkout. The answer depends on the statistic being prepared. The generator therefore does not silently combine them. For applicable reports, you can choose whether the query should count only issue transactions or both issue and renew. The generated report notes the choice so the resulting number is easier to interpret later.

Most circulated titles

A common mistake is to count only rows from old_issues and call the result total circulation. That can omit relevant activity or make the result depend on how historical checkout records are retained. The revised VWS report uses recorded circulation transactions in statistics, joins the item to its bibliographic record, groups by title and orders by the transaction count. You can set a date range, branch, item type and whether renewals are included.

This is still a reporting definition rather than a universal metric. A library preparing official statistics should document whether renewals, in-house use and other transaction types are included and apply the same definition consistently across reporting periods.

Recently accessioned items and inventory reports

Koha's items table contains item-level data used for many inventory reports. The recently accessioned template uses items.dateaccessioned and can filter by runtime dates, branch or item type. The results include item and bibliographic identifiers that Koha can recognise for follow-up workflows.

Other item templates cover branch/item-type counts, lost items, damaged items, withdrawn items and items that have never circulated according to datelastborrowed. These reports are useful for collection review, stock work, migration checks and data cleanup, but local authorised values still determine the meaning of lost, damaged and withdrawn status codes.

Data-quality reports for barcodes, call numbers and locations

Data-quality templates can identify item records where a barcode, call number or shelving location is empty. These checks pair naturally with other VWS library tools. For example, after finding item records without a barcode you can review your numbering policy with the Koha Barcode Generator. Existing identifiers that need physical labels can be sent to the Library Barcode Sheet Generator, while call numbers can be formatted with the Book Spine Label Generator.

Why duplicate Koha barcodes are treated as a diagnostic

Modern Koha schemas define a unique constraint for the non-null items.barcode field. In a normal healthy database, duplicate non-empty item barcodes should therefore not be a routine cataloguing condition. The generator still includes a legacy barcode-integrity diagnostic because older migrations, direct database modifications or damaged data can create exceptional situations.

If the diagnostic reports duplicate non-empty barcodes, treat the result as a database-integrity issue requiring investigation rather than simply editing one label. Missing barcodes are a more normal data-quality condition and can be addressed according to the library's local item-identification policy.

Patron counts and membership reports

The patron-count template groups borrowers by branch and patron category without returning identity fields. This is often the best starting point for administrative statistics because it answers the question without exposing individual patrons.

The membership-expiry and new-enrollment reports can return more detail if required, but the privacy selector lets the report writer keep output minimal. Date filters use Koha runtime parameters so the same saved report can be used for a week, month, semester or annual review.

What “inactive patron” means in this tool

There is no single universal SQL definition of an inactive patron. A patron with no item checked out today may have used the library yesterday, while a patron who has not logged into the OPAC may still have borrowed material at a desk. For that reason, the revised report is explicitly named No recorded patron activity since date (heuristic).

The query combines borrowers.lastseen with the latest issue, renewal or return event available in statistics. A patron is listed only when both signals are older than the selected cutoff. This is a stronger approximation than simply checking whether the patron appears in the current issues table, but it remains a heuristic. Local integrations, activity that is not logged in those sources, or retention policies can change what “inactive” should mean for your institution.

Koha database schema compatibility

The public Koha schema documentation is the best reference for current tables and fields. VWS templates focus on common core structures such as items, biblio, borrowers, issues and statistics. These are long-standing parts of Koha, but administrators should still verify a report against the release actually installed and any local database changes.

The target Koha release selector is included in the generated report comments and compatibility review. It should not be interpreted as a guarantee that every locally customised database will have identical behaviour. If Koha reports an unknown table or column, compare the query with the schema documentation for your exact release before changing the SQL.

Koha identifiers in report results

Where useful, detailed VWS templates return columns with Koha's standard identifier names such as itemnumber, biblionumber and borrowernumber. Current Koha report interfaces can recognize these identifiers for hyperlinks and related batch workflows. Keeping the actual identifier column name visible also makes the SQL easier to understand and more portable across releases.

How to test a generated report safely

1. Choose the report that matches the actual question rather than the closest-sounding title.
2. Add a date, branch or item-type runtime filter when it can reduce the data set.
3. Keep patron output at the least-identifying level that still solves the task.
4. Generate the SQL and review the Read-only check, tables, runtime-parameter count, sensitive fields and performance risk.
5. Copy the SQL into Koha Reports and save it with a descriptive name and notes.
6. Run a constrained test first. If the query is a heavy aggregate, use a quiet period or non-production environment when available.
7. Check a few returned rows against known Koha records before relying on totals.
8. Only then make the saved report available to the staff roles that genuinely need it.

Report logic should be documented

Two SQL reports can use correct syntax and still answer different questions. For example, “circulation” might mean checkout events only, checkout plus renewals, or every transaction including local use. “Active patron” could mean recent circulation, OPAC login, any staff interaction or a local membership rule. A production report should therefore include a short description of its assumptions.

The VWS generator adds comments and visible notes for important choices such as renewal handling and the patron-activity heuristic. When you save the query in Koha, preserve that context in the report description so another administrator understands what the result measures.

Using SQL reports during a Koha migration

Reports can be especially useful before and after migration. Before exporting data, they can identify missing barcodes, empty item call numbers, branch/item-type distributions and accession ranges. After import, the same style of report can help compare record counts and spot unexpected gaps.

For a wider migration review, use the Koha Migration Readiness Checker. MARC record structure can be reviewed with the MARC21 Validator. Data can be moved between MARC and spreadsheets with the MARC to Excel Converter and Excel to MARC Converter for Koha Libraries.

Common Koha SQL reporting mistakes

Calling every SELECT query “safe”

A SELECT statement does not modify data, but it can still reveal private information or overload a database. Review both privacy and performance.

Hard-coding branch codes and dates

Hard-coded values make reports harder to reuse and easier to misread later. Koha runtime parameters make the intended filter visible when staff run the report.

Counting only old_issues for circulation totals

old_issues is useful for checkout history, but a circulation statistic should be defined carefully. The statistics transaction log is often a better basis for period-based counts because it records issue and renewal events separately.

Treating “no current checkout” as inactivity

A patron can be active without having an item checked out at this moment. Use a defined activity period and document which activity signals are considered.

Returning patron contact data by default

Most counts and collection reports do not require email addresses or phone numbers. Add those fields only when the workflow actually needs them.

Running a large aggregate without filters

A long-running Koha installation can have a large statistics table. Date and branch filters can make a major difference.

Related VWS Online library tools

SQL reports often reveal work that belongs in another workflow. Use the Koha Barcode Generator when you need a controlled new item-barcode range, the Library Accession Number Generator for accession sequences, and the Book Spine Label Generator for physical call-number labels. For MARC creation or repair, use the MARC21 Record Builder and MARC21 Validator.

Frequently asked questions

Does this tool run SQL on my Koha database?

No. It generates SQL text in your browser. You copy the query into the Koha Reports module yourself. The tool does not request or store your Koha database credentials.

Are the generated reports safe because they use SELECT?

They are designed to be read-only and non-mutating, but read-only does not automatically mean risk-free. Review patron privacy, result size, performance and local schema compatibility before use.

What are Koha runtime parameters?

Runtime parameters are placeholders such as <<Start date|date>> or <<Library|branches>>. Koha turns them into fields or selectors when the saved report runs, making one SQL report reusable.

Why does the circulation report ask whether renewals should count?

Koha records checkout and renewal transactions separately. Some statistical definitions count both; others report them separately. The choice should be explicit rather than hidden in the SQL.

How does the inactive-patron report work?

It is a documented heuristic. It compares the selected cutoff with both the patron's lastseen value and the latest issue, renew or return event found in statistics. It should not be treated as a universal patron-deletion rule.

Why is duplicate barcode checking called a legacy diagnostic?

Current Koha schemas normally enforce uniqueness for non-null item barcodes. Duplicate barcode results may therefore indicate legacy migrated data, direct database changes or another integrity issue rather than ordinary cataloguing.

Can I make a generated patron report public?

Do not make a report public merely because it is read-only. If the result contains patron identifiers, names, contact information, circulation information or other sensitive data, keep it restricted according to your library's privacy policy and Koha permissions.

What should I do if Koha says a column does not exist?

Check the database schema documentation for your exact Koha release and review local customisations. A current template may need adjustment for an older or customised installation.

Use generated SQL as a reviewed starting point

The most useful Koha report is not the longest query. It is the smallest, clearest query that answers a defined question, exposes only the data required and can be run repeatedly without confusing future staff. Use this generator to avoid starting from a blank SQL editor, but keep the final review in the hands of the Koha administrator who understands the local installation, privacy policy and reporting purpose.