SQL Reports Library

STOP: Have you heard of mana-kb? it allows you to search and and submit to a shared library of SQL reports directly from Koha!? - Mana docs

The following SQL statements have been written by Koha users world-wide. Keep in mind that not all reports have been tested and should be read thoroughly before applying them to your own system.

General Information for SQL Reports

Tips

Documentation on SQL tables and fields

Here is the Koha DB schema

A link to more specific schemas (Acquisitions only now)

Blocked SQL fields and keywords

There are several fields in the Koha database that contain passwords, authentication tokens, and other confidential data. Many of these fields are encrypted in the database. These fields are specifically blocked when creating SQL reports.

Additionally, there are several words in ordinary language that act as keywords with special functions in SQL. These words are also blocked when creating SQL reports.

If you try to access a blocked database field or use a blocked keyword, the Koha reports module will give you an error message when you try to save or run the report.

Blocked fields

Koha bug 37508 prevents the Koha SQL reports module from outputting fields that may contain passwords or other types of authentication tokens. Bug 37508 was initially pushed to 24.11 and then back-ported to several older versions of Koha.

In Koha 24.05, the fields that are blocked in SQL reports are:

• api_keys.secret
• aqbookseller_interfaces.password
• borrowers.overdrive_auth_token
• borrowers.password
• borrowers.secret
• borrower_modifications.password
• borrower_modifications.verification_token
• borrower_password_recovery.uuid
• deletedborrowers.overdrive_auth_token
• deletedborrowers.password
• deletedborrowers.secret
• oauth_access_tokens.access_token
• smtp_servers.password
• vendor_edi_accounts.password
• z3950servers.password

Because these fields are blocked in any table, any attempt to run a SELECT query that gathers all fields from a table that contains one of these fields will also be blocked (i.e. "SELECT * FROM borrowers" will fail because borrowers.password is a part of " * ").

Blocked keywords

Koha has blocked several SQL keywords for a very long time. In 2024, new words were also blocked as a part of bug 37508 to prevent the possible exposure of secure authentication information in the Koha database.

Some of these words must be matched exactly to trigger an error message. For example, the word UPDATE is blocked, but it requires an exact match. Therefore "UPDATE" will trigger a block but "UPDATED" or "UPDATES" will not.

Other words only require a partial match. For example, the word "password" is blocked, and it only requires a partial match. Therefore "password" will trigger a block, and "passwords" will also trigger a block.

In Koha 24.05, the words that are blocked in SQL reports are:

Word	Reason	Match type
UPDATE	SQL command used to modify rows in one or more tables Could cause damage to your Koha database	Exact match
DELETE	SQL command used to delete rows from one or more tables Could cause damage to your Koha database	Exact match
DROP	Group of SQL commands similar to DELETE Could cause damage to your Koha database	Exact match
INSERT	SQL command used to insert rows into a table Could cause damage to your Koha database	Exact match
SHOW	Group of commands used to provide information about the database and server status Could expose confidential data in your Koha database	Exact match
CREATE	Group of SQL commands used to create databases, tables, users, etc Could cause damage to your Koha database	Exact match
password	Could expose secure authentication information in your Koha database	Partial match
token	Could expose secure authentication information in your Koha database	Partial match
uuid	Could expose secure authentication information in your Koha database	Partial match
secret	Could expose secure authentication information in your Koha database	Partial match

Because field names with variations of the word "password" appear in in several tables, the following field names that contain the string "password" have been placed on an allow-list in the reports module.

• password_expiration_date
• password_expiry_days
• reset_password
• change_password
• min_password_length
• require_strong_password
• password_expiration_date

Links

If you want to put links to your report, you can use the SQL's CONCAT keyword in your SELECT clause.

for example, the following SQL Report will list all your biblio with a link to each of them.

SELECT
  biblionumber, 
  CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblionumber,'\">',title,'</a>') AS Title 
FROM biblio 
ORDER BY biblionumber

Links by borrower

-- Patron information screen
CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=', borrowernumber, '\">', borrowernumber, '</a>' ) AS borrowernumber,

-- Patron notices
CONCAT('<a href=\"/cgi-bin/koha/members/notices.pl?borrowernumber=', borrowernumber, '\">', borrowernumber, '</a>' ) AS borrowernumber,

-- Patron files
CONCAT('<a href=\"/cgi-bin/koha/members/files.pl?borrowernumber=', borrowernumber, '\">', borrowernumber, '</a>' ) AS borrowernumber,

-- Circulation
CONCAT('<a href=\"/cgi-bin/koha/circ/circulation.pl?borrowernumber=',borrowernumber,'\">', borrowernumber, '</a>') as borrowernumber,

-- Patron accounts and fines
CONCAT('<a href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber=',borrowernumber,'\">', borrowernumber, '</a>') as borrowernumber,

-- Patron invoice
CONCAT('<a href=\"/cgi-bin/koha/members/maninvoice.pl?borrowernumber=',borrowernumber,'\" target="_blank">',borrowernumber,'</a>') AS borrowernumber,

-- Patron logs
CONCAT('<a href=\"/cgi-bin/koha/tools/viewlog.pl?do_it=1&modules=CIRCULATION&object=', borrowernumber, '\">', 'Circulation log' , '</a>' ) as borrowernumber,

-- Call patron's phone number
CONCAT('<a href=\"tel:',phone,'\">',phone,'</a>') as phone,

-- Renew patron
CONCAT('<a href=\"/cgi-bin/koha/members/setstatus.pl?borrowernumber=', b.borrowernumber, '&destination=&reregistration=y\" target=\"_blank\">', 'renew', '</a>' ) AS renewal_link,

Links by bib

-- Show bib record
CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblionumber, '\">', biblionumber, '</a>' ) AS biblionumber,

-- MARC detail
CONCAT('<a href=\"/cgi-bin/koha/catalogue/MARCdetail.pl?biblionumber=', biblionumber, '\">', biblionumber, '</a>' ) AS biblionumber,

-- Add item
CONCAT('<a href=\"/cgi-bin/koha/cataloguing/additem.pl?biblionumber=', biblionumber, '\">', biblionumber, '</a>' ) AS biblionumber,

-- Place hold
CONCAT('<a href=\"/cgi-bin/koha/reserve/request.pl?biblionumber=', biblionumber, '\">', biblionumber, '</a>' ) AS biblionumber,

-- Edit biblio record
CONCAT('<a href=\"/cgi-bin/koha/cataloguing/addbiblio.pl?biblionumber=', biblionumber, '\">', biblionumber, '</a>' ) AS biblionumber,

Links by basket

CONCAT('<a href=\"/cgi-bin/koha/acqui/basket.pl?basketno=', basketno, '\">', basketno, '</a>' ) AS basketno

Links by branchcode

Circ rules:

CONCAT('<a href=\"/cgi-bin/koha/admin/smart-rules.pl?branch=', branchcode, '\">', branchcode, '</a>' ) AS branchcode

Links by item

-- Edit item
CONCAT('<a href=\"/cgi-bin/koha/cataloguing/additem.pl?op=edititem&biblionumber=', biblionumber, '&itemnumber=', itemnumber, '#edititem', '\">', itemnumber, '</a>' ) AS itemnumber,

-- Edit item, 20.11 and later:
CONCAT('<a href=\"/cgi-bin/koha/cataloguing/additem.pl?op=edititem&itemnumber=', itemnumber, '#edititem', '\">', itemnumber, '</a>' ) AS itemnumber,

-- Item detail
CONCAT('<a href=\"/cgi-bin/koha/catalogue/moredetail.pl?', 'biblionumber=', biblionumber, '&itemnumber=', itemnumber, '\">', itemnumber, '</a>' ) AS itemnumber,

-- Item detail, 20.11 and later:
CONCAT('<a href=\"/cgi-bin/koha/catalogue/moredetail.pl?','&itemnumber=', itemnumber, '\">', itemnumber, '</a>' ) AS itemnumber,

-- Circulation log
CONCAT( '<a href=\"/cgi-bin/koha/tools/viewlog.pl?do_it=1&modules=CIRCULATION&info=', itemnumber, '\">', 'Circulation log' , '</a>' ) as itemnumber,

Links by subscription

CONCAT('<a href=\"/cgi-bin/koha/serials/subscription-history.pl?subscriptionid=',subscriptionid,'\">', subscriptionid, '</a>') as subscriptionid

Links by syspref

CONCAT('<a href=\"/cgi-bin/koha/admin/preferences.pl?op=search&searchfield=?variable=', variable, ' : ' ,value, '\">', variable, '</a>' ) AS 'Syspref',

Links by report ID

--- view the report
CONCAT('<a href=\"/cgi-bin/koha/reports/guided_reports.pl?reports=' , id , '&phase=Show+SQL', '\">',id,'</a>') AS 'Report number',

--- edit the report
CONCAT('<a href=\"/cgi-bin/koha/reports/guided_reports.pl?reports=', id, '&phase=Edit%20SQL">', id, '</a>' ) AS id

Links by Fund Code

CONCAT('<a href=\"/cgi-bin/koha/admin/aqbudgets.pl?op=add_form&budget_id=', aqbudgets.budget_id, '&budget_period_id=', aqbudgetperiods.budget_period_id, '\">', aqbudgets.budget_id, '</a>' ) AS 'Fund Code',

Links by Accountlines_id

-- Details for fee
CONCAT('<a href=\"/cgi-bin/koha/members/accountline-details.pl?accountlines_id=',accountlines_id,'\">', accountlines_id, '</a>') AS accountlines_id, 
-- Pay a fee
CONCAT('<a href=\"/cgi-bin/koha/members/paycollect.pl?borrowernumber=',borrowernumber,'&pay_individual=1&debit_type_code=',debit_type_code,'&amount=',amount,'&amountoutstanding=',amountoutstanding,'&description=',description,'&title=&itemnumber=&accountlines_id=',accountlines_id,'&payment_note=&remote_user=change_given=','\">',accountlines_id,'</a>') AS accountlines_id,
-- Write off a fee
CONCAT('<a href=\"/cgi-bin/koha/members/paycollect.pl?writeoff_individual=1&borrowernumber=',borrowernumber,'&debit_type_code=LOST&amount=',amount,'&amountoutstanding=',amountoutstanding,'&description=',description,'&itemnumber=&accountlines_id=',accountlines_id,'&payment_note=','\">',accountlines_id,'</a>') AS accountlines_id,

Query MARC

MySQL has some XML handling functions (since MySQL 5.1.5): http://dev.mysql.com/doc/refman/5.6/en/xml-functions.html

For version 17.05.x: the "marcxml" field of the "biblioitems" table changes to the "biblio_metadata" table and the "metadata" field

For example:

 
SELECT 
  ExtractValue((
    SELECT metadata
    FROM biblio_metadata
    WHERE
      biblionumber=14),
      '//datafield[@tag="952"]/subfield[@code>="a"]') AS ITEM;

or the equivalent

SELECT
  ExtractValue(metadata,'//datafield[@tag="952"]/*') AS ITEM
FROM biblio_metadata
WHERE biblionumber=14;

return the entire 952 data for all 952 fields for biblionumber 14 (without delimiting).

 
SELECT 
  ExtractValue((
    SELECT metadata 
    FROM biblio_metadata 
    WHERE biblionumber=14),
      '//datafield[@tag="260"]/subfield[@code="b"]') AS PUBLISHER;

returns the 260$b data for biblionumber 14.

 
SELECT biblionumber, ExtractValue(metadata, 'count(//datafield[@tag="505"])') AS count505 
FROM biblio_metadata
HAVING count505 > 1;

will give a list of biblionumbers along with the number of 505 fields (only when there is more than one 505 field)

SELECT biblionumber,  
       substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 8,4 ) AS 'PUB DATE', 
       title 
FROM biblio_metadata 
INNER JOIN biblio USING (biblionumber) 
WHERE biblionumber = 14

Control fields can be queried using ExtractValue(metadata,'//controlfield[@tag="008"]'), and fixed fields may be extracted using the SQL SUBSTRING() function.

SELECT SUBSTR(EXTRACTVALUE(metadata,"//leader"),6,1) as leader_position_6
FROM biblio_metadata
WHERE biblionumber=1

The MARC leader can be queried using ExtractValue(metadata,'//leader').

Note that ExtractValue concatenates multiple tags into a single field. These can be individually addressed using an array index after [@tag="856"]:

ExtractValue( metadata, '//datafield[@tag="856"][1]/subfield[@code="u"]' ) as '856$u'

The index is 1-based, [@tag="856"][1] is the first tag, [@tag="856"][2] is the second, etc.

Here's a bit of shell script that will generate the first 10 values of 650$a:

for i in {1..10}; do
echo "ExtractValue(metadata,"\
"'//datafield[@tag=\"650\"][$i]"\
"/subfield[@code=\"a\"]') AS 'subjects $i',"
done

Here's the output:

ExtractValue(metadata,'//datafield[@tag="650"][1]/subfield[@code="a"]') AS 'subjects 1',
ExtractValue(metadata,'//datafield[@tag="650"][2]/subfield[@code="a"]') AS 'subjects 2',
ExtractValue(metadata,'//datafield[@tag="650"][3]/subfield[@code="a"]') AS 'subjects 3',
ExtractValue(metadata,'//datafield[@tag="650"][4]/subfield[@code="a"]') AS 'subjects 4',
ExtractValue(metadata,'//datafield[@tag="650"][5]/subfield[@code="a"]') AS 'subjects 5',
ExtractValue(metadata,'//datafield[@tag="650"][6]/subfield[@code="a"]') AS 'subjects 6',
ExtractValue(metadata,'//datafield[@tag="650"][7]/subfield[@code="a"]') AS 'subjects 7',
ExtractValue(metadata,'//datafield[@tag="650"][8]/subfield[@code="a"]') AS 'subjects 8',
ExtractValue(metadata,'//datafield[@tag="650"][9]/subfield[@code="a"]') AS 'subjects 9',
ExtractValue(metadata,'//datafield[@tag="650"][10]/subfield[@code="a"]') AS 'subjects 10',

If there happen to be 11 subjects rather than 10, you're out of luck. Also, note that there's a trailing comma after "AS 'subjects 10',", which will cause a syntax error if you have a WHERE clause directly after this list.

If you want to find all records with 7XX tags, you can do this:

SELECT
    biblionumber,
    ExtractValue(metadata,'//datafield/@tag') AS tags
FROM
    biblio_metadata
WHERE
    ExtractValue(metadata,'//datafield/@tag') REGEXP '7[0-9][0-9]';

Getting records based on their subfield values

Here is how to get the records that have a subfield of 041 containing "spa"

SELECT biblionumber
  FROM biblio_metadata
 WHERE ExtractValue(metadata,'//datafield[@tag="041"]/*') LIKE '%spa%';

Here is how to get the records whose 041$a is "spa"

SELECT biblionumber
  FROM biblio_metadata
 WHERE ExtractValue(metadata,'//datafield[@tag="041"]/subfield[@code="a"]') = 'spa';

Using svc/barcode in reports

The barcode service provided by /cgi-bin/koha/svc/barcode can generate barcode images which will display in the report:

SELECT
    CONCAT( p.firstname, ' ', p.surname, ' (', p.branchcode, ')' ) AS 'patron (homebranch)',
    CONCAT( 
        '<img src="/cgi-bin/koha/svc/barcode?barcode=',
         cardnumber, 
        '&type=',
        <<Barcode type (Code39 UPCE UPCA QRcode NW7 Matrix2of5 ITF Industrial2of5 IATA2of5 EAN8 EAN13 COOP2of5)>>,
        '"></img>') AS cardnumber,
    cardnumber AS 'cardnumber text'
FROM
    borrowers p

Adding the following values in authorised_values with category 'BCTYPE' will be helpful:

authorised_value	lib	lib_opac
Code39	Code 39	Barcode must start and end with asterisks e.g. *000042*
UPCA	UPC A	11 or 12 numeric characters
UPCE	UPC E	6 or 7 numeric characters
QRcode	QR Code
NW7	NW7 (Codabar)	May contain 0-9 + - / . + A-Z
Matrix2of5	Matrix 2 of 5	Must be entirely numeric
ITF	ITF (Interleaved 2 of 5)	Must be entirely numeric
Industrial2of5	Industrial 2 of 5	Must be entirely numeric
IATA2of5	IATA 2 of 5	Must be entirely numeric
EAN8	EAN8	Barcode must be 7 or 8 numeric characters
EAN13	EAN13	Barcode must be 12 or 13 numeric characters
COOP2of5	COOP 2 of 5

This allows the report to be re-written as

SELECT
    CONCAT( p.firstname, ' ', p.surname, ' (', p.branchcode, ')' ) AS 'patron (homebranch)',
    CONCAT( 
        '<img src="/cgi-bin/koha/svc/barcode?barcode=',
         cardnumber, 
        '&type=',
        <<Barcode type|BCTYPE>>,
        '"></img>') AS cardnumber,
    cardnumber AS 'cardnumber text' 
FROM
    borrowers p

Using font-awesome fonts

Green checks and red Xs can be added using Font-awesome fonts and unicode characters:

select
   IF(
     biblionumber % 2 = 0,
    '<i style= "color:green" class="fa fa-check">',
    '<i style="color:red">✖</i>'
    ) as even,
    biblionumber
FROM
    biblio

action_logs

In the action_logs table, timestamp always indicates when the log entry was written, and user is either the borrowernumber of the staff member taking the action or 0 if the action was initiated by Koha itself (e.g. in the case of fines added or updated by fines.pl).

The values of action, object and info vary by module, and are shown below, along with the system preference that enables or disables the those logs.

syspref	module	action(s)	object	info
FinesLog	FINES	[blank], CREATE, MODIFY	borrowernumber	Data::Dumper output from accountlines
IssueLog, ReturnLog	CIRCULATION	ISSUE, RETURN	borrowernumber (NULL for RETURN)	itemnumber
BorrowersLog	MEMBERS	ADDCIRCMESSAGE, CHANGE PASS, CREATE, DELCIRCMESSAGE, DELETE, MODIFY, RENEW	borrwernumber	ADDCIRCMESSAGE, DELCIRCMESSAGE => message added or deleted; CHANGE PASS, CREATE, DELETE => blank; MODIFY => 'UPDATE (executed with arg: <borrowernumber>)', RENEW => 'Membership renewed'
SubscriptionLog	SERIAL	ADD, DELETE, MODIFY, RENEW	subscription.subscriptionid	[blank]
AuthoritiesLog	AUTHORITIES	ADD, DELETE, MODIFY	links to auth_header.authid	for ADD and DELETE, this simply says 'authority', for 'MODIFY', contains changes to the authority record.
[Always enabled]	SYSTEMPREFERENCE	MODIFY	NULL	systempreferences.value
ReportsLog	REPORTS	ADD, DELETE(?)	saved_sql.id (i.e. the report number)	saved_sql.savedsql (sql query)
CataloguingLog	CATALOGUING	ADD, DELETE, MODIFY	itemnumber or biblionumber	literal 'item' or 'biblio', followed by data dumper dump of modified data.
HoldsLog	HOLDS	CREATE, CANCEL, DELETE, MODIFY	reserve_id	Data dumper of reserves?
LetterLog	?	?	?	?
CronjobLog	CRONJOBS	RUN	NULL	cron script name with full path.

Runtime Parameters

If you feel that your report might be too resource intensive you might want to consider using runtime parameters to your query. Runtime parameters basically make a filter appear before the report is run to save your system resources.

Syntax

<<Question to ask|authorized_value>>

• The << and >> are just delimiters. You must put << at the beginning and >> at the end of your parameter

• The 'Question to ask' will be displayed on the left of the string to enter.

• Note that you can have more than one parameter in a given SQL
• Note that entering nothing at run time won't probably work as you expect. It will be considered as "value empty" not as "ignore this parameter". For example entering nothing for : "title=<<Enter title>>" will display results with title='' (no title). If you want to have to have something not mandatory, use "title like <<Enter title>>" and enter a % at run time instead of nothing.
• If you are using a predefined runtime parameter the "Question to ask" portion of the parameter is required.

Runtime parameter options

Parameter	Syntax	Usage
Custom input	<<Enter any text string>>	Show an input field labeled with your custom text
[authorized value category], e.g. "CCODE"	CCODE>>	Select any authorized value in the selected category
biblio_framework	biblio_framework>>	Select a bibliographic framework
branches	branches>>	Select a library
categorycode	categorycode>>	Select a patron category
cn_source	cn_source>>	Select a source of classification or shelving scheme
date	date>>	Creates a date input field with a datepicker widget
itemtypes	itemtypes>>	Select an item type
list	list>>	Allows input of a list of values to be passed to an "IN" clause in the SQL

Examples

SELECT surname,firstname 
FROM borrowers 
WHERE branchcode=<<Enter patrons library|branches>> AND surname LIKE <<Enter filter FOR patron surname (% IF none)>>

SELECT * 
FROM items 
WHERE homebranch = <<Pick your branch|branches>> AND barcode LIKE <<Partial barcode value here>>

Tip:

You have to put "%" in a text box to 'leave it blank'. Otherwise, it literally looks for "" (empty string) as the value for the field.

You can get around the necessity of having users enter '%' by constructing the LIKE statement as follows:

SELECT * 
FROM items 
WHERE
    homebranch = <<Pick your branch|branches>> 
    AND barcode LIKE CONCAT( '%', <<Partial barcode value here>>, '%')

Working with Headers

Sometimes you want your report to include headers. Simple example:

select 'borrower number', 'card number', 'last name'
union all
select borrowernumber, cardnumber, surname from borrowers;

However, if you have the report setup to recur via a cron job this will cause reports to always have data so you will get a report emailed to you even if there is nothing in the data query. You can address this by making the headers conditional. To do this you need to do two things.

1) You have to give it a single row source. If you don't you could end up with as many headers as rows in the source.

2) You need to set a condition to test under which it will supply the headers. In most cases that probably means taking the entirety of the second query and making a subquery of it to test so that it only supplies the headers if the data query would supply results.

Example:

select 'borrower number', 'card number', 'last name' from (select count(*) as c from borrowers) b where c > 0
union all
select borrowernumber, cardnumber, surname from borrowers;

If you are on MariaDB 10.2 or above you can simplify it with a CTE:

with patrons as (select * from borrowers),
patron_count as (select count(*) as c from patrons)
select 'borrower number', 'card number', 'last name' from patron_count where c > 0
union all
select borrowernumber, cardnumber, surname from patrons;

Adding Row Numbers to Output

Sometimes you want your report to include row numbers. To do so, add the following to the SELECT statement:

SELECT ROW_NUMBER() OVER () AS num, [...]

Including a Random Sample in Output

Sometimes you want your report to include a random sample from a results set. To do so, add the following to the bottom of your query:

ORDER BY RAND()
LIMIT 50

Note for single branch libraries

Many of the reports below have a branch picker, which looks something like this:

...
WHERE
    homebranch = <<Pick your branch|branches>>
...

If your library only has one branch, this is unnecessary -- every patron, item, hold, checkout (issue), etc. will always be in the same branch, so there's no point in forcing the person running the query to choose.

So

SELECT * 
FROM items 
WHERE
    homebranch = <<Pick your branch|branches>> 
    and barcode like CONCAT( '%', <<Partial barcode value here>>, '%')

can be changed to

SELECT * 
FROM items 
WHERE
    barcode like CONCAT( '%', <<Partial barcode value here>>, '%')

(Note that statements in the WHERE clause must be separated by AND or OR -- when removing the first statement, in a WHERE clause, you need to remove the following AND on the next line)

Runtime Variables

Note: in 18.05 forward, duplicated run time parameters will only have a single prompt; you don't have to use the '@VARIABLE:=' syntax.

If you have duplicate parameters you need during runtime, instead of asking users to enter multiple instances of the same information, you can use variables. Unlike typical MYSQL scripts, you can not set variables prior to the SELECT statement. However, you can set variables within other statement expressions. Variables typically start with '@' and must be set with ':='. Take the following example:

SELECT * 
FROM items i
WHERE i.homebranch=<<Pick your branch|branches>>
UNION
SELECT *
FROM deleteditems di
WHERE di.homebranch=<<Pick your branch|branches>>

Instead of asking for the same library twice, you could ask for it on the first instance, and pass that value along in a variable, like this:

SELECT * 
FROM items i
WHERE i.homebranch=@TargetBranch:=<<Pick your branch|branches>>
UNION
SELECT *
FROM deleteditems di
WHERE di.homebranch=@TargetBranch

If you run your query and find that your results are not as you expect, it's possible that the part of the query which contains the run-time variables may be getting eliminated by the mysql optimizer. To get around this, you can use a union with a subquery (which won't get optimized away -- one of the few times when the quirks of the mysql optimizer works in your favor)... this will be unioned with your regular query:

SELECT
    *
FROM
    (
    SELECT
        ( @CallNumber := <<Call Number (USE % AS wildcard) >>) AS title,
        ( @StartDate := <<Start date|date>>) AS author,
        ( @EndDate := <<End date|date>>) AS publishercode,
        0 AS 'publication year',
        0 AS editionstatement,
        0 AS itemlost,
        0 AS withdrawn,
        0 AS 'item type',
        0 AS 'issue count',
        0 AS 'renewal count',
        0 AS 'local use count'
    ) AS set_variables
WHERE 0 = 1
UNION 
SELECT
    b.title,
    b.author,
    bi.publishercode,
    SUBSTR(ExtractValue(m.metadata,'//controlfield[@tag="008"]'),8,4)  AS 'publication year',
    bi.editionstatement,
    i.itemlost,
    i.withdrawn,
    i.itype AS 'item type',
    COUNT( IF( statistics.type = 'issue', statistics.itemnumber, NULL ) )  AS 'issue count',
    COUNT( IF( statistics.type = 'renew', statistics.itemnumber, NULL ) )  AS 'renewal count',
    COUNT( IF( statistics.type = 'localuse', statistics.itemnumber, NULL ) )  AS 'local use count'
FROM
    biblio b
    INNER JOIN biblioitems bi USING (biblionumber)
    INNER JOIN biblio_metadata m USING (biblionumber)
    INNER JOIN deleteditems i ON (
        b.biblionumber = i.biblionumber
        AND i.itemcallnumber LIKE @CallNumber
        AND i.dateaccessioned >= @StartDate
        AND date(i.timestamp) <=  @EndDate
    )
    LEFT JOIN statistics USING ( itemnumber )
WHERE
   date( statistics.datetime ) BETWEEN @StartDate AND @EndDate
GROUP BY itemnumber

The WHERE 0 = 1 in the where clause of the first query is used so that the rows where you are setting the variables don't display.

Custom Sort Order

The following approach uses a runtime parameter to set the sort order for a report.

Note that users will have to be prompted with the specific sort order options available to them. Better would be if the user could select from a list of options (as with authorized values), but that does not appear to be possible.

There may also be a way to move the sort order selection out of the SELECT statement so that it isn't repeated in the report output.

The ELSE clause sets the default sort order.

SELECT b.biblionumber, b.title, b.author, i.itemcallnumber, i.price, @SortOrder := << Sort ORDER (bib,title,author) >> AS c1
FROM items i
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
ORDER BY 
        CASE 
           WHEN @SortOrder= 'bib' THEN b.biblionumber
           WHEN @SortOrder= 'title' THEN b.title
           WHEN @SortOrder= 'author' THEN b.author
           ELSE i.itemcallnumber
        END

How to avoid errors

Cast datetime or timestamp to date in BETWEEN tests

Without the cast, '2016-03-14 19:01:34' is not between '2016-03-12' and '2016-03-14':

select '2016-03-14 19:01:34' between '2016-03-12' and '2016-03-14' as `is between`;
+------------+
| is between |
+------------+
|          0 |
+------------+

With the cast, '2016-03-14 19:01:34' is between '2016-03-12' and '2016-03-14':

select date ( '2016-03-14 19:01:34' ) between '2016-03-12' and '2016-03-14' as `is between`;
+------------+
| is between |
+------------+
|          1 |
+------------+

Use DATEDIFF() to subtract dates

When selecting dates that are close to each other, it looks like the minus-operator ('-') does what it's supposed to:

select DATE('2016-12-21'), DATE('2016-12-21') - DATE('2016-12-20');
+--------------------+-----------------------------------------+
| DATE('2016-12-21') | DATE('2016-12-21') - DATE('2016-12-20') |
+--------------------+-----------------------------------------+
| 2016-12-21         |                                       1 |
+--------------------+-----------------------------------------+
1 row in set (0.00 sec)

However, subtracting dates that are farther apart shows that this doesn't actually work:

select DATE('2016-12-21'), DATE('2016-12-21') - DATE('2016-05-13');
+--------------------+-----------------------------------------+
| DATE('2016-12-21') | DATE('2016-12-21') - DATE('2016-05-13') |
+--------------------+-----------------------------------------+
| 2016-12-21         |                                     708 |
+--------------------+-----------------------------------------+
1 row in set (0.00 sec)

The real difference in days, as shown using DATEDIFF():

select DATE('2016-12-21'), DATEDIFF( DATE('2016-12-21'), DATE('2016-05-13') );
+--------------------+----------------------------------------------------+
| DATE('2016-12-21') | DATEDIFF( DATE('2016-12-21'), DATE('2016-05-13') ) |
+--------------------+----------------------------------------------------+
| 2016-12-21         |                                                222 |
+--------------------+----------------------------------------------------+
1 row in set (0.00 sec)

WISHLIST

Requester	Module	Purpose of request	SQL Request	Notes
Barton Chittenden	Cataloging	Item availability		I often get requests for reports that show whether an item is available or not. As far as I can tell, this is determined by: is the item in transit? => not available is it lost? => not available is it damaged? => not available is it withdrawn? => not available is it on hold? Is the item waiting? yes => not avaliable no Is the syspref AllowItemsOnHoldCheckout set? no => not available is it set as 'not for loan'? is the syspref AllowNotForLoanOverride set? no => not available
Barton Chittenden	Cataloging	Get the combined MARCXML data for biblio and item records.		Knowing the *size* of the combined record is probably just as important; there are implicit and explicit limits to MARC data size.
Sunil	Acquisitions	To Eliminate duplicate orders.		I want to write a Sql Query to eliminate duplicate orders from selected vender. Please help me in this query.
Sunil	Acquisitions	List All Late Orders		I want to write a Sql Query to List All Late Orders, vendor-wise. Please help me in this query.
Arron Birch	Catalog	To create a report that pulls individual fields of a MARC record		I am trying to run reports of specific fields of a MARC record. Preferable I would like a general report that lets me change what field I would like to run a report for. For the current assignment I am wanting to run a report with the 300 field of the MARC record. (Have a look at the report "Biblios with like data in a subfield of a field". Is that what you want?)
Rachel Hollis	Catalog	Mismatches between biblioitem 942 and item 952		We think there is value in a report that identifies (by title, call number and biblio ID) records that have item mismatches, specific to our situation are 942 subfields 2 & c and 952 subfields 2 & y. Our Koha 3.01 biblio item loans are controlled by the 942. Additionally we have libraries that use Dewey, LC and locally developed classification schemes. Administration and System Preferences allow for static and variable data that can get mismatched. Check the following report to see, if that is what you need. Mismatches between 2 fields and 2 subfields: This report displays the Biblionumber, Title, and Author of all Biblios that Do Not Match 2 sets of fields and subfields. SELECT CONCAT( '<a href=\"/cgi-bin/koha/catalogue/detail.plbiblionumber=', biblio.biblionumber, '\">', biblio.biblionumber,'</a>' ) AS biblionumber, biblio.title, biblio.author FROM biblioitems JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber) WHERE ExtractValue(marcxml, "//datafield[@tag=<<1st Set Field 1 (XXX)>>]/subfield[@code=<<Subfield(Y)>>]") AND ExtractValue(marcxml, "//datafield[@tag=<<1st Set Field 2 (XXX)>>]/subfield[@code=<<Subfield(Y)>>]") NOT LIKE ExtractValue(marcxml, "//datafield[@tag=<<2nd Set Field 1(XXX)>>]/subfield[@code=<<Subfield(Y)>>]") AND ExtractValue(marcxml, "//datafield[@tag=<<2nd Set Field 2(XXX)>>]/subfield[@code=<<Subfield(Y)>>]")
Joe Tholen	Circulation	List items not circulated in last year, by shelf location, using old_issues and issues		For migrated libraries to weed with.
Scotty Zollars	Cataloging	List all records with NULL in the source of acquistion field in the item record within a date range.		SELECT barcode, homebranch, title, dateaccessioned, booksellerid FROM items, biblio WHERE items.biblionumber = biblio.biblionumber AND items.dateaccessioned BETWEEN <<Enter date from>> AND <<Enter date to>> AND booksellerid IS NULL ORDER BY barcode
Scotty Zollars	Circulation	List interlibrary loan materials check out to other libraries, by day.		For ILL record keeping
Susan Bennett	Catalog	I need to eliminate materials that are on the holds shelf waiting for patron pick up from the following SQL. What is the flag in the record?		SELECT items.barcode, items.homebranch, items.itemcallnumber, items.holdingbranch, items.location, items.ccode, items.onloan, biblio.author, biblio.title FROM items LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) WHERE items.holdingbranch="GW" AND items.homebranch<>items.holdingbranch AND items.onloan IS NULL ORDER BY items.holdingbranch asc
Scotty Zollars	Circulation	We are only one branch. Our interlibrary loan patrons are community patrons. They have the last name of ILL and the first name of the library, for example Erie Public Library. I need a list of interlibrary loan materials check out to other libraries, by month. i have the following donated so far.		SELECT monthname(datesent) month,COUNT(*) FROM branchtransfers where frombranch="MMM" and YEAR(datesent)=YEAR(NOW())-1 GROUP BY month ORDER by month(datesent)
Rachel Hollis	Circulation	Compare number of items owned by library with number circulating	Count of items currently checked out	SELECT COUNT( itemnumber ) FROM issues
Charlotte Stock	Catalog	List all DVD records with Standard Number at 024 #a	Data needed for export	I can't find a way to export a list of DVDs showing only their identifier (ie. Standard number at 024 #a). The List tool doesn't allow me to specify the identifier needed, so I presume a report is the way forward?
Séverine Queune	Acquisitions	Informations about orders linked to a suggestion		I'd like have informations about an order linked to a suggestion, especially the different dates existing in the different tables (suggestions - aqorders - aqbasket) to calculate the DATEDIFF between suggestions.suggesteddate, aqorders.entrydate, aqbasket.closedate and aqorders.datereceived but I can't found out how to link these datas ! All I found is to link them using biblionumber but it's not reliable when there are several orders. Is there someone to help me ?
Séverine Queune - 2019-10-11	Saved reports	Allow resqust on a list of barcode		I'd like to open my "where" condition to allow people to request on several barcodes, so they won't have to edit the request itself and save the list in it. I tried several things, like WHERE barcode IN (REPLACE(,' ', '\',\'')) for a liste of barcode separated by a space but none of them worked. Does someone have an idea if I can do it ?