Submitted by jovana on Thu, 21/12/2017 - 10:55
Is there a way to filter my custom SQL reports before they run?

Absolutely!  Instead of specifying a value in the WHERE section of a SQL statement, (e.g. WHERE status=’7’), you can insert a parameter by encasing a question or statement in <<  >> brackets, (e.g. WHERE status=<<Please, enter a status number>>), which will prompt you for a value each time you run the report. 

Please, enter a status number: [Run the report]

In some cases, you can choose a value from a drop-down menu, instead of typing in a value. To do so, you must specify a category from the Koha list of authorized values (e.g. LOC, DAMAGED, etc), which can be found under the Basic Parameters heading in Koha Administration. Alternatively, you can also use one of the following:  branches, itemtypes, or categorycode (i.e. patron type). These categories must be placed next to the parameter using the pipe or bar character “|” (e.g. WHERE branchcode=<<Enter patron’s library|branches>>).

Enter patron's library: [Electronic] [Run the report]

REMEMBER that an empty field will still search for a value. If you truly want to ignore the parameter being asked for in the report, you must place a % symbol in the textbox. 

Enter filter FOR patron surname (% IF none): [%] : [Run the report]

For a guided tour through this process, see the YouTube Tutorial "Using Runtime Parameters in Koha 3.6 Reports". Despite the title, this information is true for versions of Koha 3.2 and higher. 

FAQ Category