It would be helpful to have reports that are specific groups/categories to be authorized under the reports permissions in a patron account. Not everyone wants to see everyone else's reports, some are similar but are not. Some are for superlibrarians to test, and are not ready for use. It would be great if we could specify groups and even subgroups in the permissions that the staff could access, or specify all groups. All groups should be the norm for a superlibrarian. Christopher
This is something we would be interested in to as there are some reports we would like to give to the Stock Librarians only and so restrictions can be places on when they are run too.
*** Bug 10903 has been marked as a duplicate of this bug. ***
Would also be nice to limit access to reports by the home library of the staff. Some reports are specific to the needs of just one library. Some reports are in development for a while. And so on. As reports proliferate over time, libraries would welcome any option that reduces the number of reports visible to them.
Having the ability to limit who can see which reports would help a lot at PCCLD. It would certainly clean up the report module and make relevant reports easier for staff to find.
(In reply to Cab Vinton from comment #3) > Would also be nice to limit access to reports by the home library of the > staff. > > Some reports are specific to the needs of just one library. Some reports are > in development for a while. And so on. > > As reports proliferate over time, libraries would welcome any option that > reduces the number of reports visible to them. +1
(In reply to Daniel Gaghan from comment #4) > Having the ability to limit who can see which reports would help a lot at > PCCLD. It would certainly clean up the report module and make relevant > reports easier for staff to find. +1
Perhaps a branch field and a borrower field could be added. If the field is empty, it applies to everyone. Otherwise, it would be limited to matching borrower IDs or branches.
Shame this enhancement hasn't got any traction, but I thought of a kludge that would work in a pinch -- Add a comment with a unique string to all reports in a particular grouping (e.g., for circ staff, a particular branch, etc.), then use the string in the keyword filter on the left. Only reports with that particular string will then be shown in the Reports module. In theory, multiple strings could be used to cover reports in more than one group. I believe the keyword filter is actually doing a phrase search, though, so some careful planning would be required. As I said, a kludge ...
We're also interested in this enhancement for reports. We've accumulated a large amount of reports since joining in 2014. Many of these are kept for reference and as a record. Since these are not useful for the majority of our staff, we'd like these to be hidden in a restricted Report Group. It would make it a clutter-free experience for staff to search reports while allowing access to those that may need it.
+1 There are many reports that libraries would like that only apply to their own daily operations. It would be helpful to be able to hide these from most users. In addition, something like this would allow pre-baked reports for cron emailing to be hidden from those libraries not specified in the report parameters.
+1 The ability to have smaller lists for each staff to browse through to find the report they want would be very helpful. Some staff only use a handful of reports but they have to memorize the title/number for the reports to find them again. Would love to see this feature.
Created attachment 184422 [details] koha patch file
Created attachment 184508 [details] Bug 16631: 0001-reports_branches-table-SQL-established
Created attachment 184509 [details] Bug 16631: 0002-Make-DBversion-in-sync-with-the-Koha-module
Created attachment 184510 [details] Bug 16631: 0003-Add-Filter-Reports-system-preference.-Add-UI-for-man
Created attachment 184511 [details] Bug 16631: 0004-Add-ReportsBranch.pm
Created attachment 184512 [details] Bug 16631: 0005-Not-working.-Trying-to-get-branches-to-show-up-in-gu
Created attachment 184513 [details] Bug 16631: 0006-The-reports-list-can-be-limited-by-library-via-the-q
Created attachment 184514 [details] Bug 16631: 0007-Add-branch-libraries-to-reports-edit-view
Created attachment 184515 [details] Bug 16631: 0008-Populate-Library-limitations-selection-with-librarie
Created attachment 184516 [details] Bug 16631: 0009-Get-selected-reports-and-save-selected-reports-from-
Created attachment 184517 [details] Bug 16631: 0010-Refactor-getting-selected-lirbaries
Created attachment 184518 [details] Bug 16631: 0011-Add-library-limits-info-to-duplicate-section-and-fix
Created attachment 184519 [details] Bug 16631: 0012-Add-unit-tests-for-reports_branches-updates
Created attachment 184520 [details] Bug 16631: 0013-Pass-the-current-branch-as-the-filter-branchcode
Created attachment 184521 [details] Bug 16631: 0014-Fix-library-limit-method-issue-by-defining-use-state
Created attachment 184522 [details] Bug 16631: 0015-Filtering-the-list-based-on-the-library-limits-works
A few obvious standout problems here: 1. Not sure what your 0005 file is there but it looks like it's not a patch 2. Database updates aren't done via updatedatabase.pl anymore. You'll want to read through https://wiki.koha-community.org/wiki/Database_updates 3. Commit author sometimes seems to be different to the person uploading. Also, sometimes commit author the default one from KTD. Would be good to fix/clarify these. 4. There are no commit messages and no test plan. Take a look at https://wiki.koha-community.org/wiki/Commit_messages 5. Looks like some patches would fail QA based on whitespace and formatting alone. Would be good for the author to use koha-testing-docker from development to avoid those kinds of errors Probably worth squashing a lot of these overall. I'm not sure about this design, but I haven't exhaustively reviewed the code. In any case, great to see some code trying to better organise the reports!
Created attachment 184525 [details] Bug 16631: Update Report Listing
Created attachment 184526 [details] [review] Bug 16631: Patch
(In reply to David Cook from comment #28) > Probably worth squashing a lot of these overall. Apologies. I meant squashing ones that changed the same files. Just trying to keep things a bit more cohesive and coherent. (In reply to Casey Conlin from comment #30) > Created attachment 184526 [details] [review] [review] > Bug 16631: Patch This looks very weird. Looks like you've got a rebase problem there.
Created attachment 184657 [details] [review] Bug-16631
Created attachment 184674 [details] [review] Patch file for reports enhancement.
Created attachment 184686 [details] This patch adds the reports_branches table to enforce library limits for reports, and adjusts the saved_sql table to point to reports_branches.
Created attachment 184687 [details] This patch implements the library_limit concept for Report/Reports. Staff can set libaries to which the report should be visible, if the system preference is enabled.
Created attachment 184688 [details] [review] Add library limits to reports.
Created attachment 184689 [details] [review] Add-library-limits-to-Reports
Created attachment 184690 [details] [review] Add library limits to Reports
Created attachment 184691 [details] [review] 0001-Bug-16631-Institute-library-limits
Created attachment 184692 [details] [review] 0002-Bug-16631-Fix-bug_16631
Created attachment 184693 [details] [review] 0003-Bug-16631-Fix-missing-double-quote-in-guided_reports
Created attachment 184694 [details] [review] 0001-Bug-16631-DO-NOT-PUSH-Add-schema-files-for-reports_b (1)
Created attachment 184695 [details] [review] 0002-Bug-16631-Institute-library-limits (1)
Created attachment 184845 [details] [review] Bug 16631: Add schema files for reports_branches and adjust saved_sql table
Created attachment 184846 [details] [review] Bug 16631: Institute library limits
Created attachment 184847 [details] [review] Bug 16631: [DO NOT PUSH] Add schema files for reports_branches and adjust saved_sql table
Created attachment 184848 [details] [review] Bug 16631: Institute library limits
(In reply to David Cook from comment #31) > (In reply to David Cook from comment #28) > > Probably worth squashing a lot of these overall. > > Apologies. I meant squashing ones that changed the same files. Just trying > to keep things a bit more cohesive and coherent. > > (In reply to Casey Conlin from comment #30) > > Created attachment 184526 [details] [review] [review] [review] > > Bug 16631: Patch > > This looks very weird. Looks like you've got a rebase problem there. Thank you for the notes, David! This should be ready for review now. Any questions or comments are welcome.
Comment on attachment 184847 [details] [review] Bug 16631: [DO NOT PUSH] Add schema files for reports_branches and adjust saved_sql table >From 04bb1e1220d059f1d28b48de8a5d095912311ef6 Mon Sep 17 00:00:00 2001 >From: "Cameron E. Tidd" <ctidd@citlink.net> >Date: Sat, 26 Jul 2025 02:02:50 +0000 >Subject: [PATCH 1/2] Bug 16631: [DO NOT PUSH] Add schema files for > reports_branches and adjust saved_sql table >MIME-Version: 1.0 >Content-Type: text/plain; charset=UTF-8 >Content-Transfer-Encoding: 8bit > >This patch adds the reports_branches table to enforce library limits for reports, and adjusts the saved_sql table to point to reports_branches. > > Sponsored-by: The Ramapo Catskill Library System >Test plan: >Confirm Initial Settings >1. Log in to Koha staff and set your library as Centerville if itâs not already. >2. Visit the Saved reports. >3. Three (3) previously created reports should be visible. > >Verify Patch Settings >4. From the Koha Sandbox, Apply the patch, then Restart services. >5. Go to the System Preferences page (Click the More dropdown from the top nav bar, click Administration, click System Preferences). >6. Verify Reports is an option in the left side navigation bar, and that when clicked, you see the EnableFilteringReports option. >7. Enable EnableFilteringReports as described in step 6. >8. Go back to Saved reports as in Step 2. >9. Click Edit in the row menu for Report 1. >10. At the bottom of the Report 1 edit page, you should see a Library limitation select box. > >Verify the Library Limitation Settings are Persisted >11. Choose Centerville from the select box and click Update SQL. >12. Reload the page, or navigate away from the page and go back to the edit screen for Report 1. >13. Verify that Centerville is still in the Library limitation select box. >14. Add Fairfield in the Library limitation select box and click Update and run SQL. >15. Go back to editing Report 1. >16. Confirm the Library limitation select box has both Centerville and Fairfield selected. >17. Duplicate Report 1. >18. Confirm the Library limitation select box has both Centerville and Fairfield selected. >19. Click Save Report. >20. Confirm the Library limitation select box has both Centerville and Fairfield selected. > >Verify Reports are Filtered Based on Library Limitations >21. Go to Saved reports as in Step 2. >22. Click Edit for Report 2. >23. Add Fairfield in the Library limitation select box as the only choice and click Update and run SQL. >24. Go to Saved reports as in Step 2. >25. Confirm you do not see Report 2, and only Report 1 and Report 3. >26. In the upper right corner, click the drop down that says koha Centerville and click Set library to change your library. >27. Set your library to Fairfield. >28. Go to Saved reports as in Step 2. >29. Confirm you do not see Report 1, and only Report 2 and Report 3. > >Verify Disabling EnableFilteringReports Preference Shows All Reports >30. Disable EnableFilteringReports preference as in steps 5 to 7. >31. Go to Saved reports as in Step 2. >32. Confirm Report 1, Report 2, Report 3 are visible. >33. Click to edit Report 1 and verify there is no Library limitation select box. > >Verify Library limitations are Persisted When EnableFilteringReports is Toggled On or Off >34. Enable EnableFilteringReports preference as in steps 5 to 7. >35. Ensure youâre library is set to Fairfield as in step 26. >36. Go to Saved reports as in Step 2. >37. Confirm Report 1 is not visible, and Report 2 and Report 3 are visible. >38. Click to edit Report 2 and verify the Library limitation select box is at the bottom of the page and that it has Fairfield selected. > >Run Unit Tests >39. In the Koha home directory run the command: prove t/db_dependent/Koha/Reports.t >40. The new unit tests for the reprots_branches tables are in this file >41. All tests should pass. > >Signoff >42. Sign off if the steps above checkout. >--- > Koha/Schema/Result/ReportsBranch.pm | 86 ++++++++++++++ > Koha/Schema/Result/SavedSql.pm | 220 ++++++++++++++++++++++++++++++++++++ > 2 files changed, 306 insertions(+) > create mode 100644 Koha/Schema/Result/ReportsBranch.pm > >diff --git a/Koha/Schema/Result/ReportsBranch.pm b/Koha/Schema/Result/ReportsBranch.pm >new file mode 100644 >index 0000000000..80472a5ff1 >--- /dev/null >+++ b/Koha/Schema/Result/ReportsBranch.pm >@@ -0,0 +1,86 @@ >+use utf8; >+ >+package Koha::Schema::Result::ReportsBranch; >+ >+# Created by DBIx::Class::Schema::Loader >+# DO NOT MODIFY THE FIRST PART OF THIS FILE >+ >+=head1 NAME >+ >+Koha::Schema::Result::ItemtypesBranch >+ >+=cut >+ >+use strict; >+use warnings; >+ >+use base 'DBIx::Class::Core'; >+ >+=head1 TABLE: C<itemtypes_branches> >+ >+=cut >+ >+__PACKAGE__->table("reports_branches"); >+ >+=head1 ACCESSORS >+ >+=head2 report_id >+ >+ data_type: 'varchar' >+ is_foreign_key: 1 >+ is_nullable: 1 >+ size: 80 >+ >+=head2 branchcode >+ >+ data_type: 'varchar' >+ is_foreign_key: 1 >+ is_nullable: 0 >+ size: 10 >+ >+=cut >+ >+__PACKAGE__->add_columns( >+ "report_id", >+ { data_type => "varchar", is_foreign_key => 1, is_nullable => 1, size => 80 }, >+ "branchcode", >+ { data_type => "varchar", is_foreign_key => 1, is_nullable => 0, size => 10 }, >+); >+ >+=head1 RELATIONS >+ >+=head2 branchcode >+ >+Type: belongs_to >+ >+Related object: L<Koha::Schema::Result::Branch> >+ >+=cut >+ >+__PACKAGE__->belongs_to( >+ "branchcode", >+ "Koha::Schema::Result::Branch", >+ { branchcode => "branchcode" }, >+ { is_deferrable => 1, on_delete => "CASCADE", on_update => "RESTRICT" }, >+); >+ >+=head2 itemtype >+ >+Type: belongs_to >+ >+Related object: L<Koha::Schema::Result::Itemtype> >+ >+=cut >+ >+__PACKAGE__->belongs_to( >+ "report_id", >+ "Koha::Schema::Result::Report", >+ { report_id => "report_id" }, >+ { is_deferrable => 1, on_delete => "CASCADE", on_update => "RESTRICT" }, >+); >+ >+# Created by DBIx::Class::Schema::Loader v0.07048 @ 2019-07-04 04:56:48 >+# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:cBTswjKV8VWN1iueB+PygQ >+ >+# You can replace this text with custom code or comments, and it will be preserved on regeneration >+1; >diff --git a/Koha/Schema/Result/SavedSql.pm b/Koha/Schema/Result/SavedSql.pm >index 6f75ae3b31..88d54b52ef 100644 >--- a/Koha/Schema/Result/SavedSql.pm >+++ b/Koha/Schema/Result/SavedSql.pm >@@ -185,6 +185,221 @@ __PACKAGE__->add_columns( > > __PACKAGE__->set_primary_key("id"); > >+=head1 RELATIONS >+ >+=head2 reports_branches >+ >+Type: has_many >+ >+Related object: L<Koha::Schema::Result::ReportsBranch> >+ >+=cut >+ >+__PACKAGE__->has_many( >+ "reports_branches", >+ "Koha::Schema::Result::ReportsBranch", >+ { "foreign.report_id" => "self.id" }, >+ { cascade_copy => 0, cascade_delete => 0 }, >+); >+ >+ >+# Created by DBIx::Class::Schema::Loader v0.07051 @ 2025-07-26 01:58:59 >+# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:w7XKvOV/ylS6Dq8tsUNmSg >+# These lines were loaded from '/kohadevbox/koha/Koha/Schema/Result/SavedSql.pm' found in @INC. >+# They are now part of the custom portion of this file >+# for you to hand-edit. If you do not either delete >+# this section or remove that file from @INC, this section >+# will be repeated redundantly when you re-create this >+# file again via Loader! See skip_load_external to disable >+# this feature. >+ >+use utf8; >+package Koha::Schema::Result::SavedSql; >+ >+# Created by DBIx::Class::Schema::Loader >+# DO NOT MODIFY THE FIRST PART OF THIS FILE >+ >+=head1 NAME >+ >+Koha::Schema::Result::SavedSql >+ >+=cut >+ >+use strict; >+use warnings; >+ >+use base 'DBIx::Class::Core'; >+ >+=head1 TABLE: C<saved_sql> >+ >+=cut >+ >+__PACKAGE__->table("saved_sql"); >+ >+=head1 ACCESSORS >+ >+=head2 id >+ >+ data_type: 'integer' >+ is_auto_increment: 1 >+ is_nullable: 0 >+ >+unique id and primary key assigned by Koha >+ >+=head2 borrowernumber >+ >+ data_type: 'integer' >+ is_nullable: 1 >+ >+the staff member who created this report (borrowers.borrowernumber) >+ >+=head2 date_created >+ >+ data_type: 'datetime' >+ datetime_undef_if_invalid: 1 >+ is_nullable: 1 >+ >+the date this report was created >+ >+=head2 last_modified >+ >+ data_type: 'datetime' >+ datetime_undef_if_invalid: 1 >+ is_nullable: 1 >+ >+the date this report was last edited >+ >+=head2 savedsql >+ >+ data_type: 'mediumtext' >+ is_nullable: 1 >+ >+the SQL for this report >+ >+=head2 last_run >+ >+ data_type: 'datetime' >+ datetime_undef_if_invalid: 1 >+ is_nullable: 1 >+ >+=head2 report_name >+ >+ data_type: 'varchar' >+ default_value: (empty string) >+ is_nullable: 0 >+ size: 255 >+ >+the name of this report >+ >+=head2 type >+ >+ data_type: 'varchar' >+ is_nullable: 1 >+ size: 255 >+ >+always 1 for tabular >+ >+=head2 notes >+ >+ data_type: 'mediumtext' >+ is_nullable: 1 >+ >+the notes or description given to this report >+ >+=head2 cache_expiry >+ >+ data_type: 'integer' >+ default_value: 300 >+ is_nullable: 0 >+ >+=head2 public >+ >+ data_type: 'tinyint' >+ default_value: 0 >+ is_nullable: 0 >+ >+=head2 report_area >+ >+ data_type: 'varchar' >+ is_nullable: 1 >+ size: 6 >+ >+=head2 report_group >+ >+ data_type: 'varchar' >+ is_nullable: 1 >+ size: 80 >+ >+=head2 report_subgroup >+ >+ data_type: 'varchar' >+ is_nullable: 1 >+ size: 80 >+ >+=head2 mana_id >+ >+ data_type: 'integer' >+ is_nullable: 1 >+ >+=cut >+ >+__PACKAGE__->add_columns( >+ "id", >+ { data_type => "integer", is_auto_increment => 1, is_nullable => 0 }, >+ "borrowernumber", >+ { data_type => "integer", is_nullable => 1 }, >+ "date_created", >+ { >+ data_type => "datetime", >+ datetime_undef_if_invalid => 1, >+ is_nullable => 1, >+ }, >+ "last_modified", >+ { >+ data_type => "datetime", >+ datetime_undef_if_invalid => 1, >+ is_nullable => 1, >+ }, >+ "savedsql", >+ { data_type => "mediumtext", is_nullable => 1 }, >+ "last_run", >+ { >+ data_type => "datetime", >+ datetime_undef_if_invalid => 1, >+ is_nullable => 1, >+ }, >+ "report_name", >+ { data_type => "varchar", default_value => "", is_nullable => 0, size => 255 }, >+ "type", >+ { data_type => "varchar", is_nullable => 1, size => 255 }, >+ "notes", >+ { data_type => "mediumtext", is_nullable => 1 }, >+ "cache_expiry", >+ { data_type => "integer", default_value => 300, is_nullable => 0 }, >+ "public", >+ { data_type => "tinyint", default_value => 0, is_nullable => 0 }, >+ "report_area", >+ { data_type => "varchar", is_nullable => 1, size => 6 }, >+ "report_group", >+ { data_type => "varchar", is_nullable => 1, size => 80 }, >+ "report_subgroup", >+ { data_type => "varchar", is_nullable => 1, size => 80 }, >+ "mana_id", >+ { data_type => "integer", is_nullable => 1 }, >+); >+ >+=head1 PRIMARY KEY >+ >+=over 4 >+ >+=item * L</id> >+ >+=back >+ >+=cut >+ >+__PACKAGE__->set_primary_key("id"); >+ > > # Created by DBIx::Class::Schema::Loader v0.07049 @ 2021-01-21 13:39:29 > # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:198dNG9DGQzop+s5IHy7sw >@@ -214,3 +429,8 @@ sub koha_objects_class { > } > > 1; >+# End of lines loaded from '/kohadevbox/koha/Koha/Schema/Result/SavedSql.pm' >+ >+ >+# You can replace this text with custom code or comments, and it will be preserved on regeneration >+1; >-- >2.15.0 >
Created attachment 184859 [details] [review] Bug 16631: [DO NOT PUSH] Add schema files for reports_branches and adjust saved_sql table
Created attachment 184861 [details] [review] Bug 16631: Institute library limits
This patch is ready for review at https://sandboxes.biblibre.eu/
I tested this in Casey's sandbox and it worked as in the test plan. I can not run the unit test, so maybe someone else could do that part?
Hi Casey, Tests are failing here, can you have a look? t/db_dependent/Koha/Reports.t
(In reply to Lucas Gass (lukeg) from comment #54) > Hi Casey, > > Tests are failing here, can you have a look? > > t/db_dependent/Koha/Reports.t Hi, Lucas, thanks for taking a look here! When you ran the test command, were you in ktd shell? I omitted that step from my the test plan. run: ktd --shell kohadev-koha@kohadevbox:koha(16631-reports-libraries-filter-patch)$ t/db_dependent/Koha/Reports.t 1..10 ok 1 - Adding a new report should have set the id ok 2 - The 2 reports should have been added ok 3 - Find a report by id should return the correct report ok 4 - Delete should have deleted the report # Subtest: prep_report 1..4 ok 1 - Expected sql generated correctly with single param and name ok 2 - Expected sql generated correctly with multiple params and names ok 3 - Expected sql generated correctly with multiple params and no names ok 4 ok 5 - prep_report # Subtest: is_sql_valid 1..15 ok 1 - Empty sql is missing SELECT ok 2 - Nonsense sql is missing SELECT ok 3 - select FOO is good ok 4 - select FOO with UPDATE BAR ok 5 - UPDATE qux ok 6 - select FOO with DELETE BAR ok 7 - DELETE qux ok 8 - select FOO with DROP BAR ok 9 - DROP qux ok 10 - select FOO with INSERT BAR ok 11 - INSERT qux ok 12 - select FOO with SHOW BAR ok 13 - SHOW qux ok 14 - select FOO with CREATE BAR ok 15 - CREATE qux ok 6 - is_sql_valid # Subtest: check_columns 1..3 ok 1 - Bad column found in SQL ok 2 - No bad column found in SQL ok 3 - Check column_names parameter ok 7 - check_columns # Subtest: _might_add_limit 1..10 ok 1 - Pref is undefined, no changes ok 2 - Pref is zero, no changes ok 3 - Pref is empty, no changes ok 4 - Limit 10 found at the end ok 5 - Already contains a limit ok 6 - Variation, also contains a limit ok 7 - Subquery, limit 10 found at the end ok 8 - Subquery variation, limit 10 found at the end ok 9 - Subquery lc variation, limit 10 found at the end ok 10 - Query refers to limit field, limit 10 found at the end ok 8 - _might_add_limit # Subtest: reports_branches are added and removed from report_branches table 1..4 ok 1 - 3 branches added to report_branches table ok 2 - 1 branch removed from report_branches table ok 3 - Report deleted, count is back to original ok 4 - No branches left in reports_branches table after report deletion ok 9 - reports_branches are added and removed from report_branches table ok 10 - no warnings
(In reply to Lucas Gass (lukeg) from comment #54) > Hi Casey, > > Tests are failing here, can you have a look? > > t/db_dependent/Koha/Reports.t It might be necessary to run reset_all to update the db with the new tables before running the test command. I can add this to the test plan in the commit if necessary.
(In reply to Lucas Gass (lukeg) from comment #54) > Hi Casey, > > Tests are failing here, can you have a look? > > t/db_dependent/Koha/Reports.t Hi Lucas. I see a couple changes to the status here and it's back at Needs Signoff now. Were you able to run the tests? Are we still at Needs Signoff?
Main Library Alliance would like to make a comment regarding consortium use, partially relating back to the original submission by Christopher Brannon. While this feature may definitely prove useful to us, particularly for test reports, it didn't seem like there was a way for a superlibrarian to "view all" when demoing on the sandbox, unless I missed an option somewhere. In smaller systems this may not be as much of an issue, but in larger systems (56 and counting here, not counting lockers & bookmobiles) an admin having to manually check each branch could be a nuisance. Some pathway for at least superlibrarians to "view all" would be preferable.