6. Reports

6.1. Define Codes Stored in DB

6.1.1. Fines Table

Question: What do the codes in the accounttype field in the accountlines table stand for?

Answer:

  • A = acc't management fee

  • C = credit

  • F = overdue fine

  • FOR =forgiven

  • FU = overdue, still acccruing

  • L = Lost Item

  • M = Sundry

  • N = New Card

  • PAY = payment

  • W = writeoff

6.1.2. Statistics Table

Question: What are the possible codes for the type field in the statistics table?

Answer:

  • localuse

    • Registers if an item that had been checked out to a statistics patron (category type = 'X') is returned

  • issue

  • return

  • renew

  • writeoff

  • payment

  • CreditXXX

    • stores different types of fee credits, so a query to catch them all would include a clause like "type LIKE 'Credit%'"

6.1.3. Reserves Table

Question: What are the possible codes for the found field in the reserves and old_reserves tables?

Answer:

  • NULL: means the patron requested the 1st available, and we haven't choosen the item

  • T = Transit: the reserve is linked to an item but is in transit to the pickup branch

  • W =Waiting: the reserve is linked to an item, is at the pickup branch, and is waiting on the hold shelf

  • F = Finished: the reserve has been completed, and is done

6.1.4. Reports Dictionary Table

Question: What are the possible codes for the area field in the reports_dictionary table?

Answer:

  • 1 = Circulation

  • 2 = Catalog

  • 3 = Patrons

  • 4 = Acquistions

  • 5 = Accounts

6.1.5. Messages Table

Question: What are the possible codes for the message_type field in the messages table?

Answer:

  • L = For Librarians

  • B = For Patrons/Borrowers

6.1.6. Serial Table

Question: What are the possible codes for the status field in the serial table"?

Answer:

  • 1 = Expected

  • 2 = Arrived

  • 3 = Late

  • 4 = Missing

  • 5 = Not Available

  • 6 = Delete

6.2. Runtime Parameters

Question: Is there a way to filter my custom SQL reports before they run?

Answer: If you feel that your report might be too resource intesive 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, or branches or itemtype or categorycode, a list with the Koha authorized values will be displayed instead of a free field Note that you can have more than one parameter in a given SQL Note that entering nothing at run time won't probably work as you expect. It will be considered as "value empty" not as "ignore this parameter". 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

Examples:

  • SELECT surname,firstname FROM borrowers WHERE branchcode=<<Enter patrons 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>>

Tip

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.

Important

The only supported dropdowns at this time are Branches (branches), Item Types (itemtypes) and Patron Categories (categorycode).

6.3. Results Limited

Question: When I download my report it's limited to 10,000 results, how do I get all of the results to download?

Answer: 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.