1 .. include:: images.rst
8 Reports in Koha are a way to gather data. Reports are used to generate
9 statistics, member lists, shelving lists, or any list of data in your
12 - *Get there:* More > Reports
14 .. _custom-reports-label:
17 -----------------------------------
19 Koha's data is stored in a MySQL database which means that librarians
20 can generate nearly any report they would like by either using the
21 :ref:`guided reports wizard <guided-report-wizard-label>` or writing their own
22 :ref:`SQL query <report-from-sql-label>`.
24 .. _add-custom-report-label:
27 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
29 .. _guided-report-wizard-label:
32 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
34 The guided report wizard will walk you through a six step process to
37 Step 1: Choose the module you want to report on. This will determine
38 what tables and fields are available for you to query.
40 'Report is public' should be left to the default of 'No' in most cases
41 especially if the report contains patron or other sensitive information.
42 A public report can be accessed using the
43 :ref:`JSON reports services <json-reports-services-label>` by anyone and without authentication.
49 If your system administrator has set up memcache on your server you
50 might see one more option for the Cache expiry. This is related to
51 your public reports. If you make the report public then it's
52 constantly running and will cause a large load on your system.
53 Setting this value prevents that.
57 Step 2: Choose a report type. For now, Tabular is the only option
62 Step 3: Choose the fields you want in your report. You can select
63 multiple fields and add them all at once by using CTRL+click on each
64 item you want to add before clicking the Add button.
68 Step 4: Choose any limits you might want to apply to your report (such
69 as item types or branches). If you don't want to apply any limits,
70 simply click 'Next' instead of choosing an option.
74 Step 5: Perform math functions. If you don't want to do any
75 calculations, simply click 'Next' instead of choosing an option.
79 Step 6: Choose data order. If you want the data to print out in the
80 order it's found in the database, simply click 'Finish'.
84 When you are finished you will be presented with the SQL generated by
85 the report wizard. From here you can choose to save the report by
86 clicking 'Finish' or copy the SQL and make edits to it by hand.
90 If you choose to save the report you will be asked to name your report,
91 sort it in to groups and subgroups and enter any notes regarding it.
95 Once your report is saved it will appear on the 'Use saved' page with
96 all other saved reports.
100 From here you can make edits, run the report, or schedule a time to have
101 the report run. To find the report you created you can sort by any of
102 the columns by clicking the on the column header (creation date is the
103 best bet for finding the report you just added). You can also filter
104 your results using the filter menu on the left or use the tabs to find
105 reports based on your custom groups.
107 .. _report-from-sql-label:
110 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
112 In addition to the report wizard, you have the option to write your own
113 queries using SQL. To find reports written by other Koha users, visit
114 the Koha wiki: http://wiki.koha-community.org/wiki/SQL_Reports_Library.
115 You can also find your database structure in
116 /installer/data/mysql/kohastructure.sql or online at:
117 `http://schema.koha-community.org <http://schema.koha-community.org/>`__.
119 To add your query, click the link to 'Create from SQL' on the main
120 reports module or the 'New report' button at the top of the 'Saved
125 Fill in the form presented
129 - The 'Name' is what will appear on the Saved Reports page to help you
130 identify the report later. It will also be searchable using the
131 filters found the left of the Saved Reports page.
133 - You can use the 'Report group' to organize your reports so that you
134 can easily filter reports by groups. Report groups are set in the
135 `REPORT\_GROUP <#reportgroup>`__ authorized value category or can be
136 added on the fly when creating the report by choosing the 'or create'
141 Report groups set up in the authorised value category
142 need to have unique authorised values and descriptions.
146 If you're adding a report group on the fly, remember that you
147 code should be fewer than 10 characters and should not include
148 special characters or spaces.
150 - You can use 'Report subgroup' to further organize your reports so
151 that you can easily filter reports by groups and subgroups. Report
152 subgroups are set in the `REPORT\_SUBGROUP <#reportsubgroup>`__
153 authorized value category or can be added on the fly when creating
154 the report by choosing the 'or create' radio button. Report subgroups
155 are set up with unique values in 'Authorised value', and 'Description'.
156 The 'Description (OPAC)'' field needs to contain the authorised value for
157 the report group that the subgroup falls under.
163 If you're adding a report subgroup on the fly, remember that
164 you code should be fewer than 10 characters and should not
165 include special characters or spaces.
167 - 'Report is public' should be left to the default of 'No' in most cases
168 especially if the report contains patron or other sensitive information.
169 A public report can be accessed using the
170 :ref:`JSON reports services <json-reports-services-label>` by anyone and without authentication.
172 - 'Notes' will also appear on the saved reports page, this can be used
173 to provide more details about the report or tips on how to enter
176 - The type should always be 'Tabular' at this time since the other
177 formats have not been implemented
179 - In the 'SQL' box you will type or paste the SQL for the report
183 If your system administrator has set up memcache on your server you
184 might see one more option for the Cache expiry. This is related to
185 your public reports. If you make the report public then it's
186 constantly running and will cause a large load on your system.
187 Setting this value prevents that.
191 Once everything is entered click the 'Save report' button and you'll be
192 presented with options to run it. Once a report is saved you do not have
193 to recreate it you can simply find it on the Saved Reports page and
194 :ref:`run <running-custom-reports-label>` or :ref:`edit <edit-custom-reports-label>` it.
196 .. _report-writing-tips-label:
199 '''''''''''''''''''''''''''''''''''''
201 **Runtime parameters**
203 If you feel that your report might be too resource intensive you
204 might want to consider using runtime parameters to your query.
205 Runtime parameters basically make a filter appear before the report
206 is run to save your system resources.
208 There is a specific syntax that Koha will understand as 'ask for
209 values when running the report'. The syntax is <<Question to
210 ask\|authorized\_value>>.
212 - The << and >> are just delimiters. You must put << at the
213 beginning and >> at the end of your parameter
215 - The 'Question to ask' will be displayed on the left of the string
218 - The authorized\_value can be omitted if not applicable. If it
219 contains an authorized value category, 'branches', 'itemtype',
220 'categorycode', 'biblio\_framework', a list with the Koha authorized
221 values will be displayed instead of a free field.
225 You can have more than one parameter in a given SQL query.
229 You have to put "%" in a text box to 'leave it blank'. Otherwise,
230 it literally looks for "" (empty string) as the value for the
232 For example entering nothing for: "title=<<Enter title>>" will
233 display results with title='' (no title).
234 If you want to have to have something not mandatory, use
235 "title like <<Enter title>>" and enter a % at run time instead
240 To generate a date picker calendar to the right of the field when
241 running a report you can use the 'date' keyword like this:
246 List of parameters that can be used in runtime parameters
248 +-------------------------+---------------------------------------------------+------------------------------+
249 | Parameter | What the user sees | What gets inserted in query |
250 +-------------------------+---------------------------------------------------+------------------------------+
251 | date | date picker | validly formatted date |
252 | branches | drop down of branch names | branch code |
253 | itemtypes | drop down of item type names | item type |
254 | categorycode | drop down of patron category descriptions | borrower category code |
255 | biblio\_framework | drop down of MARC bibliographic frameworks | framework code |
256 | (auth-value-category) | drop down of auth-value descriptions in category | authorized value |
257 | (nothing) | text box | entered text |
258 +-------------------------+---------------------------------------------------+------------------------------+
262 - SELECT surname, firstname FROM borrowers WHERE branchcode=<<Enter
263 patron's library\|branches>> AND surname like <<Enter filter for
264 patron surname (% if none)>>
266 - SELECT \* FROM items WHERE homebranch = <<Pick your
267 branch\|branches>> and barcode like <<Partial barcode value here>>
269 - SELECT title, author FROM biblio WHERE frameworkcode=<<Enter the
270 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 .. _mana-SQL-report-label:
293 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
295 In the "Create report from SQL", you can search Mana KB for pre-made reports
296 by clicking on "New report" and choosing "New SQL from Mana".
302 This option will only appear if you've :ref:`configured Mana KB <share-with-mana-kb-label>` in the administration module.
304 You will be prompted to enter keywords to search the knowledge base.
308 In the search results, you will see
310 - the details of the report (name, notes and type)
312 - how many people have used this entry (# of users)
314 - when it was used for the last time (last import)
316 - additional comments made by other Koha users (comments)
320 .. What does the orange mean??
322 Click on "Import" to import a copy of the report in your own saved reports.
324 You can then edit it, duplicate it, delete it, run it as you would any of
327 .. _duplicate-report-label:
330 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
332 Reports can also be added by duplicating an existing report. Visit the
333 'Saved reports' page to see all of the reports listed on your system
338 To the right of every report there is an 'Actions' pull down. Clicking
339 that and choose 'Duplicate' to use an existing report as the basis for
340 your new report. That will populate the new report form with the
341 existing SQL for easy editing and resaving.
343 .. _edit-custom-reports-label:
346 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
348 Every report can be edited from the reports lists. To see the list of
349 reports already stored in Koha, click 'Use saved.'
353 To find the report you'd like to edit you can sort by any of the columns
354 by clicking the on the column header. You can also filter your results
355 using the filter menu on the left or use the tabs to find reports based
356 on your custom groups.
358 From this list you can edit any custom report by clicking 'Actions' to
359 the right of the report and choosing 'Edit' from the menu that appears.
363 The form to edit the report will appear.
367 .. _running-custom-reports-label:
369 Running custom reports
370 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
372 Once custom reports are saved to Koha, you can run them by going to the
373 Saved Reports page and clicking the 'Actions' button to the right of the
374 report and choosing 'Run'.
378 When you report runs you will either be asked for some values
382 or you will see the results right away
386 From the results you can choose to rerun the report by clicking 'Run
387 report' at the top, edit the report by clicking the 'Edit' button or
388 starting over and creating a new report by using the 'New' button. You
389 can also download your results by choosing a file type at the bottom of
390 the results next to the 'Download the report' label and clicking
395 A comma separated text file is a CSV file and it can be opened by
396 any spreadsheet application.
398 .. _send-itemnumbers-to-batch-item-modification-label:
400 Send itemnumbers to batch item modification
401 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
403 After running a report that contains itemnumbers the list of numbers can be imported
404 directly into :ref:`Batch item modification` by clicking the batch modify button
405 in the report results.
409 .. _statistics-reports-label:
412 --------------------------------------
414 Statistic reports will show you counts and sums. These reports are all
415 about numbers and statistics, for reports that return more detailed
416 data, use the :ref:`guided report wizard`. These
417 reports are limited in what data they can look at, so it's often
418 recommended to use :ref:`custom reports <custom-reports-label>` for official end
419 of the year statistics.
421 .. _acquisitions-statistics-label:
423 Acquisitions statistics
424 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
428 These reports are limited in what data they can look at, so it's
429 often recommended to use :ref:`custom reports <custom-reports-label>` for
430 official end of the year statistics.
432 Using the form provided, choose which value you would like to appear in
433 the column and which will appear in the row.
437 If you choose to output to the browser your results will print to the
442 You can also choose to export to a file that you can manipulate to your
445 When generating your report, note that you get to choose between
446 counting or summing the values.
450 Choosing amount will change your results to appear as the sum of the
455 .. _patron-statistics-label:
458 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
462 These reports are limited in what data they can look at, so it's
463 often recommended to use :ref:`custom reports <custom-reports-label>` for
464 official end of the year statistics.
466 Using the form provided, choose which value you would like to appear in
467 the column and which will appear in the row.
471 If you choose to output to the browser your results will print to the
476 Based on your selections, you may see some query information above your
477 results table. You can also choose to export to a file that you can
478 manipulate to your needs.
480 .. _catalog-statistics-label:
483 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
487 These reports are limited in what data they can look at, so it's
488 often recommended to use :ref:`custom reports <custom-reports-label>` for
489 official end of the year statistics.
491 Using the form provided, choose which value you would like to appear in
492 the column and which will appear in the row.
496 If you choose to output to the browser your results will print to the
501 You can also choose to export to a file that you can manipulate to your
504 .. _circulation-statistics-label:
506 Circulation statistics
507 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
511 These reports are limited in what data they can look at, so it's
512 often recommended to use :ref:`custom reports <custom-reports-label>` for
513 official end of the year statistics.
515 Using the form provided, choose which value you would like to appear in
516 the column and which will appear in the row.
520 If you choose to output to the browser your results will print to the
525 You can also choose to export to a file that you can manipulate to your
530 To get a complete picture of your monthly or daily circulation, you
531 can run the report twice, once for 'Type' of 'Checkout' and again
534 This report uses 'Period,' or date, filtering that allows you to
535 limit to a month by simply selecting the first day of the first
536 month through the first day of the next month. For example, 10/1 to
537 11/1 to find statistics for the month of October.
539 - To find daily statistics, set your date range.</br> Example: "I
540 want circulation data starting with date XXX up to, but not
541 including, date XXX."
543 - For a whole month, an example range would be: 11/01/2009 to
546 - For a whole year, an example range would be: 01/01/2009 to
549 - For a single day, an example would be: 11/15/2009 to 11/16/2009
550 to find what circulated on the 15th
552 .. _tracking-in-house-use-reports-label:
554 Tracking in house use
555 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
557 Using the Circulation statistics reporting wizard you can run reports on
558 in house usage of items simply by choosing 'Local use' from the 'Type'
563 .. _serials-statistics-label:
566 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
570 These reports are limited in what data they can look at, so it's
571 often recommended to use :ref:`custom reports <custom-reports-label>` for
572 official end of the year statistics.
574 Using the form provided, choose how you would like to list the serials
579 If you choose to output to the browser your results will print to the
584 You can also choose to export to a file that you can manipulate to your
587 .. _holds-statistics-label:
590 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
594 These reports are limited in what data they can look at, so it's
595 often recommended to use :ref:`custom reports <custom-reports-label>` for
596 official end of the year statistics.
598 Using the form provided you can see statistics for holds placed, filled,
599 cancelled and more at your library. From the form choose what value you
600 want to display in the column and what value to show in the row. You can
601 also choose from the filters on the far right of the form.
605 If you choose to output to the browser your results will print to the
610 You can also choose to export to a file that you can manipulate to your
613 .. _patrons-with-the-most-checkouts-label:
615 Patrons with the most checkouts
616 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
618 This report will simply list the patrons who have the most checkouts.
622 If you choose to output to the browser your results will print to the
627 You can also choose to export to a file that you can manipulate to your
630 .. _most-circulated-items-label:
632 Most circulated items
633 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
635 This report will simply list the items that have the been checked out
640 If you choose to output to the browser your results will print to the
645 You can also choose to export to a file that you can manipulate to your
648 .. _patrons-with-no-checkouts-label:
650 Patrons with no checkouts
651 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
653 This report will list for you the patrons in your system who haven't
654 checked any items out.
658 If you choose to output to the browser your results will print to the
663 You can also choose to export to a file that you can manipulate to your
666 .. _items-with-no-checkouts-label:
668 Items with no checkouts
669 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
671 This report will list items in your collection that have never been
676 If you choose to output to the browser your results will print to the
681 You can also choose to export to a file that you can manipulate to your
684 .. _catalog-by-item-type-label:
687 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
689 This report will list the total number of items of each item type per
694 If you choose to output to the browser your results will print to the
699 You can also choose to export to a file that you can manipulate to your
702 .. _lost-items-label:
705 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
707 This report will allow you to generate a list of items that have been
708 marked as Lost within the system
712 .. _average-loan-time-label:
715 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
717 This report will list the average time items are out on loan based on
718 the criteria you enter:
722 If you choose to output to the browser your results will print to the
727 You can also choose to export to a file that you can manipulate to your
730 .. _report-dictionary-label:
733 ----------------------------------
735 The report dictionary is a way to pre-define common filters you'd like
736 to apply to your reports. This is a good way to add in filters that the
737 report wizard doesn't include by default. To add a new definition, or
738 filter, click 'New definition' on the reports dictionary page and follow
741 Step 1: Name the definition and provide a description if necessary
745 Step 2: Choose the module that the will be queried.
749 Step 3: Choose columns to query from the tables presented.
753 Step 4: Choose the value(s) from the field(s). These will be
754 automatically populated with options available in your database.
758 Confirm your selections to save the definition.
762 Your definitions will all appear on the Reports Dictionary page
766 Then when generating reports on the module you created the value for you
767 will see an option to limit by the definition at the bottom of the usual