SELECT

One of two mandatory functions to enable the report to work. The other is the FROM function. This function defines the columns and tables you want to draw information from.

You can request information from one or multiple tables with the SELECT function, but to get these to display you will need to use the correct JOIN function later in the report.

CONCAT 

Used in this context the concatenate function creates a clickable link to records on the system. 

This specific sequence creates a link into the bibliographic record. You can find a list of these phrases here: https://wiki.koha-community.org/wiki/SQL_Reports_Library. 

The part you need is just under the TOC, with heading “Links”.

If you didn’t want to link to the bibliographic record you would just use SELECT items.biblionumber which would list the biblionumbers.

AS

Using AS re-names the field from the table you are drawing from into something clearer. If you are changing the name to a single word, the function can go items.barcode AS Barcode i.e. Barcode is just on its own. If you are changing the label to multiple words you need to bracket the words with quotes e.g. items.barcode AS ‘Barcodes in Question’.

FROM

One of two mandatory functions to enable the report to work. The other is the SELECT function. FROM is the function that determines what the primary table the information requested with the SELECT function will be drawn from. Only one table should be used in conjunction with the FROM function, information from other tables is found by linking them to this primary one with a JOIN function.

ITEMS

In this example, ITEMS is the table holding the columns information is being taken from. The names of the different tables of the KOHA database can be found here: http://schema.koha-community.org/

It is important to look at the schema for the version of KOHA your system is on, as the names of tables or which table contains certain information may change during upgrades. 

JOIN

There are a few different types of joins you can do. These work to link multiple tables of information together. This takes some practice as joining tables incorrectly can exclude relevant information.

When using a JOIN it is necessary to find a column that is common between the two tables to link them together. In this example the common column between the items and the biblio table is the biblionumber. 

For more details on the different joins check here: https://www.w3schools.com/sql/sql_join.asp

WHERE

WHERE is an optional condition you can use to limit the information being reported on. There are a vast variety of parameters that can be set with the WHERE function, including specifying a range of dates, a specific barcode or the status of a loan. 

In this example the WHERE function is adding the parameter that only items found within a specified location in the library will be found in the results. 

AND/OR

If there are multiple conditions you want to add you can use the AND or the OR function. These essentially tack on a second WHERE function.

In this case I am asking WHERE the shelving location of item is under Main Shelves AND the branch is selected by the person running the report.

It is also possible to use the OR function in the same way. 

The AND function is exclusive, meaning the information being brought back need to fulfil all both parameters set. 

The OR function is inclusive, meaning the information brought back can fulfil any of the parameters that are set. 

<<>>

These <<>> operators allow the creation of dropdown menus. Information for the dropdown menus are drawn from different authorised values on the system. 

In this example, we are using the branches authorised value to allow the person running the report to limit the search to a specific branch. When the report is run, before returning any results, the person running it will need to select one branch to isolate the report results on. This is vital on larger systems, with multiple branches, where running a report on every branch at once might slow the system. 

It is important to note that if the report is being written up on the SQL database, as opposed to the REPORTS module on the KOHA site operators such as these will not work. 

ORDER BY ASC/DESC

This is a function that sorts the information results by a certain subset of the data. 

For instance, if you were looking for results for patron cardnumbers and patron first names, you could ORDER BY patron first names in ascending order by using the function ORDER BY borrowers.cardnumber ASC. It is possible to order the column in ascending (ASC) or descending (DESC) order. 

LIMIT

This is an optional parameter to narrow the output to a specific number of results.

The parameter is used in conjunction with the number of results, in the example above it is LIMIT 7, so only 7 results will be returned, regardless of how many results there are in total.

This can be a useful parameter when running a report which may return an extremely large amount of results which could risk slowing or stalling the system.