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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
340 After running a report that contains itemnumbers the list of numbers can be imported
341 directly into :ref:`Batch item modification` by clicking the batch modify button
342 in the report results.
346 .. _statistics-reports-label:
349 --------------------------------------
351 Statistic reports will show you counts and sums. These reports are all
352 about numbers and statistics, for reports that return more detailed
353 data, use the :ref:`Guided Report Wizard`. These
354 reports are limited in what data they can look at, so it's often
355 recommended to use :ref:`custom reports <custom-reports-label>` for official end
356 of the year statistics.
358 .. _acquisitions-statistics-label:
360 Acquisitions Statistics
361 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
365 These reports are limited in what data they can look at, so it's
366 often recommended to use :ref:`custom reports <custom-reports-label>` for
367 official end of the year statistics.
369 Using the form provided, choose which value you would like to appear in
370 the Column and which will appear in the Row.
374 If you choose to output to the browser your results will print to the
379 You can also choose to export to a file that you can manipulate to your
382 When generating your report, note that you get to choose between
383 counting or summing the values.
387 Choosing amount will change your results to appear as the sum of the
392 .. _patron-statistics-label:
395 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
399 These reports are limited in what data they can look at, so it's
400 often recommended to use :ref:`custom reports <custom-reports-label>` for
401 official end of the year statistics.
403 Using the form provided, choose which value you would like to appear in
404 the Column and which will appear in the Row.
408 If you choose to output to the browser your results will print to the
413 Based on your selections, you may see some query information above your
414 results table. You can also choose to export to a file that you can
415 manipulate to your needs.
417 .. _catalog-statistics-label:
420 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
424 These reports are limited in what data they can look at, so it's
425 often recommended to use :ref:`custom reports <custom-reports-label>` for
426 official end of the year statistics.
428 Using the form provided, choose which value you would like to appear in
429 the Column and which will appear in the Row.
433 If you choose to output to the browser your results will print to the
438 You can also choose to export to a file that you can manipulate to your
441 .. _circulation-statistics-label:
443 Circulation Statistics
444 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
448 These reports are limited in what data they can look at, so it's
449 often recommended to use :ref:`custom reports <custom-reports-label>` for
450 official end of the year statistics.
452 Using the form provided, choose which value you would like to appear in
453 the Column and which will appear in the Row.
457 If you choose to output to the browser your results will print to the
462 You can also choose to export to a file that you can manipulate to your
467 To get a complete picture of your monthly or daily circulation, you
468 can run the report twice, once for 'Type' of 'Checkout' and again
471 This report uses 'Period,' or date, filtering that allows you to
472 limit to a month by simply selecting the first day of the first
473 month through the first day of the next month. For example, 10/1 to
474 11/1 to find statistics for the month of October.
476 - To find daily statistics, set your date range.</br> Example: "I
477 want circulation data starting with date XXX up to, but not
478 including, date XXX."
480 - For a whole month, an example range would be: 11/01/2009 to
483 - For a whole year, an example range would be: 01/01/2009 to
486 - For a single day, an example would be: 11/15/2009 to 11/16/2009
487 to find what circulated on the 15th
489 .. _tracking-in-house-use-label:
491 Tracking in house use
492 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
494 Using the Circulation statistics reporting wizard you can run reports on
495 in house usage of items simply by choosing 'Local Use' from the 'Type'
500 .. _serials-statistics-label:
503 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
507 These reports are limited in what data they can look at, so it's
508 often recommended to use :ref:`custom reports <custom-reports-label>` for
509 official end of the year statistics.
511 Using the form provided, choose how you would like to list the serials
516 If you choose to output to the browser your results will print to the
521 You can also choose to export to a file that you can manipulate to your
524 .. _holds-statistics-label:
527 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
531 These reports are limited in what data they can look at, so it's
532 often recommended to use :ref:`custom reports <custom-reports-label>` for
533 official end of the year statistics.
535 Using the form provided you can see statistics for holds placed, filled,
536 cancelled and more at your library. From the form choose what value you
537 want to display in the column and what value to show in the row. You can
538 also choose from the filters on the far right of the form.
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 .. _patrons-with-the-most-checkouts-label:
552 Patrons with the most checkouts
553 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
555 This report will simply list the patrons who have the most checkouts.
559 If you choose to output to the browser your results will print to the
564 You can also choose to export to a file that you can manipulate to your
567 .. _most-circulated-items-label:
569 Most Circulated Items
570 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
572 This report will simply list the items that have the been checked out
577 If you choose to output to the browser your results will print to the
582 You can also choose to export to a file that you can manipulate to your
585 .. _patrons-with-no-checkouts-label:
587 Patrons with no checkouts
588 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
590 This report will list for you the patrons in your system who haven't
591 checked any items out.
595 If you choose to output to the browser your results will print to the
600 You can also choose to export to a file that you can manipulate to your
603 .. _items-with-no-checkouts-label:
605 Items with no checkouts
606 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
608 This report will list items in your collection that have never been
613 If you choose to output to the browser your results will print to the
618 You can also choose to export to a file that you can manipulate to your
621 .. _catalog-by-item-type-label:
624 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
626 This report will list the total number of items of each item type per
631 If you choose to output to the browser your results will print to the
636 You can also choose to export to a file that you can manipulate to your
639 .. _lost-items-label:
642 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
644 This report will allow you to generate a list of items that have been
645 marked as Lost within the system
649 .. _average-loan-time-label:
652 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
654 This report will list the average time items are out on loan based on
655 the criteria you enter:
659 If you choose to output to the browser your results will print to the
664 You can also choose to export to a file that you can manipulate to your
667 .. _report-dictionary-label:
670 ----------------------------------
672 The report dictionary is a way to pre-define common filters you'd like
673 to apply to your reports. This is a good way to add in filters that the
674 report wizard doesn't include by default. To add a new definition, or
675 filter, click 'New Definition' on the Reports Dictionary page and follow
678 Step 1: Name the definition and provide a description if necessary
682 Step 2: Choose the module that the will be queried.
686 Step 3: Choose columns to query from the tables presented.
690 Step 4: Choose the value(s) from the field(s). These will be
691 automatically populated with options available in your database.
695 Confirm your selections to save the definition.
699 Your definitions will all appear on the Reports Dictionary page
703 Then when generating reports on the module you created the value for you
704 will see an option to limit by the definition at the bottom of the usual