Taiga 1274 (24976) Updated guided reports process

[kohadocs.git] / source / reports.rst

.. include:: images.rst

Reports
=======

Reports in Koha are a way to gather data. Reports are used to generate
statistics, member lists, shelving lists, or any list of data in your
database.

-  *Get there:* More > Reports

.. _custom-reports-label:

Custom reports
-----------------------------------

Koha's data is stored in a MySQL database which means that librarians
can generate nearly any report they would like by either using the
:ref:`guided reports wizard <guided-report-wizard-label>` or writing their own
:ref:`SQL query <report-from-sql-label>`.

.. _add-custom-report-label:

Add custom report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. _guided-report-wizard-label:

Guided report wizard
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The guided report wizard will walk you through a six step process to
generate a report.

Step 1: Choose the module you want to report on. This will determine
what tables and fields are available for you to query.

'Report is public' should be left to the default of 'No' in most cases 
especially if the report contains patron or other sensitive information.
A public report can be accessed using the :ref:`JSON reports services 
<json-reports-services-label>` by anyone and without authentication.

|image881|

    **Note**

    If your system administrator has set up memcache on your server you
    might see one more option for the Cache expiry. This is related to
    your public reports. If you make the report public then it's
    constantly running and will cause a large load on your system.
    Setting this value prevents that.

    |image1292|

Step 2: Choose a report type. For now, Tabular is the only option
available.

|image882|

Step 3: Choose the fields you want in your report. You can select
multiple fields and add them all at once by using CTRL+click on each
item you want to add before clicking the Add button.

|image883|

Step 4: Choose any limits you might want to apply to your report (such
as item types or branches). If you don't want to apply any limits,
simply click 'Next' instead of choosing an option.

|image884|

Step 5: Perform math functions. If you don't want to do any
calculations, simply click 'Next' instead of choosing an option.

|image885|

Step 6: Choose data order. If you want the data to print out in the
order it's found in the database, simply click 'Finish'.

|image886|

When you are finished you will be presented with the SQL generated by
the report wizard. From here you can choose to save the report by
clicking 'Save' or copy the SQL and make edits to it by hand.

|image887|

If you choose to save the report you will be asked to name your report,
sort it in to groups and subgroups and enter any notes regarding it.

|image888|

Once your report is saved it will appear on the 'Use saved' page with
all other saved reports.

|image889|

From here you can make edits, run the report, or schedule a time to have
the report run. To find the report you created you can sort by any of
the columns by clicking the on the column header (creation date is the
best bet for finding the report you just added). You can also filter
your results using the filter menu on the left or use the tabs to find
reports based on your custom groups.

.. _report-from-sql-label:

Report from SQL
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

In addition to the report wizard, you have the option to write your own
queries using SQL. To find reports written by other Koha users, visit
the Koha wiki: http://wiki.koha-community.org/wiki/SQL_Reports_Library.
You can also find your database structure in
/installer/data/mysql/kohastructure.sql or online at:
`http://schema.koha-community.org <http://schema.koha-community.org/>`__.

To add your query, click the link to 'Create from SQL' on the main
reports module or the 'New report' button at the top of the 'Saved
reports' page.

|image890|

Fill in the form presented

|image891|

-  The 'Name' is what will appear on the Saved Reports page to help you
   identify the report later. It will also be searchable using the
   filters found the left of the Saved Reports page.

-  You can use the 'Report group' to organize your reports so that you
   can easily filter reports by groups. Report groups are set in the
   `REPORT\_GROUP <#reportgroup>`__ authorized value category or can be
   added on the fly when creating the report by choosing the 'or create'
   radio button.

   |image1293|

   Report groups set up in the authorised value category
   need to have unique authorised values and descriptions.

   -  **Note**

          If you're adding a report group on the fly, remember that you
          code should be fewer than 10 characters and should not include
          special characters or spaces.

-  You can use 'Report subgroup' to further organize your reports so
   that you can easily filter reports by groups and subgroups. Report
   subgroups are set in the `REPORT\_SUBGROUP <#reportsubgroup>`__
   authorized value category or can be added on the fly when creating
   the report by choosing the 'or create' radio button.  Report subgroups
   are set up with unique values in 'Authorised value', and 'Description'. 
   The 'Description (OPAC)'' field needs to contain the authorised value for 
   the report group that the subgroup falls under.

   |image892|

   -  **Note**

          If you're adding a report subgroup on the fly, remember that
          you code should be fewer than 10 characters and should not
          include special characters or spaces.

-  'Report is public' should be left to the default of 'No' in most cases 
    especially if the report contains patron or other sensitive information.
    A public report can be accessed using the :ref:`JSON reports services 
    <json-reports-services-label>` by anyone and without authentication.

-  'Notes' will also appear on the saved reports page, this can be used
   to provide more details about the report or tips on how to enter
   values when it runs

-  The type should always be 'Tabular' at this time since the other
   formats have not been implemented

-  In the 'SQL' box you will type or paste the SQL for the report

    **Note**

    If your system administrator has set up memcache on your server you
    might see one more option for the Cache expiry. This is related to
    your public reports. If you make the report public then it's
    constantly running and will cause a large load on your system.
    Setting this value prevents that.

    |image1294|

Once everything is entered click the 'Save report' button and you'll be
presented with options to run it. Once a report is saved you do not have
to recreate it you can simply find it on the Saved Reports page and
:ref:`run <running-custom-reports-label>` or :ref:`edit <edit-custom-reports-label>` it.

.. _report-writing-tips-label:

Report writing tips
'''''''''''''''''''''''''''''''''''''

**Runtime parameters**


If you feel that your report might be too resource intensive you
might want to consider using runtime parameters to your query.
Runtime parameters basically make a filter appear before the report
is run to save your system resources.

There is a specific syntax that Koha will understand as 'ask for
values when running the report'. The syntax is <<Question to
ask\|authorized\_value>>.

-  The << and >> are just delimiters. You must put << at the
   beginning and >> at the end of your parameter

-  The 'Question to ask' will be displayed on the left of the string
   to enter.

-  The authorized\_value can be omitted if not applicable. If it
   contains an authorized value category, 'branches', 'itemtype', 
   'categorycode', 'biblio\_framework', a list with the Koha authorized
   values will be displayed instead of a free field.

       **Note**

       You can have more than one parameter in a given SQL query.

       **Note**

       You have to put "%" in a text box to 'leave it blank'. Otherwise,
       it literally looks for "" (empty string) as the value for the
       field.
       For example entering nothing for: "title=<<Enter title>>" will 
       display results with title='' (no title).
       If you want to have to have something not mandatory, use 
       "title like <<Enter title>>" and enter a % at run time instead 
       of nothing.

       **Note**

       To generate a date picker calendar to the right of the field when
       running a report you can use the 'date' keyword like this:
       <<Enter Date\|date>>

       |image893|

List of parameters that can be used in runtime parameters

+-------------------------+---------------------------------------------------+------------------------------+
| Parameter               | What the user sees                                | What gets inserted in query  |
+-------------------------+---------------------------------------------------+------------------------------+
| date                    | date picker                                       | validly formatted date       |
| branches                | drop down of branch names                         | branch code                  |            
| itemtypes               | drop down of item type names                      | item type                    |
| categorycode            | drop down of patron category descriptions         | borrower category code       | 
| biblio\_framework       | drop down of MARC bibliographic frameworks        | framework code               |
| (auth-value-category)   | drop down of auth-value descriptions in category  | authorized value             |
| (nothing)               | text box                                          | entered text                 |
+-------------------------+---------------------------------------------------+------------------------------+

Examples:

   -  SELECT surname, firstname FROM borrowers WHERE branchcode=<<Enter
      patron's library\|branches>> AND surname like <<Enter filter for
      patron surname (% if none)>>

   -  SELECT \* FROM items WHERE homebranch = <<Pick your
      branch\|branches>> and barcode like <<Partial barcode value here>>

   -  SELECT title, author FROM biblio WHERE frameworkcode=<<Enter the
      frameworkcode\|biblio\_framework>>


.. ****************************************
.. **************    TODO    **************
.. ****************************************

.. **Querying MARC**

.. It is possible to query the MARC records with the ExtractValue function.

.. **Links**

.. **Runtime variables**

.. **See more results**

.. There is a limit of 10,000 records put on SQL statements entered in 
   Koha. To get around this you want to add 'LIMIT 100000' to the end
   of your SQL statement (or any other number above 10,000).

.. _duplicate-report-label:

Duplicate report
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Reports can also be added by duplicating an existing report. Visit the
'Saved reports' page to see all of the reports listed on your system
already.

|image895|

To the right of every report there is an 'Actions' pull down. Clicking
that and choose 'Duplicate' to use an existing report as the basis for
your new report. That will populate the new report form with the
existing SQL for easy editing and resaving.

.. _edit-custom-reports-label:

Edit custom reports
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Every report can be edited from the reports lists. To see the list of
reports already stored in Koha, click 'Use saved.'

|image896|

To find the report you'd like to edit you can sort by any of the columns
by clicking the on the column header. You can also filter your results
using the filter menu on the left or use the tabs to find reports based
on your custom groups.

From this list you can edit any custom report by clicking 'Actions' to
the right of the report and choosing 'Edit' from the menu that appears.

|image897|

The form to edit the report will appear.

|image898|

.. _running-custom-reports-label:

Running custom reports
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Once custom reports are saved to Koha, you can run them by going to the
Saved Reports page and clicking the 'Actions' button to the right of the
report and choosing 'Run'.

|image899|

When you report runs you will either be asked for some values

|image900|

or you will see the results right away

|image901|

From the results you can choose to rerun the report by clicking 'Run
report' at the top, edit the report by clicking the 'Edit' button or
starting over and creating a new report by using the 'New' button. You
can also download your results by choosing a file type at the bottom of
the results next to the 'Download the report' label and clicking
'Download.'

    **Note**

    A comma separated text file is a CSV file and it can be opened by
    any spreadsheet application.

.. _send-itemnumbers-to-batch-item-modification-label:

Send itemnumbers to batch item modification
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

After running a report that contains itemnumbers the list of numbers can be imported
directly into :ref:`Batch item modification` by clicking the batch modify button
in the report results.

|image1343|

.. _statistics-reports-label:

Statistics reports
--------------------------------------

Statistic reports will show you counts and sums. These reports are all
about numbers and statistics, for reports that return more detailed
data, use the :ref:`guided report wizard`. These
reports are limited in what data they can look at, so it's often
recommended to use :ref:`custom reports <custom-reports-label>` for official end
of the year statistics.

.. _acquisitions-statistics-label:

Acquisitions statistics
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    **Note**

    These reports are limited in what data they can look at, so it's
    often recommended to use :ref:`custom reports <custom-reports-label>` for
    official end of the year statistics.

Using the form provided, choose which value you would like to appear in
the column and which will appear in the row.

|image902|

If you choose to output to the browser your results will print to the
screen.

|image903|

You can also choose to export to a file that you can manipulate to your
needs.

When generating your report, note that you get to choose between
counting or summing the values.

|image904|

Choosing amount will change your results to appear as the sum of the
amounts spent.

|image905|

.. _patron-statistics-label:

Patron statistics
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    **Note**

    These reports are limited in what data they can look at, so it's
    often recommended to use :ref:`custom reports <custom-reports-label>` for
    official end of the year statistics.

Using the form provided, choose which value you would like to appear in
the column and which will appear in the row.

|image906|

If you choose to output to the browser your results will print to the
screen.

|image907|

Based on your selections, you may see some query information above your
results table. You can also choose to export to a file that you can
manipulate to your needs.

.. _catalog-statistics-label:

Catalog statistics
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    **Note**

    These reports are limited in what data they can look at, so it's
    often recommended to use :ref:`custom reports <custom-reports-label>` for
    official end of the year statistics.

Using the form provided, choose which value you would like to appear in
the column and which will appear in the row.

|image908|

If you choose to output to the browser your results will print to the
screen.

|image909|

You can also choose to export to a file that you can manipulate to your
needs.

.. _circulation-statistics-label:

Circulation statistics
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    **Note**

    These reports are limited in what data they can look at, so it's
    often recommended to use :ref:`custom reports <custom-reports-label>` for
    official end of the year statistics.

Using the form provided, choose which value you would like to appear in
the column and which will appear in the row.

|image910|

If you choose to output to the browser your results will print to the
screen.

|image911|

You can also choose to export to a file that you can manipulate to your
needs.

    **Note**

    To get a complete picture of your monthly or daily circulation, you
    can run the report twice, once for 'Type' of 'Checkout' and again
    for 'Renewal.'

    This report uses 'Period,' or date, filtering that allows you to
    limit to a month by simply selecting the first day of the first
    month through the first day of the next month. For example, 10/1 to
    11/1 to find statistics for the month of October.

    -  To find daily statistics, set your date range.</br> Example: "I
       want circulation data starting with date XXX up to, but not
       including, date XXX."

    -  For a whole month, an example range would be: 11/01/2009 to
       12/01/2009

    -  For a whole year, an example range would be: 01/01/2009 to
       01/01/2010

    -  For a single day, an example would be: 11/15/2009 to 11/16/2009
       to find what circulated on the 15th

.. _tracking-in-house-use-label:

Tracking in house use
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Using the Circulation statistics reporting wizard you can run reports on
in house usage of items simply by choosing 'Local use' from the 'Type'
pull down:

|image912|

.. _serials-statistics-label:

Serials statistics
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    **Note**

    These reports are limited in what data they can look at, so it's
    often recommended to use :ref:`custom reports <custom-reports-label>` for
    official end of the year statistics.

Using the form provided, choose how you would like to list the serials
in your system.

|image913|

If you choose to output to the browser your results will print to the
screen.

|image914|

You can also choose to export to a file that you can manipulate to your
needs.

.. _holds-statistics-label:

Holds statistics
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    **Note**

    These reports are limited in what data they can look at, so it's
    often recommended to use :ref:`custom reports <custom-reports-label>` for
    official end of the year statistics.

Using the form provided you can see statistics for holds placed, filled,
cancelled and more at your library. From the form choose what value you
want to display in the column and what value to show in the row. You can
also choose from the filters on the far right of the form.

|image915|

If you choose to output to the browser your results will print to the
screen.

|image916|

You can also choose to export to a file that you can manipulate to your
needs.

.. _patrons-with-the-most-checkouts-label:

Patrons with the most checkouts
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This report will simply list the patrons who have the most checkouts.

|image917|

If you choose to output to the browser your results will print to the
screen.

|image918|

You can also choose to export to a file that you can manipulate to your
needs.

.. _most-circulated-items-label:

Most circulated items
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This report will simply list the items that have the been checked out
the most.

|image919|

If you choose to output to the browser your results will print to the
screen.

|image920|

You can also choose to export to a file that you can manipulate to your
needs.

.. _patrons-with-no-checkouts-label:

Patrons with no checkouts
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This report will list for you the patrons in your system who haven't
checked any items out.

|image921|

If you choose to output to the browser your results will print to the
screen.

|image922|

You can also choose to export to a file that you can manipulate to your
needs.

.. _items-with-no-checkouts-label:

Items with no checkouts
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This report will list items in your collection that have never been
checked out.

|image923|

If you choose to output to the browser your results will print to the
screen.

|image924|

You can also choose to export to a file that you can manipulate to your
needs.

.. _catalog-by-item-type-label:

Catalog by item type
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This report will list the total number of items of each item type per
branch.

|image925|

If you choose to output to the browser your results will print to the
screen.

|image926|

You can also choose to export to a file that you can manipulate to your
needs.

.. _lost-items-label:

Lost items
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This report will allow you to generate a list of items that have been
marked as Lost within the system

|image927|

.. _average-loan-time-label:

Average loan time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This report will list the average time items are out on loan based on
the criteria you enter:

|image928|

If you choose to output to the browser your results will print to the
screen.

|image929|

You can also choose to export to a file that you can manipulate to your
needs.

.. _report-dictionary-label:

Report dictionary
----------------------------------

The report dictionary is a way to pre-define common filters you'd like
to apply to your reports. This is a good way to add in filters that the
report wizard doesn't include by default. To add a new definition, or
filter, click 'New definition' on the reports dictionary page and follow
the 4 step process.

Step 1: Name the definition and provide a description if necessary

|image930|

Step 2: Choose the module that the will be queried.

|image931|

Step 3: Choose columns to query from the tables presented.

|image932|

Step 4: Choose the value(s) from the field(s). These will be
automatically populated with options available in your database.

|image933|

Confirm your selections to save the definition.

|image934|

Your definitions will all appear on the Reports Dictionary page

|image935|

Then when generating reports on the module you created the value for you
will see an option to limit by the definition at the bottom of the usual
filters.

|image936|