Table of Contents
The guided report wizard will walk you through a six step process to generate a report.
Step 1: Choose the module you want to report on. This will determine what tables and fields are available for you to query.
Step 2: Choose a report type. For now, Tabular is the only option available.
Step 3: Choose the fields you want in your report. You can select multiple fields and add them all at once by using CTRL+Click on each item you want to add before clicking the Add button.
Step 4: Choose any limits you might want to apply to your report (such as item types or branches). If you don't want to apply any limits, simply click Next instead of making an option.
Step 5: Perform math functions. If you don't want to do any calculations, simply click Next instead of making an option.
Step 6: Choose data order. If you want the data to print out in the order it's found in the database, simply click Finish.
When you are finished you will be presented with the SQL generated by the report wizard. From here you can choose to save the report by clicking 'Save' or copy the SQL and make edits to it by hand.
If you choose to save the report you will be asked to name your report and enter any notes regarding it.
Once your report is saved it will appear on the 'Use Saved' page with all other saved reports.
From here you can make edits, run the report, or schedule a time to have the report run.
In addition to the report wizard, you have the option to write your own queries using SQL. To find reports written by other Koha users, visit the Koha Wiki: http://wiki.koha-community.org/wiki/SQL_Reports_Library. You can also find your database structure in /installer/data/mysql/kohastructure.sql or online at: http://git.koha-community.org/cgi-bin/gitweb.cgi?p=koha.git;a=blob_plain;f=installer/data/mysql/kohastructure.sql;hb=HEAD.
To add your query, simply fill in the form presented
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.
There is a limit of 10,000 records put on SQL statements entered in Koha. To get around this you want to add 'LIMIT 100000' to the end of your SQL statement (or any other number above 10,000.
There is a specific syntax that Koha will understand as 'ask for values when running the report'. The syntax is <<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.
The authorized_value can be omitted if not applicable. If it contains an authorized value category, or branches or itemtype or categorycode, a list with the Koha authorized values will be displayed instead of a free field 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
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>>
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.
The only supported dropdowns at this time are Branches (branches), Item Types (itemtypes) and Patron Categories (categorycode).