1 .. include:: images.rst
6 Reports in Koha are a way to gather data. Reports are used to generate
7 statistics, member lists, shelving lists, or any list of data in your
10 - *Get there:* More > Reports
12 .. _custom-reports-label:
15 -----------------------------------
17 Koha's data is stored in a MySQL database which means that librarians
18 can generate nearly any report they would like by either using the
19 :ref:`guided reports wizard <guided-report-wizard-label>` or writing their own
20 :ref:`SQL query <report-from-sql-label>`.
22 .. _add-custom-report-label:
25 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
27 .. _guided-report-wizard-label:
30 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
32 The guided report wizard will walk you through a six step process to
35 Step 1: Choose the module you want to report on. This will determine
36 what tables and fields are available for you to query.
38 'Report is public' should be left to the default of 'No' in most cases
39 especially if the report contains patron or other sensitive information.
40 A public report can be accessed using the :ref:`JSON reports services
41 <json-reports-services-label>` by anyone and without authentication.
47 If your system administrator has set up memcache on your server you
48 might see one more option for the Cache expiry. This is related to
49 your public reports. If you make the report public then it's
50 constantly running and will cause a large load on your system.
51 Setting this value prevents that.
55 Step 2: Choose a report type. For now, Tabular is the only option
60 Step 3: Choose the fields you want in your report. You can select
61 multiple fields and add them all at once by using CTRL+click on each
62 item you want to add before clicking the Add button.
66 Step 4: Choose any limits you might want to apply to your report (such
67 as item types or branches). If you don't want to apply any limits,
68 simply click 'Next' instead of choosing an option.
72 Step 5: Perform math functions. If you don't want to do any
73 calculations, simply click 'Next' instead of choosing an option.
77 Step 6: Choose data order. If you want the data to print out in the
78 order it's found in the database, simply click 'Finish'.
82 When you are finished you will be presented with the SQL generated by
83 the report wizard. From here you can choose to save the report by
84 clicking 'Save' or copy the SQL and make edits to it by hand.
88 If you choose to save the report you will be asked to name your report,
89 sort it in to groups and subgroups and enter any notes regarding it.
93 Once your report is saved it will appear on the 'Use saved' page with
94 all other saved reports.
98 From here you can make edits, run the report, or schedule a time to have
99 the report run. To find the report you created you can sort by any of
100 the columns by clicking the on the column header (creation date is the
101 best bet for finding the report you just added). You can also filter
102 your results using the filter menu on the left or use the tabs to find
103 reports based on your custom groups.
105 .. _report-from-sql-label:
108 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
110 In addition to the report wizard, you have the option to write your own
111 queries using SQL. To find reports written by other Koha users, visit
112 the Koha wiki: http://wiki.koha-community.org/wiki/SQL_Reports_Library.
113 You can also find your database structure in
114 /installer/data/mysql/kohastructure.sql or online at:
115 `http://schema.koha-community.org <http://schema.koha-community.org/>`__.
117 To add your query, click the link to 'Create from SQL' on the main
118 reports module or the 'New report' button at the top of the 'Saved
123 Fill in the form presented
127 - The 'Name' is what will appear on the Saved Reports page to help you
128 identify the report later. It will also be searchable using the
129 filters found the left of the Saved Reports page.
131 - You can use the 'Report group' to organize your reports so that you
132 can easily filter reports by groups. Report groups are set in the
133 `REPORT\_GROUP <#reportgroup>`__ authorized value category or can be
134 added on the fly when creating the report by choosing the 'or create'
139 Report groups set up in the authorised value category
140 need to have unique authorised values and descriptions.
144 If you're adding a report group on the fly, remember that you
145 code should be fewer than 10 characters and should not include
146 special characters or spaces.
148 - You can use 'Report subgroup' to further organize your reports so
149 that you can easily filter reports by groups and subgroups. Report
150 subgroups are set in the `REPORT\_SUBGROUP <#reportsubgroup>`__
151 authorized value category or can be added on the fly when creating
152 the report by choosing the 'or create' radio button. Report subgroups
153 are set up with unique values in 'Authorised value', and 'Description'.
154 The 'Description (OPAC)'' field needs to contain the authorised value for
155 the report group that the subgroup falls under.
161 If you're adding a report subgroup on the fly, remember that
162 you code should be fewer than 10 characters and should not
163 include special characters or spaces.
165 - 'Report is public' should be left to the default of 'No' in most cases
166 especially if the report contains patron or other sensitive information.
167 A public report can be accessed using the :ref:`JSON reports services
168 <json-reports-services-label>` by anyone and without authentication.
170 - 'Notes' will also appear on the saved reports page, this can be used
171 to provide more details about the report or tips on how to enter
174 - The type should always be 'Tabular' at this time since the other
175 formats have not been implemented
177 - In the 'SQL' box you will type or paste the SQL for the report
181 If your system administrator has set up memcache on your server you
182 might see one more option for the Cache expiry. This is related to
183 your public reports. If you make the report public then it's
184 constantly running and will cause a large load on your system.
185 Setting this value prevents that.
189 Once everything is entered click the 'Save report' button and you'll be
190 presented with options to run it. Once a report is saved you do not have
191 to recreate it you can simply find it on the Saved Reports page and
192 :ref:`run <running-custom-reports-label>` or :ref:`edit <edit-custom-reports-label>` it.
194 .. _report-writing-tips-label:
197 '''''''''''''''''''''''''''''''''''''
199 **Runtime parameters**
202 If you feel that your report might be too resource intensive you
203 might want to consider using runtime parameters to your query.
204 Runtime parameters basically make a filter appear before the report
205 is run to save your system resources.
207 There is a specific syntax that Koha will understand as 'ask for
208 values when running the report'. The syntax is <<Question to
209 ask\|authorized\_value>>.
211 - The << and >> are just delimiters. You must put << at the
212 beginning and >> at the end of your parameter
214 - The 'Question to ask' will be displayed on the left of the string
217 - The authorized\_value can be omitted if not applicable. If it
218 contains an authorized value category, 'branches', 'itemtype',
219 'categorycode', 'biblio\_framework', a list with the Koha authorized
220 values will be displayed instead of a free field.
224 You can have more than one parameter in a given SQL query.
228 You have to put "%" in a text box to 'leave it blank'. Otherwise,
229 it literally looks for "" (empty string) as the value for the
231 For example entering nothing for: "title=<<Enter title>>" will
232 display results with title='' (no title).
233 If you want to have to have something not mandatory, use
234 "title like <<Enter title>>" and enter a % at run time instead
239 To generate a date picker calendar to the right of the field when
240 running a report you can use the 'date' keyword like this:
245 List of parameters that can be used in runtime parameters
247 +-------------------------+---------------------------------------------------+------------------------------+
248 | Parameter | What the user sees | What gets inserted in query |
249 +-------------------------+---------------------------------------------------+------------------------------+
250 | date | date picker | validly formatted date |
251 | branches | drop down of branch names | branch code |
252 | itemtypes | drop down of item type names | item type |
253 | categorycode | drop down of patron category descriptions | borrower category code |
254 | biblio\_framework | drop down of MARC bibliographic frameworks | framework code |
255 | (auth-value-category) | drop down of auth-value descriptions in category | authorized value |
256 | (nothing) | text box | entered text |
257 +-------------------------+---------------------------------------------------+------------------------------+
261 - SELECT surname, firstname FROM borrowers WHERE branchcode=<<Enter
262 patron's library\|branches>> AND surname like <<Enter filter for
263 patron surname (% if none)>>
265 - SELECT \* FROM items WHERE homebranch = <<Pick your
266 branch\|branches>> and barcode like <<Partial barcode value here>>
268 - SELECT title, author FROM biblio WHERE frameworkcode=<<Enter the
269 frameworkcode\|biblio\_framework>>
272 .. ****************************************
273 .. ************** TODO **************
274 .. ****************************************
278 .. It is possible to query the MARC records with the ExtractValue function.
282 .. **Runtime variables**
284 .. **See more results**
286 .. There is a limit of 10,000 records put on SQL statements entered in
287 Koha. To get around this you want to add 'LIMIT 100000' to the end
288 of your SQL statement (or any other number above 10,000).
291 .. _mana-SQL-report-label:
294 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
296 In the "Create report from SQL", you can search Mana KB for pre-made reports
297 by clicking on "New report" and choosing "New SQL from Mana".
303 This option will only appear if you've :ref:`configured Mana KB
304 <share-with-mana-kb-label>` in the administration module.
306 You will be prompted to enter keywords to search the knowledge base.
310 In the search results, you will see
312 - the details of the report (name, notes and type)
314 - how many people have used this entry (# of users)
316 - when it was used for the last time (last import)
318 - additional comments made by other Koha users (comments)
322 .. What does the orange mean??
324 Click on "Import" to import a copy of the report in your own saved reports.
326 You can then edit it, duplicate it, delete it, run it as you would any of
329 .. _duplicate-report-label:
332 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
334 Reports can also be added by duplicating an existing report. Visit the
335 'Saved reports' page to see all of the reports listed on your system
340 To the right of every report there is an 'Actions' pull down. Clicking
341 that and choose 'Duplicate' to use an existing report as the basis for
342 your new report. That will populate the new report form with the
343 existing SQL for easy editing and resaving.
345 .. _edit-custom-reports-label:
348 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
350 Every report can be edited from the reports lists. To see the list of
351 reports already stored in Koha, click 'Use saved.'
355 To find the report you'd like to edit you can sort by any of the columns
356 by clicking the on the column header. You can also filter your results
357 using the filter menu on the left or use the tabs to find reports based
358 on your custom groups.
360 From this list you can edit any custom report by clicking 'Actions' to
361 the right of the report and choosing 'Edit' from the menu that appears.
365 The form to edit the report will appear.
369 .. _running-custom-reports-label:
371 Running custom reports
372 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
374 Once custom reports are saved to Koha, you can run them by going to the
375 Saved Reports page and clicking the 'Actions' button to the right of the
376 report and choosing 'Run'.
380 When you report runs you will either be asked for some values
384 or you will see the results right away
388 From the results you can choose to rerun the report by clicking 'Run
389 report' at the top, edit the report by clicking the 'Edit' button or
390 starting over and creating a new report by using the 'New' button. You
391 can also download your results by choosing a file type at the bottom of
392 the results next to the 'Download the report' label and clicking
397 A comma separated text file is a CSV file and it can be opened by
398 any spreadsheet application.
400 .. _send-itemnumbers-to-batch-item-modification-label:
402 Send itemnumbers to batch item modification
403 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
405 After running a report that contains itemnumbers the list of numbers can be imported
406 directly into :ref:`Batch item modification` by clicking the batch modify button
407 in the report results.
411 .. _statistics-reports-label:
414 --------------------------------------
416 Statistic reports will show you counts and sums. These reports are all
417 about numbers and statistics, for reports that return more detailed
418 data, use the :ref:`guided report wizard`. These
419 reports are limited in what data they can look at, so it's often
420 recommended to use :ref:`custom reports <custom-reports-label>` for official end
421 of the year statistics.
423 .. _acquisitions-statistics-label:
425 Acquisitions statistics
426 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
430 These reports are limited in what data they can look at, so it's
431 often recommended to use :ref:`custom reports <custom-reports-label>` for
432 official end of the year statistics.
434 Using the form provided, choose which value you would like to appear in
435 the column and which will appear in the row.
439 If you choose to output to the browser your results will print to the
444 You can also choose to export to a file that you can manipulate to your
447 When generating your report, note that you get to choose between
448 counting or summing the values.
452 Choosing amount will change your results to appear as the sum of the
457 .. _patron-statistics-label:
460 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
464 These reports are limited in what data they can look at, so it's
465 often recommended to use :ref:`custom reports <custom-reports-label>` for
466 official end of the year statistics.
468 Using the form provided, choose which value you would like to appear in
469 the column and which will appear in the row.
473 If you choose to output to the browser your results will print to the
478 Based on your selections, you may see some query information above your
479 results table. You can also choose to export to a file that you can
480 manipulate to your needs.
482 .. _catalog-statistics-label:
485 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
489 These reports are limited in what data they can look at, so it's
490 often recommended to use :ref:`custom reports <custom-reports-label>` for
491 official end of the year statistics.
493 Using the form provided, choose which value you would like to appear in
494 the column and which will appear in the row.
498 If you choose to output to the browser your results will print to the
503 You can also choose to export to a file that you can manipulate to your
506 .. _circulation-statistics-label:
508 Circulation statistics
509 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
513 These reports are limited in what data they can look at, so it's
514 often recommended to use :ref:`custom reports <custom-reports-label>` for
515 official end of the year statistics.
517 Using the form provided, choose which value you would like to appear in
518 the column and which will appear in the row.
522 If you choose to output to the browser your results will print to the
527 You can also choose to export to a file that you can manipulate to your
532 To get a complete picture of your monthly or daily circulation, you
533 can run the report twice, once for 'Type' of 'Checkout' and again
536 This report uses 'Period,' or date, filtering that allows you to
537 limit to a month by simply selecting the first day of the first
538 month through the first day of the next month. For example, 10/1 to
539 11/1 to find statistics for the month of October.
541 - To find daily statistics, set your date range.</br> Example: "I
542 want circulation data starting with date XXX up to, but not
543 including, date XXX."
545 - For a whole month, an example range would be: 11/01/2009 to
548 - For a whole year, an example range would be: 01/01/2009 to
551 - For a single day, an example would be: 11/15/2009 to 11/16/2009
552 to find what circulated on the 15th
554 .. _tracking-in-house-use-label:
556 Tracking in house use
557 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
559 Using the Circulation statistics reporting wizard you can run reports on
560 in house usage of items simply by choosing 'Local use' from the 'Type'
565 .. _serials-statistics-label:
568 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
572 These reports are limited in what data they can look at, so it's
573 often recommended to use :ref:`custom reports <custom-reports-label>` for
574 official end of the year statistics.
576 Using the form provided, choose how you would like to list the serials
581 If you choose to output to the browser your results will print to the
586 You can also choose to export to a file that you can manipulate to your
589 .. _holds-statistics-label:
592 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
596 These reports are limited in what data they can look at, so it's
597 often recommended to use :ref:`custom reports <custom-reports-label>` for
598 official end of the year statistics.
600 Using the form provided you can see statistics for holds placed, filled,
601 cancelled and more at your library. From the form choose what value you
602 want to display in the column and what value to show in the row. You can
603 also choose from the filters on the far right of the form.
607 If you choose to output to the browser your results will print to the
612 You can also choose to export to a file that you can manipulate to your
615 .. _patrons-with-the-most-checkouts-label:
617 Patrons with the most checkouts
618 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
620 This report will simply list the patrons who have the most checkouts.
624 If you choose to output to the browser your results will print to the
629 You can also choose to export to a file that you can manipulate to your
632 .. _most-circulated-items-label:
634 Most circulated items
635 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
637 This report will simply list the items that have the been checked out
642 If you choose to output to the browser your results will print to the
647 You can also choose to export to a file that you can manipulate to your
650 .. _patrons-with-no-checkouts-label:
652 Patrons with no checkouts
653 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
655 This report will list for you the patrons in your system who haven't
656 checked any items out.
660 If you choose to output to the browser your results will print to the
665 You can also choose to export to a file that you can manipulate to your
668 .. _items-with-no-checkouts-label:
670 Items with no checkouts
671 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
673 This report will list items in your collection that have never been
678 If you choose to output to the browser your results will print to the
683 You can also choose to export to a file that you can manipulate to your
686 .. _catalog-by-item-type-label:
689 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
691 This report will list the total number of items of each item type per
696 If you choose to output to the browser your results will print to the
701 You can also choose to export to a file that you can manipulate to your
704 .. _lost-items-label:
707 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
709 This report will allow you to generate a list of items that have been
710 marked as Lost within the system
714 .. _average-loan-time-label:
717 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
719 This report will list the average time items are out on loan based on
720 the criteria you enter:
724 If you choose to output to the browser your results will print to the
729 You can also choose to export to a file that you can manipulate to your
732 .. _report-dictionary-label:
735 ----------------------------------
737 The report dictionary is a way to pre-define common filters you'd like
738 to apply to your reports. This is a good way to add in filters that the
739 report wizard doesn't include by default. To add a new definition, or
740 filter, click 'New definition' on the reports dictionary page and follow
743 Step 1: Name the definition and provide a description if necessary
747 Step 2: Choose the module that the will be queried.
751 Step 3: Choose columns to query from the tables presented.
755 Step 4: Choose the value(s) from the field(s). These will be
756 automatically populated with options available in your database.
760 Confirm your selections to save the definition.
764 Your definitions will all appear on the Reports Dictionary page
768 Then when generating reports on the module you created the value for you
769 will see an option to limit by the definition at the bottom of the usual