First Report Challenge: A client wants to put together a report to show the cardnumber, first name and second name of all patrons on the system.
These notes are intended to be used to help answer the associated Report Challenge that was released last week.
Take a look at the question below and think about how you would approach answering it:
A client wants to put together a report to show the cardnumber, first name and second name of all patrons on the system.
Key points in this challenge:
How to access the database schema
Don't make assumptions about the name of a table
How to identify the table and field to include in the report
The basic SQL commands for a report in Koha
How to use the LIMIT function
Introduction
The first time I encountered questions such as these, the biggest obstacle was not knowing the correct table to find information in. In this specific instance, it is a common mistake to think that the table will be patrons (a phrase often used within Koha application), however, all information about patrons on Koha is held in the borrowers table.
This is why a question such as this is good starting point, it gives the opportunity to learn how to access the database and how to search it for a table. This skill will be useful in finding the correct table for future reports.
What information do we need?
Looking at the question, we can see we need to know the table to report on and we need to know the field(s) in the table that we are looking for.
How to find the tables and fields to include in the report?
The more you work to create and edit reports, the more familiar you will become with the names of the different tables. However, when just starting out you will likely need to refer to the database schema a lot! This will show you the entire database structure, including the tables and every field they contain.
You can read detailed instructions for how to find a table in the database schema below or use the clues under the yellow box to try to navigate to the database schema
For details on how to access the Koha database schema, please check our Help Centre or contact us directly.
Using the linked instructions above you should be able to navigate to the database schema in general.
If you want more instructions for how to find the specific table and fields for this report, please click the dropdown below.
I advise trying to find the information on the schema yourself first and then using the dropdown to check your work, but if you would rather just read through the instructions, please do!
Click here for instructions on how to find the correct table for this report
The Koha database schema will automatically open on this page:
There will be an alphabetical list of all the tables that makeup the database.
This list can be searched using the search box at the top of the table.
However, if it is not clear which table to use, often, I will go to the Columns section at the top of the page.
This will open a new page with all the fields within the different tables that make up the Koha database.
The different fields can be searched using the search box at the top of the table.
Even if you don't know the fields that makeup the table you're trying to identify, it is likely you will be able to make an educated guess on some of the content of the table.
In this example, we know we are looking for details about a patron. Think of some of the common fields in a patron account or fields that would logically be part of a patron account.
This could be name, cardnumber, patron category or local information custom to your system.
It is not about knowing the exact field or every field in a table, it is about considering what might logically come up in relation to a specific area of a system.
In this example, we search all the fields in the Koha database for the cardnumber field.
This narrows the entries down to only tables which contain a cardnumber field. This reduces the number of possible tables we could use for this report.
Looking down the different results, we can see there is a table called borrowers. This seems like the most likely table we need. To view the full content of the borrowers table and confirm if it contains the information we want for this report, click borrowers under the Table column.
This will open a new page where the content of the borrowers table can be viewed.
The content of this specific table can be searched using the Search box at the top of the table.
We know the report is looking for the first name of the patron and by searching name in the search box the table narrows down to only the entries including that word.
Take a note of the entry, often copying and pasting it the field into another word doc. can be useful so that the format of the field will be correct when it is added to the report.
Format of the report
Once you have identified where to find the information needed for the report, we need to determine the format of the report being created.
If you have ever viewed an SQL report, on Koha or elsewhere, you may already have an idea of the format for the report.
Tip: Look at existing reports on your system to see if you can confirm the structure for this report. Remember, we are working with one table and only fields from that table.
Scroll down to see the format of the report OR use the hints below to try to write out what you think the format of the report should be.
Hint 1
We are only using two SQL commands for this report.
Hint 2
The commands we are using are the most common in SQL reports on Koha.
Hint 3
One command must tell the report which fields to pick from the table
One command must tell the report which table the field should be take from.
For more details on the correct statements to use in this report, check our Help Centre or contact us directly.
Click here to view the SQL format
Now we know the table and the fieldnames. This means we can add them into the SQL report. Like most Koha reports, the basic format of this report will use the SELECT and FROM statements.
The table we have identified:
borrowers
The fields we have identified are:
cardnumber
firstname
surname
This means the format of the report is
SELECT borrowers.cardnumber, borrowers.firstname, borrowers.surname
FROM borrowers
We know how to use the SELECT and FROM statements and we know the fields to include.
BONUS: LIMIT
This will just define how many results will be returned. This is important for when you don't know what exactly will be brought back. Add the LIMIT function when testing a new report so that the results are not too resource intensive.
A LIMIT can be added to the end of the report. In this case, we will just add in a limit of 50, so only 50 entries will be returned regardless of how many entries there actually are in the database.
This will make the report format:
SELECT borrowers.cardnumber, borrowers.firstname, borrowers.surname
FROM borrowers
LIMIT 50
Please use this function whenever testing any report generated as part of this training.
Related Articles
Second Report Challenge: A client wants to put together a report to show the cardnumber, first name and second name, concatenated.
These notes are intended to be used to help answer associated "Report Challenge" that was released earlier. Take a look at the question below and think about how you would approach answering it: A client wants to put together a report to show the ...Running the overdue report with filters
This document will outline how to use filters for the Overdues report under the Circulation module. This document assumes that the FilterBeforeOverdueReport system preference has been set to Require. This document is intended to outline how these ...Overview of some SQL Report Functions
Overview of SQL Report Functions This FAQ is an example of a simple report describing the “anatomy” of the SQL code used. Details about the different functions are outlined in the table below the report. Some functions are mandatory to use in order ...SQL Report Request Form
How to submit an SQL report request The following are the steps of requesting a new SQL report to be created. 1. Log a ticket on the support portal to start the process. 2. Take a note of the ticket number. 3. Click the link below to be taken to an ...How to show the name of the patron with the checkout on the OPAC
This document will outline how to set the site to display the name of the patron an item is checked out to on the OPAC. This will mean that anyone searching the OPAC, logged in or not, will be able to see the patron who has a particular item checked ...