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 making an option.
72 Step 5: Perform math functions. If you don't want to do any
73 calculations, simply click Next instead of making 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
179 - If you feel that your report might be too resource intensive you
180 might want to consider using runtime parameters to your query.
181 Runtime parameters basically make a filter appear before the report
182 is run to save your system resources.
184 There is a specific syntax that Koha will understand as 'ask for
185 values when running the report'. The syntax is <<Question to
186 ask\|authorized\_value>>.
188 - The << and >> are just delimiters. You must put << at the
189 beginning and >> at the end of your parameter
191 - The 'Question to ask' will be displayed on the left of the string
194 - The authorized\_value can be omitted if not applicable. If it
195 contains an authorized value category, or branches or itemtype or
196 categorycode or biblio\_framework, a list with the Koha authorized
197 values will be displayed instead of a free field Note that you can
198 have more than one parameter in a given SQL Note that entering
199 nothing at run time won't probably work as you expect. It will be
200 considered as "value empty" not as "ignore this parameter". For
201 example entering nothing for : "title=<<Enter title>>" will
202 display results with title='' (no title). If you want to have to
203 have something not mandatory, use "title like <<Enter title>>" and
204 enter a % at run time instead of nothing
208 - SELECT surname,firstname FROM borrowers WHERE branchcode=<<Enter
209 patrons library\|branches>> AND surname like <<Enter filter for
210 patron surname (% if none)>>
212 - SELECT \* FROM items WHERE homebranch = <<Pick your
213 branch\|branches>> and barcode like <<Partial barcode value here>>
215 - SELECT title , author FROM biblio WHERE frameworkcode=<<Enter the
216 frameworkcode\|biblio\_framework>>
220 To generate a date picker calendar to the right of the field when
221 running a report you can use the 'date' keyword like this:
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
234 In addition to using any authorized value code to generate a
235 dropdown, you can use the following values as well: Framework
236 codes (biblio\_framework), Branches (branches), Item Types
237 (itemtypes) and Patron Categories (categorycode). For example a
238 branch pull down would be generated like this
245 There is a limit of 10,000 records put on SQL statements entered in
246 Koha. To get around this you want to add 'LIMIT 100000' to the end
247 of your SQL statement (or any other number above 10,000).
251 If your system administrator has set up memcache on your server you
252 might see one more option for the Cache expiry. This is related to
253 your public reports. If you make the report public then it's
254 constantly running and will cause a large load on your system.
255 Setting this value prevents that.
259 Once everything is entered click the 'Save Report' button and you'll be
260 presented with options to run it. Once a report is saved you do not have
261 to recreate it you can simply find it on the Saved Reports page and
262 :ref:`run <running-custom-reports-label>` or :ref:`edit <edit-custom-reports-label>` it.
264 .. _duplicate-report-label:
267 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
269 Reports can also be added by duplicating an existing report. Visit the
270 'Saved reports' page to see all of the reports listed on your system
275 To the right of every report there is an 'Actions' pull down. Clicking
276 that and choose 'Duplicate' to use an existing report as the basis for
277 your new report. That will populate the new report form with the
278 existing SQL for easy editing and resaving.
280 .. _edit-custom-reports-label:
283 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
285 Every report can be edited from the reports lists. To see the list of
286 reports already stored in Koha, click 'Use Saved.'
290 To find the report you'd like to edit you can sort by any of the columns
291 by clicking the on the column header. You can also filter your results
292 using the filter menu on the left or use the tabs to find reports based
293 on your custom groups.
295 From this list you can edit any custom report by clicking 'Actions' to
296 the right of the report and choosing 'Edit' from the menu that appears.
300 The form to edit the report will appear.
304 .. _running-custom-reports-label:
306 Running Custom Reports
307 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
309 Once custom reports are saved to Koha, you can run them by going to the
310 Saved Reports page and clicking the 'Actions' button to the right of the
311 report and choosing 'Run'.
315 When you report runs you will either be asked for some values
319 or you will see the results right away
323 From the results you can choose to rerun the report by clicking 'Run
324 report' at the top, edit the report by clicking the 'Edit' button or
325 starting over and creating a new report by using the 'New' button. You
326 can also download your results by choosing a file type at the bottom of
327 the results next to the 'Download the report' label and clicking
332 A Comma Separated Text file is a CSV file and it can be opened by
333 any spreadsheet application.
335 .. _send-itemnumbers-to-batch-item-modification-label:
337 Send itemnumbers to batch item modification
338 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
342 .. _statistics-reports-label:
345 --------------------------------------
347 Statistic reports will show you counts and sums. These reports are all
348 about numbers and statistics, for reports that return more detailed
349 data, use the :ref:`Guided Report Wizard`. These
350 reports are limited in what data they can look at, so it's often
351 recommended to use :ref:`custom reports <custom-reports-label>` for official end
352 of the year statistics.
354 .. _acquisitions-statistics-label:
356 Acquisitions Statistics
357 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
361 These reports are limited in what data they can look at, so it's
362 often recommended to use :ref:`custom reports <custom-reports-label>` for
363 official end of the year statistics.
365 Using the form provided, choose which value you would like to appear in
366 the Column and which will appear in the Row.
370 If you choose to output to the browser your results will print to the
375 You can also choose to export to a file that you can manipulate to your
378 When generating your report, note that you get to choose between
379 counting or summing the values.
383 Choosing amount will change your results to appear as the sum of the
388 .. _patron-statistics-label:
391 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
395 These reports are limited in what data they can look at, so it's
396 often recommended to use :ref:`custom reports <custom-reports-label>` for
397 official end of the year statistics.
399 Using the form provided, choose which value you would like to appear in
400 the Column and which will appear in the Row.
404 If you choose to output to the browser your results will print to the
409 Based on your selections, you may see some query information above your
410 results table. You can also choose to export to a file that you can
411 manipulate to your needs.
413 .. _catalog-statistics-label:
416 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
420 These reports are limited in what data they can look at, so it's
421 often recommended to use :ref:`custom reports <custom-reports-label>` for
422 official end of the year statistics.
424 Using the form provided, choose which value you would like to appear in
425 the Column and which will appear in the Row.
429 If you choose to output to the browser your results will print to the
434 You can also choose to export to a file that you can manipulate to your
437 .. _circulation-statistics-label:
439 Circulation Statistics
440 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
444 These reports are limited in what data they can look at, so it's
445 often recommended to use :ref:`custom reports <custom-reports-label>` for
446 official end of the year statistics.
448 Using the form provided, choose which value you would like to appear in
449 the Column and which will appear in the Row.
453 If you choose to output to the browser your results will print to the
458 You can also choose to export to a file that you can manipulate to your
463 To get a complete picture of your monthly or daily circulation, you
464 can run the report twice, once for 'Type' of 'Checkout' and again
467 This report uses 'Period,' or date, filtering that allows you to
468 limit to a month by simply selecting the first day of the first
469 month through the first day of the next month. For example, 10/1 to
470 11/1 to find statistics for the month of October.
472 - To find daily statistics, set your date range.</br> Example: "I
473 want circulation data starting with date XXX up to, but not
474 including, date XXX."
476 - For a whole month, an example range would be: 11/01/2009 to
479 - For a whole year, an example range would be: 01/01/2009 to
482 - For a single day, an example would be: 11/15/2009 to 11/16/2009
483 to find what circulated on the 15th
485 .. _tracking-in-house-use-label:
487 Tracking in house use
488 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
490 Using the Circulation statistics reporting wizard you can run reports on
491 in house usage of items simply by choosing 'Local Use' from the 'Type'
496 .. _serials-statistics-label:
499 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
503 These reports are limited in what data they can look at, so it's
504 often recommended to use :ref:`custom reports <custom-reports-label>` for
505 official end of the year statistics.
507 Using the form provided, choose how you would like to list the serials
512 If you choose to output to the browser your results will print to the
517 You can also choose to export to a file that you can manipulate to your
520 .. _holds-statistics-label:
523 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
527 These reports are limited in what data they can look at, so it's
528 often recommended to use :ref:`custom reports <custom-reports-label>` for
529 official end of the year statistics.
531 Using the form provided you can see statistics for holds placed, filled,
532 cancelled and more at your library. From the form choose what value you
533 want to display in the column and what value to show in the row. You can
534 also choose from the filters on the far right of the form.
538 If you choose to output to the browser your results will print to the
543 You can also choose to export to a file that you can manipulate to your
546 .. _patrons-with-the-most-checkouts-label:
548 Patrons with the most checkouts
549 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
551 This report will simply list the patrons who have the most checkouts.
555 If you choose to output to the browser your results will print to the
560 You can also choose to export to a file that you can manipulate to your
563 .. _most-circulated-items-label:
565 Most Circulated Items
566 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
568 This report will simply list the items that have the been checked out
573 If you choose to output to the browser your results will print to the
578 You can also choose to export to a file that you can manipulate to your
581 .. _patrons-with-no-checkouts-label:
583 Patrons with no checkouts
584 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
586 This report will list for you the patrons in your system who haven't
587 checked any items out.
591 If you choose to output to the browser your results will print to the
596 You can also choose to export to a file that you can manipulate to your
599 .. _items-with-no-checkouts-label:
601 Items with no checkouts
602 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
604 This report will list items in your collection that have never been
609 If you choose to output to the browser your results will print to the
614 You can also choose to export to a file that you can manipulate to your
617 .. _catalog-by-item-type-label:
620 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
622 This report will list the total number of items of each item type per
627 If you choose to output to the browser your results will print to the
632 You can also choose to export to a file that you can manipulate to your
635 .. _lost-items-label:
638 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
640 This report will allow you to generate a list of items that have been
641 marked as Lost within the system
645 .. _average-loan-time-label:
648 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
650 This report will list the average time items are out on loan based on
651 the criteria you enter:
655 If you choose to output to the browser your results will print to the
660 You can also choose to export to a file that you can manipulate to your
663 .. _report-dictionary-label:
666 ----------------------------------
668 The report dictionary is a way to pre-define common filters you'd like
669 to apply to your reports. This is a good way to add in filters that the
670 report wizard doesn't include by default. To add a new definition, or
671 filter, click 'New Definition' on the Reports Dictionary page and follow
674 Step 1: Name the definition and provide a description if necessary
678 Step 2: Choose the module that the will be queried.
682 Step 3: Choose columns to query from the tables presented.
686 Step 4: Choose the value(s) from the field(s). These will be
687 automatically populated with options available in your database.
691 Confirm your selections to save the definition.
695 Your definitions will all appear on the Reports Dictionary page
699 Then when generating reports on the module you created the value for you
700 will see an option to limit by the definition at the bottom of the usual