Question of the Week - Using the CASE statement and formatting information to be included in a SQL report
This document will outline how a list of information can be formatted so it can be easily added to a SQL statement as well as a creative way we used the CASE statement to categorise the different system preferences being shown in an SQL report.
The question of the quarter
How do I easily add a list of information to a SQL report and control how it is displayed?
Key takeaways:
The most complicated reports are opportunities to learn new SQL.
To get the most out of the reports module we may need to manipulate the data in other programmes
Experience with excel, notepad and notepad++ can all help you get the most out of SQL reports.
You are not limited to how reports results display by default
Using the CASE statement
The * brings back all content in the identified tables BUT MUST BE USED WITH CAUTION.
This work requires some other applications, in addition to Koha.
You will need:
Excel or equivalent
Notepad (or Notepad++ or equivalent)
Koha database schema
Including information not captured in the Koha databas
This question involved helping a client edit an already existing report to include missing information. The problem we encountered was the information they client wanted was not stored directly in the table on the database.
In this situation, the client had created a report to see details of the system preferences available on the site in an easy to read, downloadable, format. The problem was the client wanted the system preference to be sorted by module, and module is not an entry in the Koha database schema.
Let's stop here, and breakdown where the different information for the report was pulled from.
What is a system preference module and where can they be found?
If you go Koha administration > System preferences this will take you into the list of system preferences on the system. Along the left hand side of the page, there will be a list of the modules each system preference is sorted under. However, this information does not current exist in the Koha database schema.
We had to find a way to add this information to the report in a useable way.
The original report
Let's take a look at the basic report the customer created and sent onto us for editing:
SELECT *
FROM systempreferences
FROM systempreferences
This is a simple report that will bring back all content from the systempreferences table in the Koha database schema.
What does this report mean?
SELECT
The SELECT statement tells the report what information should be returned.
*
The * statement means all. This will allow a report to bring back all content in one or more tables. But beware! This can easily be misused and create a report so large that it will take up too many resources and slow the system.
FROM
The FROM command tells the report which table the information is being returned from.
Running this report returns results like this:
There are five columns in the results but none of them include the module the system preference sits under.
Solution
The first thing for us to confirm was if there was any way to get a list of system preference modules from within Koha. To confirm this, I approached my technical colleague and he was able to locate a list of the different modules and the system preferences associated with them. This won't be possible in every scenario, but it highlights how many colleagues might be involved in the creation or editing of a report. This may not always be apparent when initially requesting the report, but often, we don't work alone to find solutions to the issues raised.
Question, why not just send on the file of system preferences and modules to the customer?
Ans: The customer wanted to be able to check any new system preferences as they were updated in new releases of Koha. By sending on the file, we would have taken away the customer independence and put the responsibility for following up on this on us. Instead, we wanted to find a solution that would allow the client to retain their independence when working with the system and could be updated as the system changed.
Once we had the list of system preferences and the associated modules, we needed to format the list to be incorporated into the existing report. To do this, we want to insert the system preferences and modules into the following phrase:
The format of the command is:
WHEN ' SYSTEMPREFERENCENAME ' THEN ' MODULE
We can create a
Modifying the file in excel or equivalent
Note: We do not provide support for Excel or similar tools, specifically. We may be able to provide some minor advice, but we can't provide in depth advice.
Open the file in Excel:
Move content of the list to column C:
Input WHEN in column A:
Input three single quotes into column B, like this: '''
Input three single quotes into column D, like this: '''
Input THEN into column E:
Input three single quotes into column F, like this: '''
Insert the title of the module in column G:
Input three single quotes into column H, like this: '''
Use the copy tool in excel by dragging the content of the cells either side of the system preference down the sheet.
Using an equals sign in Excel can be read as part of a formula so it is easier to edit the data by replacing the equals sign with something more neutral. In this case, I decided to change the equals to exclamation marks.
Use the find and replace tool to change the equals sign to exclamation marks.
This also works to make the module headings more noticeable, so they will be easier to see as you scroll down the file. When you reach a new module heading, copy and paste it into column G and drag it down the column until you reach the next column heading.
Open the file in Notepad
I downloaded the sheet as a CSV and opened it in Notepad. This work can also be completed in Notepad or an equivalent programme. If there are any issues with saving the file and opening it in Notepad, simply select all the columns in the Excel sheet and copy and paste them into a Notepad file.
I tidied up any data that didn't look like the format above by using the Find and Replace options. Go to Edit > Replace.
Use find and replace to remove the spaces between...
Copy and paste the information to be changed into the top box and put the correct content in the bottom.
Do the same for the exclamation point.
This will remove the exclamation point.
Do the same for the additional quotes.
Delete out When ''THEN' ''
Adding the formatted list to the existing report
Copy and paste the edited list into the SQL report and delete out any lines without specific system preference:
The ELSE statement
The ELSE statement was particularly important. This statement allows any content that does not have a specific label defined to be assigned an alternative. This means that when the clients were updating the report as the system was updated, they could easily see the system preferences that needed to have the module defined.
The ELSE statement appears at the end of the list:
In this case, I decided to set the label as Module but this can be whatever would make it clear that there is an entry in the report that is not included in the overall list.
Click Update and run SQL
This will bring up the results for the report.
Note: This report will need to updated at every upgrade because new system preferences are added they will not be included in the report. To confirm which system preferences need to be added, go to the end of the report to the ELSE statement.
| Version | Date | Detail | Author |
1.0 | 10/05/21 | Document Created | Holly Cooper |
1.1 | 06/03/26 | Details added | Holly Cooper |
| 1.2 | 09/03/26 | Document created | Holly Cooper |
| 1.3 | 24/03/26 | Adding details | Holly Cooper |
Related Articles
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 ...Question of the Quarter - How to deal with 500 errors
This document will outline how we resolved some interesting questions that have come up in the last few months: What does it mean when a table on Koha is showing a 500 error and how can it be addressed? Key takeaways: A 500 error on a table in Koha ...How to Find and Run a SQL Report
What are reports for? Reports are used to find and present data about the information on the system in a structured manner. Reports use SQL to ask questions of the Koha database. How to find reports on your system Go to the Reports module on the Koha ...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 ...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 ...