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).
290 .. _duplicate-report-label:
293 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
295 Reports can also be added by duplicating an existing report. Visit the
296 'Saved reports' page to see all of the reports listed on your system
301 To the right of every report there is an 'Actions' pull down. Clicking
302 that and choose 'Duplicate' to use an existing report as the basis for
303 your new report. That will populate the new report form with the
304 existing SQL for easy editing and resaving.
306 .. _edit-custom-reports-label:
309 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
311 Every report can be edited from the reports lists. To see the list of
312 reports already stored in Koha, click 'Use saved.'
316 To find the report you'd like to edit you can sort by any of the columns
317 by clicking the on the column header. You can also filter your results
318 using the filter menu on the left or use the tabs to find reports based
319 on your custom groups.
321 From this list you can edit any custom report by clicking 'Actions' to
322 the right of the report and choosing 'Edit' from the menu that appears.
326 The form to edit the report will appear.
330 .. _running-custom-reports-label:
332 Running custom reports
333 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
335 Once custom reports are saved to Koha, you can run them by going to the
336 Saved Reports page and clicking the 'Actions' button to the right of the
337 report and choosing 'Run'.
341 When you report runs you will either be asked for some values
345 or you will see the results right away
349 From the results you can choose to rerun the report by clicking 'Run
350 report' at the top, edit the report by clicking the 'Edit' button or
351 starting over and creating a new report by using the 'New' button. You
352 can also download your results by choosing a file type at the bottom of
353 the results next to the 'Download the report' label and clicking
358 A comma separated text file is a CSV file and it can be opened by
359 any spreadsheet application.
361 .. _send-itemnumbers-to-batch-item-modification-label:
363 Send itemnumbers to batch item modification
364 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
366 After running a report that contains itemnumbers the list of numbers can be imported
367 directly into :ref:`Batch item modification` by clicking the batch modify button
368 in the report results.
372 .. _statistics-reports-label:
375 --------------------------------------
377 Statistic reports will show you counts and sums. These reports are all
378 about numbers and statistics, for reports that return more detailed
379 data, use the :ref:`guided report wizard`. These
380 reports are limited in what data they can look at, so it's often
381 recommended to use :ref:`custom reports <custom-reports-label>` for official end
382 of the year statistics.
384 .. _acquisitions-statistics-label:
386 Acquisitions statistics
387 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
391 These reports are limited in what data they can look at, so it's
392 often recommended to use :ref:`custom reports <custom-reports-label>` for
393 official end of the year statistics.
395 Using the form provided, choose which value you would like to appear in
396 the column and which will appear in the row.
400 If you choose to output to the browser your results will print to the
405 You can also choose to export to a file that you can manipulate to your
408 When generating your report, note that you get to choose between
409 counting or summing the values.
413 Choosing amount will change your results to appear as the sum of the
418 .. _patron-statistics-label:
421 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
425 These reports are limited in what data they can look at, so it's
426 often recommended to use :ref:`custom reports <custom-reports-label>` for
427 official end of the year statistics.
429 Using the form provided, choose which value you would like to appear in
430 the column and which will appear in the row.
434 If you choose to output to the browser your results will print to the
439 Based on your selections, you may see some query information above your
440 results table. You can also choose to export to a file that you can
441 manipulate to your needs.
443 .. _catalog-statistics-label:
446 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
450 These reports are limited in what data they can look at, so it's
451 often recommended to use :ref:`custom reports <custom-reports-label>` for
452 official end of the year statistics.
454 Using the form provided, choose which value you would like to appear in
455 the column and which will appear in the row.
459 If you choose to output to the browser your results will print to the
464 You can also choose to export to a file that you can manipulate to your
467 .. _circulation-statistics-label:
469 Circulation statistics
470 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
474 These reports are limited in what data they can look at, so it's
475 often recommended to use :ref:`custom reports <custom-reports-label>` for
476 official end of the year statistics.
478 Using the form provided, choose which value you would like to appear in
479 the column and which will appear in the row.
483 If you choose to output to the browser your results will print to the
488 You can also choose to export to a file that you can manipulate to your
493 To get a complete picture of your monthly or daily circulation, you
494 can run the report twice, once for 'Type' of 'Checkout' and again
497 This report uses 'Period,' or date, filtering that allows you to
498 limit to a month by simply selecting the first day of the first
499 month through the first day of the next month. For example, 10/1 to
500 11/1 to find statistics for the month of October.
502 - To find daily statistics, set your date range.</br> Example: "I
503 want circulation data starting with date XXX up to, but not
504 including, date XXX."
506 - For a whole month, an example range would be: 11/01/2009 to
509 - For a whole year, an example range would be: 01/01/2009 to
512 - For a single day, an example would be: 11/15/2009 to 11/16/2009
513 to find what circulated on the 15th
515 .. _tracking-in-house-use-label:
517 Tracking in house use
518 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
520 Using the Circulation statistics reporting wizard you can run reports on
521 in house usage of items simply by choosing 'Local use' from the 'Type'
526 .. _serials-statistics-label:
529 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
533 These reports are limited in what data they can look at, so it's
534 often recommended to use :ref:`custom reports <custom-reports-label>` for
535 official end of the year statistics.
537 Using the form provided, choose how you would like to list the serials
542 If you choose to output to the browser your results will print to the
547 You can also choose to export to a file that you can manipulate to your
550 .. _holds-statistics-label:
553 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
557 These reports are limited in what data they can look at, so it's
558 often recommended to use :ref:`custom reports <custom-reports-label>` for
559 official end of the year statistics.
561 Using the form provided you can see statistics for holds placed, filled,
562 cancelled and more at your library. From the form choose what value you
563 want to display in the column and what value to show in the row. You can
564 also choose from the filters on the far right of the form.
568 If you choose to output to the browser your results will print to the
573 You can also choose to export to a file that you can manipulate to your
576 .. _patrons-with-the-most-checkouts-label:
578 Patrons with the most checkouts
579 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
581 This report will simply list the patrons who have the most checkouts.
585 If you choose to output to the browser your results will print to the
590 You can also choose to export to a file that you can manipulate to your
593 .. _most-circulated-items-label:
595 Most circulated items
596 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
598 This report will simply list the items that have the been checked out
603 If you choose to output to the browser your results will print to the
608 You can also choose to export to a file that you can manipulate to your
611 .. _patrons-with-no-checkouts-label:
613 Patrons with no checkouts
614 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
616 This report will list for you the patrons in your system who haven't
617 checked any items out.
621 If you choose to output to the browser your results will print to the
626 You can also choose to export to a file that you can manipulate to your
629 .. _items-with-no-checkouts-label:
631 Items with no checkouts
632 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
634 This report will list items in your collection that have never been
639 If you choose to output to the browser your results will print to the
644 You can also choose to export to a file that you can manipulate to your
647 .. _catalog-by-item-type-label:
650 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
652 This report will list the total number of items of each item type per
657 If you choose to output to the browser your results will print to the
662 You can also choose to export to a file that you can manipulate to your
665 .. _lost-items-label:
668 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
670 This report will allow you to generate a list of items that have been
671 marked as Lost within the system
675 .. _average-loan-time-label:
678 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
680 This report will list the average time items are out on loan based on
681 the criteria you enter:
685 If you choose to output to the browser your results will print to the
690 You can also choose to export to a file that you can manipulate to your
693 .. _report-dictionary-label:
696 ----------------------------------
698 The report dictionary is a way to pre-define common filters you'd like
699 to apply to your reports. This is a good way to add in filters that the
700 report wizard doesn't include by default. To add a new definition, or
701 filter, click 'New definition' on the reports dictionary page and follow
704 Step 1: Name the definition and provide a description if necessary
708 Step 2: Choose the module that the will be queried.
712 Step 3: Choose columns to query from the tables presented.
716 Step 4: Choose the value(s) from the field(s). These will be
717 automatically populated with options available in your database.
721 Confirm your selections to save the definition.
725 Your definitions will all appear on the Reports Dictionary page
729 Then when generating reports on the module you created the value for you
730 will see an option to limit by the definition at the bottom of the usual