Question: What do the codes in the accounttype field in the accountlines table stand for?
Answer:
-
A = Account management fee
-
C = Credit
-
F = Overdue fine
-
FOR = Forgiven
-
FU = Overdue, still acccruing
-
L = Lost item
-
LR = Lost item returned/refunded
-
M = Sundry
-
N = New card
-
PAY = Payment
-
W = Writeoff
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
-
The XXX stores different types of fee credits, so a query to catch them all would include a clause like "type LIKE 'Credit%'"
-
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 chosen 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
Question: What are the possible codes for the area field in the reports_dictionary table?
Answer:
-
1 = Circulation
-
2 = Catalog
-
3 = Patrons
-
4 = Acquisitions
-
5 = Accounts
Question: What are the possible codes for the message_type field in the messages table?
Answer:
-
L = For Librarians
-
B = For Patrons/Borrowers
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
-
7 = Claimed
-
8 = Stopped
-
41 = Missing (not received)
-
42 = Missing (sold out)
-
43 = Missing (damaged)
-
44 = Missing (lost)
Question: What are the possible codes for the privacy field in the borrowers table?
Answer:
-
0 = Forever
-
1 = Default
-
2 = Never
Question: What are the possible codes in the message_attribute_id field in the borrower_message_preferences table?
Answer:
-
2 = advanced notice
-
6 = item checkout
-
4 = hold filled
-
1 = item due
-
5 = item check in
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 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, or branches or itemtype or categorycode or biblio_framework, 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>>
-
SELECT title , author FROM biblio WHERE frameworkcode=<<Enter the frameworkcode|biblio_framework>>
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>>
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.
Important
In addition to using any authorized value code to generate a dropdown, you can use the following values as well: Framework codes (biblio_framework), Branches (branches), Item Types (itemtypes) and Patron Categories (categorycode). For example a branch pull down would be generated like this <<Branch|branches>>
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.