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>>
271 .. _duplicate-report-label:
274 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
276 Reports can also be added by duplicating an existing report. Visit the
277 'Saved reports' page to see all of the reports listed on your system
282 To the right of every report there is an 'Actions' pull down. Clicking
283 that and choose 'Duplicate' to use an existing report as the basis for
284 your new report. That will populate the new report form with the
285 existing SQL for easy editing and resaving.
287 .. _edit-custom-reports-label:
290 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
292 Every report can be edited from the reports lists. To see the list of
293 reports already stored in Koha, click 'Use saved.'
297 To find the report you'd like to edit you can sort by any of the columns
298 by clicking the on the column header. You can also filter your results
299 using the filter menu on the left or use the tabs to find reports based
300 on your custom groups.
302 From this list you can edit any custom report by clicking 'Actions' to
303 the right of the report and choosing 'Edit' from the menu that appears.
307 The form to edit the report will appear.
311 .. _running-custom-reports-label:
313 Running custom reports
314 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
316 Once custom reports are saved to Koha, you can run them by going to the
317 Saved Reports page and clicking the 'Actions' button to the right of the
318 report and choosing 'Run'.
322 When you report runs you will either be asked for some values
326 or you will see the results right away
330 From the results you can choose to rerun the report by clicking 'Run
331 report' at the top, edit the report by clicking the 'Edit' button or
332 starting over and creating a new report by using the 'New' button. You
333 can also download your results by choosing a file type at the bottom of
334 the results next to the 'Download the report' label and clicking
339 A comma separated text file is a CSV file and it can be opened by
340 any spreadsheet application.
342 .. _send-itemnumbers-to-batch-item-modification-label:
344 Send itemnumbers to batch item modification
345 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
347 After running a report that contains itemnumbers the list of numbers can be imported
348 directly into :ref:`Batch item modification` by clicking the batch modify button
349 in the report results.
353 .. _statistics-reports-label:
356 --------------------------------------
358 Statistic reports will show you counts and sums. These reports are all
359 about numbers and statistics, for reports that return more detailed
360 data, use the :ref:`guided report wizard`. These
361 reports are limited in what data they can look at, so it's often
362 recommended to use :ref:`custom reports <custom-reports-label>` for official end
363 of the year statistics.
365 .. _acquisitions-statistics-label:
367 Acquisitions statistics
368 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
372 These reports are limited in what data they can look at, so it's
373 often recommended to use :ref:`custom reports <custom-reports-label>` for
374 official end of the year statistics.
376 Using the form provided, choose which value you would like to appear in
377 the column and which will appear in the row.
381 If you choose to output to the browser your results will print to the
386 You can also choose to export to a file that you can manipulate to your
389 When generating your report, note that you get to choose between
390 counting or summing the values.
394 Choosing amount will change your results to appear as the sum of the
399 .. _patron-statistics-label:
402 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
406 These reports are limited in what data they can look at, so it's
407 often recommended to use :ref:`custom reports <custom-reports-label>` for
408 official end of the year statistics.
410 Using the form provided, choose which value you would like to appear in
411 the column and which will appear in the row.
415 If you choose to output to the browser your results will print to the
420 Based on your selections, you may see some query information above your
421 results table. You can also choose to export to a file that you can
422 manipulate to your needs.
424 .. _catalog-statistics-label:
427 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
431 These reports are limited in what data they can look at, so it's
432 often recommended to use :ref:`custom reports <custom-reports-label>` for
433 official end of the year statistics.
435 Using the form provided, choose which value you would like to appear in
436 the column and which will appear in the row.
440 If you choose to output to the browser your results will print to the
445 You can also choose to export to a file that you can manipulate to your
448 .. _circulation-statistics-label:
450 Circulation statistics
451 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
455 These reports are limited in what data they can look at, so it's
456 often recommended to use :ref:`custom reports <custom-reports-label>` for
457 official end of the year statistics.
459 Using the form provided, choose which value you would like to appear in
460 the column and which will appear in the row.
464 If you choose to output to the browser your results will print to the
469 You can also choose to export to a file that you can manipulate to your
474 To get a complete picture of your monthly or daily circulation, you
475 can run the report twice, once for 'Type' of 'Checkout' and again
478 This report uses 'Period,' or date, filtering that allows you to
479 limit to a month by simply selecting the first day of the first
480 month through the first day of the next month. For example, 10/1 to
481 11/1 to find statistics for the month of October.
483 - To find daily statistics, set your date range.</br> Example: "I
484 want circulation data starting with date XXX up to, but not
485 including, date XXX."
487 - For a whole month, an example range would be: 11/01/2009 to
490 - For a whole year, an example range would be: 01/01/2009 to
493 - For a single day, an example would be: 11/15/2009 to 11/16/2009
494 to find what circulated on the 15th
496 .. _tracking-in-house-use-label:
498 Tracking in house use
499 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
501 Using the Circulation statistics reporting wizard you can run reports on
502 in house usage of items simply by choosing 'Local use' from the 'Type'
507 .. _serials-statistics-label:
510 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
514 These reports are limited in what data they can look at, so it's
515 often recommended to use :ref:`custom reports <custom-reports-label>` for
516 official end of the year statistics.
518 Using the form provided, choose how you would like to list the serials
523 If you choose to output to the browser your results will print to the
528 You can also choose to export to a file that you can manipulate to your
531 .. _holds-statistics-label:
534 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
538 These reports are limited in what data they can look at, so it's
539 often recommended to use :ref:`custom reports <custom-reports-label>` for
540 official end of the year statistics.
542 Using the form provided you can see statistics for holds placed, filled,
543 cancelled and more at your library. From the form choose what value you
544 want to display in the column and what value to show in the row. You can
545 also choose from the filters on the far right of the form.
549 If you choose to output to the browser your results will print to the
554 You can also choose to export to a file that you can manipulate to your
557 .. _patrons-with-the-most-checkouts-label:
559 Patrons with the most checkouts
560 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
562 This report will simply list the patrons who have the most checkouts.
566 If you choose to output to the browser your results will print to the
571 You can also choose to export to a file that you can manipulate to your
574 .. _most-circulated-items-label:
576 Most circulated items
577 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
579 This report will simply list the items that have the been checked out
584 If you choose to output to the browser your results will print to the
589 You can also choose to export to a file that you can manipulate to your
592 .. _patrons-with-no-checkouts-label:
594 Patrons with no checkouts
595 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
597 This report will list for you the patrons in your system who haven't
598 checked any items out.
602 If you choose to output to the browser your results will print to the
607 You can also choose to export to a file that you can manipulate to your
610 .. _items-with-no-checkouts-label:
612 Items with no checkouts
613 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
615 This report will list items in your collection that have never been
620 If you choose to output to the browser your results will print to the
625 You can also choose to export to a file that you can manipulate to your
628 .. _catalog-by-item-type-label:
631 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
633 This report will list the total number of items of each item type per
638 If you choose to output to the browser your results will print to the
643 You can also choose to export to a file that you can manipulate to your
646 .. _lost-items-label:
649 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
651 This report will allow you to generate a list of items that have been
652 marked as Lost within the system
656 .. _average-loan-time-label:
659 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
661 This report will list the average time items are out on loan based on
662 the criteria you enter:
666 If you choose to output to the browser your results will print to the
671 You can also choose to export to a file that you can manipulate to your
674 .. _report-dictionary-label:
677 ----------------------------------
679 The report dictionary is a way to pre-define common filters you'd like
680 to apply to your reports. This is a good way to add in filters that the
681 report wizard doesn't include by default. To add a new definition, or
682 filter, click 'New definition' on the reports dictionary page and follow
685 Step 1: Name the definition and provide a description if necessary
689 Step 2: Choose the module that the will be queried.
693 Step 3: Choose columns to query from the tables presented.
697 Step 4: Choose the value(s) from the field(s). These will be
698 automatically populated with options available in your database.
702 Confirm your selections to save the definition.
706 Your definitions will all appear on the Reports Dictionary page
710 Then when generating reports on the module you created the value for you
711 will see an option to limit by the definition at the bottom of the usual