Hi team, I am trying to create a report that allows me to get circulation statistics based on the category_type from the categories table rather than categorycode. Putting the values in the query e.g. I for organization patrons, A for adults e.t.c. works well. However, I need to be able to select from a list. I do not know the authorized value for that hence seek your help. I have tried the below filter which says category_type is not an authorized value. WHERE categories.category_type= <<CATEGORY|category_type>> If anyone can help me on the above issue and/or help me know how/where to find such authorized values, I'd be so grateful. Warm Regards James.
WHERE categories.category_type= <<CATEGORY|category_type>>
category_type is an odd one. It's not an authorized value. In fact I don't think they're defined anywhere in the database at all, just hard-coded into templates and scripts. They're intended to be fixed values, not ones which can be modified. I don't think there's any way to get an SQL report to generate a set of options based on these values. It would be nice if one could supply a set of user-defined options in a runtime parameter, e.g. <<Category type|[{A:"Adult",C:"Child"}]>> but that would be complicated for users. I think in your case you might have to rely on good documentation in the report notes to let the user know which category_type codes are valid. -- Owen -- Web Developer Athens County Public Libraries (740) 737-6006 https://www.myacpl.org
Hi Owen and All, Thank you for your quick reply and very good advice. I will follow your idea on good documentation in the report. I have no knowledge on runtime parameters but thanks for that pointer too. If you can increase my knowledge on how to tell an authorized value or where to look I'd be grateful. I have used the 'itemtypes' authorized value (as copied from the koha reports library) but I'm always curious to learn more about it or at least understand it better. (-- WHERE items.itype= <<Itemtype|itemtypes>>) Thanks a lot Regards James On Thu, Aug 12, 2021 at 3:24 PM Owen Leonard <oleonard@myacpl.org> wrote:
WHERE categories.category_type= <<CATEGORY|category_type>>
category_type is an odd one. It's not an authorized value. In fact I don't think they're defined anywhere in the database at all, just hard-coded into templates and scripts. They're intended to be fixed values, not ones which can be modified.
I don't think there's any way to get an SQL report to generate a set of options based on these values. It would be nice if one could supply a set of user-defined options in a runtime parameter, e.g. <<Category type|[{A:"Adult",C:"Child"}]>> but that would be complicated for users. I think in your case you might have to rely on good documentation in the report notes to let the user know which category_type codes are valid.
-- Owen
-- Web Developer Athens County Public Libraries (740) 737-6006 https://www.myacpl.org
Hello James! You can look at the SQL library on the Koha wiki. In the first sections, there is one about runtime parameters that explains it very well. https://wiki.koha-community.org/wiki/SQL_Reports_Library#Runtime_Parameters I think there are a couple of options you can use. 1) Create your own authorized values list As it is possible to use any authorized values list as runtime parameter, you can create an AV list just for this report. Since the codes aren't likely to change, this could be a good option. - Go to Administration > Authorized values - Click on New category - Create a code for your category (e.g. BORCAT) - Click on New authorized value for XX - Add the authorized values for the category types, for example Authorized value = A Description = Adult The other category types are C = Child I = Organization P = Professional S = Staff X = Statistical (see https://koha-community.org/manual/latest/en/html/faq.html#patron-categories-...) - In your report, add the runtime parameter just like you would for itemtypes for example WHERE category_type = <<Category|BORCAT>> I just tried it in 20.05 and it works. 2) Add a simple text field as runtime parameter I think this was Owen's suggestion. - In your report, add a runtime parameter without av list, for example WHERE category_type = <<Category>> - As previously mentioned, users will have to know the specific codes to enter in the field (A, C, I, P, S or X), otherwise the report will not give any results. - As of version 21.05, there is an option to make a text field a text area for several values https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27380 Regards, Caroline On 2021-08-12 10 h 30, muiru james wrote:
Hi Owen and All,
Thank you for your quick reply and very good advice. I will follow your idea on good documentation in the report. I have no knowledge on runtime parameters but thanks for that pointer too.
If you can increase my knowledge on how to tell an authorized value or where to look I'd be grateful. I have used the 'itemtypes' authorized value (as copied from the koha reports library) but I'm always curious to learn more about it or at least understand it better. (-- WHERE items.itype= <<Itemtype|itemtypes>>)
Thanks a lot
Regards James
On Thu, Aug 12, 2021 at 3:24 PM Owen Leonard <oleonard@myacpl.org> wrote:
WHERE categories.category_type= <<CATEGORY|category_type>> category_type is an odd one. It's not an authorized value. In fact I don't think they're defined anywhere in the database at all, just hard-coded into templates and scripts. They're intended to be fixed values, not ones which can be modified.
I don't think there's any way to get an SQL report to generate a set of options based on these values. It would be nice if one could supply a set of user-defined options in a runtime parameter, e.g. <<Category type|[{A:"Adult",C:"Child"}]>> but that would be complicated for users. I think in your case you might have to rely on good documentation in the report notes to let the user know which category_type codes are valid.
-- Owen
-- Web Developer Athens County Public Libraries (740) 737-6006 https://www.myacpl.org
_______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Hello Caroline, Owen and all, I created the authorized values as you suggested and they work exactly as I want. More so I am now able to understand runtime parameters, I could not really tell what they were. I don't know what I would do without koha lists. Thank you so much. Very warm Regards. James On Thu, Aug 12, 2021 at 7:34 PM Caroline Cyr-La-Rose < caroline.cyr-la-rose@inlibro.com> wrote:
Hello James!
You can look at the SQL library on the Koha wiki. In the first sections, there is one about runtime parameters that explains it very well. https://wiki.koha-community.org/wiki/SQL_Reports_Library#Runtime_Parameters
I think there are a couple of options you can use.
1) Create your own authorized values list
As it is possible to use any authorized values list as runtime parameter, you can create an AV list just for this report. Since the codes aren't likely to change, this could be a good option.
- Go to Administration > Authorized values
- Click on New category
- Create a code for your category (e.g. BORCAT)
- Click on New authorized value for XX
- Add the authorized values for the category types, for example
Authorized value = A
Description = Adult
The other category types are
C = Child
I = Organization
P = Professional
S = Staff
X = Statistical
(see
https://koha-community.org/manual/latest/en/html/faq.html#patron-categories-... )
- In your report, add the runtime parameter just like you would for itemtypes for example
WHERE category_type = <<Category|BORCAT>>
I just tried it in 20.05 and it works.
2) Add a simple text field as runtime parameter
I think this was Owen's suggestion.
- In your report, add a runtime parameter without av list, for example
WHERE category_type = <<Category>>
- As previously mentioned, users will have to know the specific codes to enter in the field (A, C, I, P, S or X), otherwise the report will not give any results.
- As of version 21.05, there is an option to make a text field a text area for several values https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27380
Regards,
Caroline
On 2021-08-12 10 h 30, muiru james wrote:
Hi Owen and All,
Thank you for your quick reply and very good advice. I will follow your idea on good documentation in the report. I have no knowledge on runtime parameters but thanks for that pointer too.
If you can increase my knowledge on how to tell an authorized value or where to look I'd be grateful. I have used the 'itemtypes' authorized value (as copied from the koha reports library) but I'm always curious to learn more about it or at least understand it better. (-- WHERE items.itype= <<Itemtype|itemtypes>>)
Thanks a lot
Regards James
On Thu, Aug 12, 2021 at 3:24 PM Owen Leonard <oleonard@myacpl.org> wrote:
WHERE categories.category_type= <<CATEGORY|category_type>> category_type is an odd one. It's not an authorized value. In fact I don't think they're defined anywhere in the database at all, just hard-coded into templates and scripts. They're intended to be fixed values, not ones which can be modified.
I don't think there's any way to get an SQL report to generate a set of options based on these values. It would be nice if one could supply a set of user-defined options in a runtime parameter, e.g. <<Category type|[{A:"Adult",C:"Child"}]>> but that would be complicated for users. I think in your case you might have to rely on good documentation in the report notes to let the user know which category_type codes are valid.
-- Owen
-- Web Developer Athens County Public Libraries (740) 737-6006 https://www.myacpl.org
_______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
participants (3)
-
Caroline Cyr-La-Rose -
muiru james -
Owen Leonard