Reports

The Reports module allows you to collect data in Koha. The module is used to generate statistics, user lists, lineup lists or other data lists in your database.

Path in Koha: More> Reports

Custom Reports

The data in Koha is stored in a MySQL database. This means library staff can create almost any report they need, either via guided reports or SQL queries .

New guided report

Guided Report Wizard

The Guided Report Wizard guides you through the generation of a report in six steps.

Step 1: Select the module for which the report is to be created. This allows you to determine which tables and fields are available for query.

In most cases the ‘Report is public’ should be set to ‘No’ by default, especially if the report contains users or other privacy related information. A public report can be accessed by anyone without authentication using the JSON report service.

image881

Note

If your system administrator has memcache installed on your server, you can see more options about caching results. This is similar to your public reports. If you make the report public, running it all the time may cause. create a big load on your system. Set this value to prevent this.

image1292

Step 2: Select report type. Currently ”in table form” is the only available option.

image882

Step 3: Select the fields you want to output in your report. Multiple fields can be selected and ctrl+clicked to apply to each copy. Then click the ‘Add’ button.

image883

Step 4: Select the restrictions you want to apply to your report (like media types or libraries). If you do not want to specify any restrictions, click Next without selecting anything.

image884

Step 5: Perform mathematical calculations. If you do not want to calculate anything, click Next and do not enter anything.

image885

Step 6: Set the order of the data. If you want the data to be printed in the order they are in the database, click ‘Exit’.

image886

When this step is completed, the SQL generated by the wizard will be displayed. From here, you can save the report or copy the SQL statement and edit it even further manually.

image887

If you decide to save the report, you will be asked to enter a name for the report and to select the group and subgroup in which the report should be sorted.

image888

After the report has been saved, it will appear under ‘Saved Reports’ with all the other saved reports.

image889

  • You can sort the columns of this table using the ref: Table configuration<column-settings-label> customize in the administration module (table ID: table_reports).

From here you can edit the report, run it, or set a time to run it automatically. To find the report you have created, you can sort the different columns by clicking on the column header (the creation date is the fastest way to find newly created reports). You can also narrow down the list of reports using the filters on the left or select between different custom groups using tabs.

SQL report

In addition to the wizards you have the possibility to write your own reports with SQL. In the Koha Wiki you can find reports written by other users: http://wiki.koha-community.org/wiki/SQL_Reports_Library. Information about the database structure can be found at /installer/data/mysql/cohastructure.sql or online at: http://schema.koha-community.org [http://schema.koha-community.org/] .

To add your own queries, click the ‘New SQL Report’ link on the ‘Reports’ page, or the ‘New Report’ button at the top of the ‘Saved Reports’ page.

Fill in the displayed form

image891

The name is used to identify the report later. It will be displayed in the list of saved reports and can be searched there as well.

You can use groups to organize your reports, which you can easily search and filter by. These groups are defined in the administration under ‘Normalized values’ in the category REPORT_GROUP or created when creating a new report.

Report groups defined in the normalized values need unique normalized values and descriptions.

Note

Remember that the code for a new group should be less than 10 characters long and should not contain special characters, or.

You can also further divide your reports into subgroups so that you can easily filter your reports by groups and subgroups. These subgroups are defined in the administration under "Normalized values" in the category REPORT_SUBGROUP or can be created when a new report is created. Report subgroups have unique values in the ‘normalized values’ and in the ‘description’. The field "Description (OPAC)" must receive the normalized value of the report group to which the subgroup is assigned.

Note

Remember that the code for the new subgroup should be less than 10 characters long and should not contain special characters and spaces.

especially if the report contains user or other privacy related information. A public report can be retrieved by anyone without authentication using the JSON report service.

Also ‘notes’ are displayed on the overview page for saved reports and can be used to store more information about the report or tips for running it.

The type ‘Tabular’ should always be selected, because the other formats are not implemented yet.

In the SQL input field, you can type or copy in the SQL query.

Note

If your system administrator has memcache installed on your server, you can see more options about caching the results. This is similar to your public reports. If you make the report public, by constantly running if necessary. Create a large load on your system. Set this value to prevent this.

When everything is entered, click on ‘Save report’ and options for execution are displayed. After a report has been saved, it can be easily executed run or edited edit from the list of saved reports.

Reports dictionary

Parameters for the term

If you feel that your report is too resource-intensive, you can store so-called runtime parameters in the query. This provides filters before running the report, which can help save your resources in the system.

There is a special syntax in Koha to set values when the report runs. The syntax is<<Question to ask|authorized_value>>.

<< and>> are only separators. You must<< to the beginning and>> to be put at the end of your parameter.

The ‘question’ is displayed to the left of the term to be entered.

If you are not authoritative, the normalized values authorized_value can be skipped. If it contains a normalized value like ‘libraries’, ‘media type’ or a ‘category code’ ‘biblio_framework’, instead of a free field a list of normalized values is displayed in Koha.

Note

You can have more than one parameter displayed in an SQL query.

Note

You must enter a "%" in the text box to "leave it blank". Otherwise it shows "" (empty string) as the value of the field. For example, if you enter nothing in the "title" field "title=<<enter title>>", you will be shown results with title=(no title). If you want it to be non-mandatory, type "title like<<insert title>>" and instead of leaving it blank, put a% in the runtime field.

Note

If you want to display a calendar widget to the right of the field when running a report, you can use the keyword ‘date'<<date|date>> use in this form.

A list of parameters that can be used in the runtime parameters.

What the user sees

What is inserted in the query

date branches itemtypes categorycode biblio_framework (auth-value-category) (nothing)

Time windows pop up in a text box for library names, media types, user types, MARC frameworks, and descriptions of normalized values.

valid formatted date library code media type user type code framework code and description for normalized value

  • 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 =<<choose your library|branches>> and barcode like<<part of the barcode>>

  • SELECT title, author FROM biblio WHERE frameworkcode=<<enter frameworkcode|biblio_framework>>

SQL report from Mana

Under "Create report with SQL" you can browse the Mana KB and import existing reports by clicking on "New report" and selecting "New SQL report from Mana".

image1428

Note

This option is displayed only if you have configured the Mana KB in the Administration module.

You will be prompted to enter keywords to search the knowledge base.

In the search results you will see

edit the report details (name, notes and type)

How many people have used this entry

when it was last used (last import)

additional comments from other Koha users

image1430

Click on "Import" to add a copy of the report to your own saved reports.

you can edit, copy, delete and run it as you do all your own reports.

Duplicate report

Reports can also be created by copying an existing report. Under ‘Saved Reports’ you will find all reports that are currently available in the system.

image895

On the right side of each report you will find an ‘Actions’ menu. Click on this and select ‘Duplicate’ to use an existing report as a starting point for a new report. You can then customize and save the SQL accordingly.

Edit custom reports

Each report can be edited from the list of reports. To see a list of all saved reports, select ‘Saved Reports’.

image889

  • You can customize the columns of this table via ref: table-configuration<column-settings-label> customize it in the Administration module (table ID: table_reports).

to find the report you can sort the table by clicking on the column headers. You can also narrow down the list of displayed reports using the filters on the left or switch between different user-defined groups using the tabs.

From this list, you can edit your custom report by clicking on ‘Actions’ to the right of the report and selecting ‘Edit’ from the menu.

image897

A form is displayed, which can be used to edit the report.

image898

Run custom reports

If a custom report has been saved in Koha, you can find it under ‘Saved Reports’ and run it from the ‘Run’ entry in the ‘Actions’ menu.

image899

When you run the report, you will be asked to provide some more details.

image900

or you can get the result list immediately

image901

From the results page you can re-run the report, edit it or create a new report using the buttons at the top of the page. You can also download the result of the query by selecting a file type and then clicking ‘Download’.

Note

A comma-separated text file is a CSV file and can be opened with any spreadsheet program.

Send report results to the batch processing tools

After running a report that contains copy numbers, title record numbers and/or badge numbers, you can jump directly to the corresponding batch editing tools by clicking the ‘Batch operations with X visible records’ button. The X depends on how many records are displayed in the table (up to 1000).

image1343

Statistical Reports

Statistics reports show you number and totals. These reports are all about numbers and statistics. If you need reports with more detailed information, use the Guided Report Wizard Guided Report Wizard . These reports are often limited in what data they can output, so it is recommended to use custom reports for annual statistics.

Acquisition Statistics

Note

These reports offer only limited possibilities, so it is often useful to use custom reports for official or year-end statistics.

Use the form to specify which value should be displayed later in the column and which in the row.

image902

If you choose to run the output in the browser, the results will be displayed directly.

image903

You can also export a file and then edit it with other tools.

When you create a report, you can choose between the number and the sum of values.

If you select Total, the hit list will display the totals of the fees paid.

image905

User statistics

Note

These reports offer only limited possibilities, so it is often useful to use custom reports for official or year-end statistics.

Use the form to specify which value should be displayed later in the column and which in the row.

image906

If you choose to output to the browser, the results will be displayed directly.

image907

Based on your selection, information is displayed above the results table. You can also decide to export the file and further edit it in another program.

Catalog statistics

Note

These reports offer only limited possibilities, so it is often useful to use custom reports for official or year-end statistics.

Use the form to specify which value should be displayed later in the column and which in the row.

image908

If you choose browser output, the results will be displayed directly.

image909

You can also export a file and then further edit it with other tools.

Lending statistics

Note

These reports offer only limited possibilities, so it is often useful to use custom reports for official or year-end statistics.

Use the form to specify which value should be displayed later in the column and which in the row.

image910

If you choose to output to the browser, the results will be displayed directly.

image911

You can also export a file and then further edit it with other tools.

  • Use the date range for daily statistics. </br>Example: "I want to see loan statistics from date XXX upwards, but without date XXX."

  • For a whole month, a date range would be for example: 01/11/2009 to 01/12/2009

  • For a full year, the date range would be, for example, 01/01/2009 to 01/01/2010

  • For a single tag, enter z.B. 15/11/2009 to 16/11/2009 to find out what happened on 15. has been lent

Record on-site usage

If you are using the Circulation Statistics under Statistics Wizards, you can run the report for on-site use of copies by selecting "Local Use" in the drop-down list for "Type":

image912

Journal statistics

Note

These reports offer only limited possibilities, so it is often useful to use custom reports for official or year-end statistics.

Use the form to specify how journals should be listed in your system.

image913

If you choose to output in the browser, the results will be displayed directly.

You can also export a file and then edit it with other tools.

Pre-registration statistics

Note

These reports offer only limited possibilities, so it is often useful to use custom reports for official or year-end statistics.

If you use the statistics form, you can create statistics about set, fulfilled and cancelled reservations and others. Choose which information should be displayed in the rows and which in the columns. You can also use the filters on the right side of the form.

image915

If you choose to output to the browser, the results will be displayed directly.

You can also export a file and then edit it further with other tools.

Users with the most borrowings

This report shows you which users have borrowed the most copies.

image917

If you choose to output in the browser, the results will be displayed directly.

image918

You can also export a file and then edit it further with other tools.

Most borrowed items

This report shows you which copies were borrowed most often.

image919

If you choose the output in the browser, the results are displayed directly.

image920

You can also export a file and then edit it with other tools.

User without borrowing

This report shows which users have not borrowed anything at the moment.

image921

If you choose to output in the browser, the results are displayed directly.

image922

You can also export a file and then edit it further with other tools.

Copies without borrowing

This report lists all items in stock that have never been checked out.

image923

If you choose to output to the browser, the results are displayed directly.

image924

You can also export a file and then further edit it with other tools.

Holdings by media type

This report lists how many copies of each media type are in the system for each library.

If you choose to output in the browser, the results are displayed directly.

image926

You can also export a file and then edit it further with other tools.

Missed specimens

This report shows you a list of specimens which are marked as "Lost" in the system.

image927

Once you have set the parameters, you will see the corresponding list.

  • You can change the columns of this table using the ref: Table configuration<column-settings-label> adjust in the administration module (table ID: lostitems-table).

Orders by accounts

If you use the acquisition module, you can see here what has been ordered for a single account.

image1499

After selecting the account you want to view, you can either export the results or view them directly in the browser. If you select the display in the browser, you will see a list of orders.

Example of a report result of Orders by fund

  • You can change the columns of this table using the ref: table-configuration<column-settings-label> in the Administration module (Table ID: funds-table).

Average rental time

This report shows the average duration of a copy that is on loan, based on the criteria entered:

image928

If you choose to output in the browser, the results will be displayed directly.

image929

You can also export a file and then edit it with other tools.

Reports dictionary

The report dictionary is a way to store frequently used criteria for your reports. It is a good way to create filters that the report wizard does not offer on its own. To add a new definition or filter, click on ‘New Definition’ on the Report Dictionary home page and follow the 4 steps below.

Step 1: Name the definition and enter a description

image930

Step 2: Select module

image931

Step 3: Select the columns for the report from the displayed tables

image932

Step 4: Select the values of the fields. These are automatically filled with values from your database.

image933

Confirm your selection to save the definition.

image934

Your definitions are displayed on the dictionary page

image935

If you then create a new report, in the module for which the definition was created, it will be offered to you in the list of filters.

Like this post? Please share to your friends:
Leave a Reply

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: