Creating and Running Reports
Koha uses SQL (Structured Query Language) for reports. Basically, you are asking, via SQL, to select certain information from specific fields, where the information matches specified criteria.
Of course, libraries who are comfortable with SQL can build their own reports from scratch. Note: When creating, editing, and deleting reports in Koha, we recommend enabling your reports log so that any changes to the module are logged for easier troubleshooting and report restoration if any reports are mistakenly deleted or incorrectly edited.
Guided Reports Wizard
The Guided Reports Wizard walks you through the process of creating a report by asking you to choose from a series of prompts. For instance, is it a circulation, patron, or catalog report; what criteria do you want to include et cetera. This is a fantastic way to start learning about how SQL is structured. The Koha manual has a terrific explanation of how to use the Guided Reports Wizard.
Koha Community Wiki Report Library
The Koha Community Wiki Report Library has hundreds of reports for you to use! These reports have been created by Koha users all over the world. Even if you don't find the exact report you are looking for, you may find something similar that you can use as a starting point. Access the wiki using this link. Once you find a report that you would like to use, open a new tab and click Create from SQL found on the Report Module page. Then, simply copy, paste, and save the title, notes, and SQL statement into your Koha. Note that since these are created by community members world-wide, they may have been created for an earlier version of Koha with different database structures. ByWater partners can submit a ticket for support if a report that you bring in from the wiki isn't returning the results you expect.
Mana Knowledge Base
The Mana Knowledge Base is a global knowledge base for library-centric data, which was created so that the Koha community can share information like SQL reports and serial prediction patterns. You can search for reports stored in the database and simply import them into Koha, where they will be stored on the Saved reports page. For detailed instructions on creating reports using Mana, see SQL report from Mana in the Koha manual. As with reports from the wiki, ByWater partners can submit a support ticket if a report from Mana isn't returning the expected results.
Monday Minutes: Sharing Reports to Mana reviews the process of sharing reports from your Koha reports library to the Mana Knowledge Base.
Adding an ALL option for Reports
The reports module has an option to include an 'all' option in a report when using selectable lists.
Changing the SQL
If you have a report that includes a WHERE statement that pulls a list of options from Koha, the ability add an ALL option is now available. Here are two examples Kelly and Jessie showed during the tutorial video above.
Original SQL:
SELECT branchname
FROM branches
WHERE branchcode = <<Branch|braches>>
This will give the user the ability to choose one branch from the dropdown. Alternatively, now if the need to have the choice to include all the branches ( in this example), the mySQL would change to look like this:
SELECT branchname
FROM branches
WHERE branchcode LIKE <<Branch|branches:all>>
The two changes are replacing the equal (=) sign in the WHERE statement with a LIKE and including :all after the second value between the hungry alligators.
Another SQL example
Before mySQL:
SELECT barcode, itemnumber, biblionumber, location
FROM items
WHERE location=<<LOCATION|loc>>
Now changing the WHERE statement, the mySQL will look like this:
SELECT barcode, itemnumber, biblionumber, location
FROM items
WHERE location LIKE <<LOCATION|loc:all>>
Direct Links Built into Reports
In the Koha Reports Module, if a report includes certain columns, the data in the columns will automatically offer a menu of options.
For example, if the report includes:
- borrowernumber: Actions will include View, Edit, and Check Out
- cardnumber: Check Out
- Itemnumber: View and Edit
- Biblionumber: View and Edit
Example SQL
Here is the example SQL that was provided to give an example of how each of these would work in the Report:
SELECT biblio.biblionumber , biblio.title, items.itemnumber,
items.itemcallnumber, items.barcode, borrowers.firstname,
borrowers.surname, borrowers.borrowernumber, borrowers.cardnumber
FROM issues
LEFT JOIN borrowers ON borrowers.borrowernumber=issues.borrowernumber
LEFT JOIN items ON issues.itemnumber=items.itemnumber
LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
ORDER BY RAND() LIMIT 50
Limit by Library
While Koha's report functionality continues to bring new quality of life improvements, one pain point of runtime parameters remains: parameters like itemtypes and shelving locations that can be limited in cataloging functions are not limited to a branch when applied as report runtime parameters.
Because a runtime parameter of shelving location in a report will provide a menu of all system or consortium-wide shelving locations instead of one branch's locations, we recommend libraries consolidate locations/item types/collection codes as much as possible so that library staff don't have to guess whether they need to select "A Bio," "Adult Bio," or "Adult Biography" when they run a report. (The same options are displayed in advanced searches on the Koha OPAC, so if it's confusing to staff, it's definitely also confusing to patrons!)
Batch Modify Items From an SQL Report
Users have the ability to send itemnumbers in report results directly to the batch modification tool.
Steps to Batch Modify
- Go to Reports and run a report that contains itemnumbers
- The column for itemnumbers how includes a button
- Click the batch modify button in the report results
- The list of numbers will be imported directly into batch item modification tool
- The itemnumbers that will be brought over to the batch item modification tool will only be the ones displayed on the screen. The default number of results is 20- but this can be changed up to 1000 if needed.
- Changing the number of items displayed on the report page prior to clicking the Batch Modify button will bring all the results you desire into the tool.
Permissions
Staff who have permission to execute reports, but don't have permission to batch modify items, will not be able to complete the modification, even if they see that option to modify. Staff with permission to execute reports and permission to batch item modify will be able to utilize this feature.
Drop-down Menu in Creating Reports
Whether you are creating a report from scratch, making an edit to an existing report, or bringing in a canned report from the wiki or Koha Mana database, you will an option that allows you to insert runtime parameter.
This provides the user with an option to add an authorized value, date, item types, libraries, patron categories, or a text field.
- When selecting an option, you will see a single form field for the Parameter label. The label for the field is shown when the report is run, for example, Select a library.
- Enter text in the label field and click the Insert parameter button.
- This will insert the runtime parameter into the Report's SQL editor.
- If you select the Authorized value option, the modal window will include both a label field and a field for selecting an authorized value category.
- Enter text in the label field and click the Insert parameter button.
Report Groups
Within the Reports Module, by default, Koha has several Report groups created for the library to be able to easily organize the reports. A library can create more Report Groups and also assign Report Subgroups to reports. The more organization of the reports, the easier they will be found by the correct person. This organization is extremely helpful in libraries with multiple branches.
Whether you are creating a new report or updating an existing one, a user can easily add a Report Group/Sub Group designation.
Here is a screenshot of the pre-populated Report Groups that exist in Koha. There is also a way from this screen, to create a new Report Group.
Choose "Create New" and add in a CODE and the Name. Remember, the code should be in capitalization, no spaces or special characters and 10 digits or less. For the SubGroup, the same rules apply. Create New, and add in Code/Name.
See Your New Report Group
Once you create this new report group, you will see the new tab added to the top of the Reports Module:
Subgroup designation has it own column when looking at all the reports. Also, you can filter by SubGroup from the option right below the Report Groups:
Authorized Values
A library can also create the report group and subgroups within the Authorized Values.
REPORT_GROUP
REPORT_SUBGROUP
Once these values have been added with the new groups or subgroups, they will appear in the dropdown in the Reports Module.
How Do I Open Report Results in Excel or Another Spreadsheet Software?
Koha has several options for downloading results from saved reports. Two of these options, "Comma separated text (.csv)" and "Open Document Spreadsheet" will open as files that can be opened with a spreadsheet software. Of the two, .csv is more versatile.
The description accompanying the .csv download may differ slightly depending on the default separator set in the CSVDelimiter system preference, but it will give you a hint to where your spreadsheet software should be looking for separators. In my screen shot, the delimiter in our site is a semicolon, so the description is "Semicolon separated text."
Different spreadsheet programs handle .csv files a little differently, but most will prompt you for what kind of delimiter the document is using. Here's an example of importing report results into Google Sheets.
From a new, blank spreadsheet, I've selected File, then Import, and uploaded the results from my computer. For simplicity, I've selected "Detect automatically," and my results imported flawlessly, so now I can save local copies of results, perform calculations, or apply additional formatting beyond what a report in Koha offers.
Using other Open Source spreadsheet software like LibreOffice Calc or OpenOffice Calc is a similar process: open .csv file with that software, and indicate what delimiter the file is using.
And of course, a .csv can also be opened in Microsoft Excel. How to do that varies by version of Excel on a computer, so their help center documentation will provide further guidance.
Can Koha Report Runtime Parameters be Limited to Values Used by the Library?
While Koha's report functionality continues to bring new quality of life improvements, one pain point of runtime parameters remains: parameters like itemtypes and shelving locations that can be limited in cataloging functions are not limited to a branch when applied as report runtime parameters.
Because a runtime parameter of shelving location in a report will provide a menu of all system- or consortium-wide shelving locations instead of one branch's locations, we recommend libraries consolidate locations/item types/collection codes as much as possible so that library staff don't have to guess whether they need to select "A Bio," "Adult Bio," or "Adult Biography" when they run a report. (The same options are displayed in advanced searches on the Koha OPAC, so if it's confusing to staff, it's definitely also confusing to patrons!)
Charts
The Reports Module can create charts from a report.
Create a Chart in Reports
- Navigate to Reports --> Saved reports
- Search or select the report you would like to create a chart for
- Under actions click run
- if applicable enter the parameters and click run the report
- Notice there is a Create Chart Link, click
- This will provide settings to select the type of chart and columns for visible rows
- You can create Pie Bar of Line Charts
- You can select the desired X column
- You can select the checkbox to exclude the last line
- Click Draw
Example Report
SELECT
monthname(datetime) AS month,
SUM( IF(type = 'issue', 1, 0 )) AS Issues,
SUM( IF(type = 'renew', 1, 0 )) AS Renewals,
SUM( IF(type = 'return', 1, 0 )) AS Returns,
COUNT(statistics.type) AS 'Total Transactions'
FROM statistics
WHERE YEAR(datetime) = <<Enter Year YYYY>>
GROUP BY month
ORDER BY month(datetime) ASC
Troubleshooting
There are many reasons a report might return an error and sometimes it can be difficult to understand the problem. Below are some of the most common errors and what they mean.
Ambiguous columns
You’ll see this error if your report references a field that exists in multiple tables without specifying the table name. For example, this report would display an error because the biblionumber field exists in both the items and biblio tables:
However, this version would work because it explicitly specifies which table to use for the biblionumber field:
Bad Words
Koha prevents the following words from appearing in a report: UPDATE, DELETE, DROP, INSERT, SHOW, or CREATE. If you need to use one of these words in your report, you can safely work around this security measure using a wildcard. For example, instead of this:
SELECT *
FROM action_logs
WHERE action = "DELETE"
You could use this:
SELECT *
FROM action_logs
WHERE action LIKE "DELET%"
Sensitive Information
Koha doesn’t allow the display of fields that contain sensitive information such as password, secret, or overdrive_auth_token from the borrowers/deletedborrowers tables. That includes selecting * from a table containing these fields. As the error message suggests, the way to fix this is to edit your report so that no sensitive fields are selected.
Unknown Column
This error appears when a report references a field that doesn’t exist – or that the system thinks doesn’t exist. To troubleshoot this error, you can start by double-checking your spelling and looking at the database schema to make sure the field exists in the table being referenced.
If you’re sure the field is correct and there are no typos, you can also check that you’re using the correct table name/alias, because once a table is given an alias, the full table name can no longer be used in the report. For example, this report would not work because the items table is given the alias “i” and the SELECT statement refers to the full table name:
SELECT items.itemnumber
FROM items i
Syntax Error
This error means there is some sort of technical problem with the code. This can appear in many scenarios but some good first troubleshooting steps include: making sure the report isn’t missing any commas, checking that all parentheses are closed, and ensuring all clauses are written in the correct order.
Related Articles
Using Mana to Import Reports into Koha
The Koha community shares reports through Mana-kb. The setup is the same as for Serials and adds a Mana link in the Reports Module. Using Mana in the Reports Module When creating a SQL report, there is a an option in the dropdown - “New SQL from ...Koha Item Status Reports
In another article, there was a discussion of a variety of item statuses and how their behavior can be customized in Koha. That article covered everything that is a “status” in Koha: Withdrawn, Lost, Damaged, Restricted, and Not For Loan. However, ...Notices in Koha
Notices are a powerful way to communicate with patrons about their checked-out materials, holds, renewals, etc. Koha has many options for customizing the notices and customizing how they are delivered, both through settings configuration and through ...Item Types in Koha
One of the key components of your library's collection will be the Item Type designation. Item types are a way for your patrons to identify what the item is and more importantly to Koha how this item circulates in your library. Here is a link to the ...Koha Offline Circulation Tool
The Koha Offline Circulation Tool that was created by Kyle Hall for Windows. The offline circulation tool for Windows will generate a KOC (koha offline circulation) file that you can upload into Koha once your system comes back up. This tool works ...