Bug 37886

Summary: SQL reports - Multiselect parameters should be optional
Product: Koha Reporter: Brendan Lawlor <blawlor>
Component: ReportsAssignee: Brendan Lawlor <blawlor>
Status: RESOLVED DUPLICATE QA Contact: Testopia <testopia>
Severity: enhancement    
Priority: P5 - low    
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35746
Change sponsored?: --- Patch complexity: Trivial patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:
Bug Depends on: 37077    
Bug Blocks:    
Attachments: Bug 37886: Make multiselect parameters optional in SQL reports

Description Brendan Lawlor 2024-09-10 19:14:37 UTC
When a report is written with multiselect parameters and the the report is run with nothing selected the report results should not be limited.

Multiselect reports use the IN() function, so the SQL should be generated with a comma separated list of all of the authorised values.
Comment 1 Brendan Lawlor 2024-09-10 19:45:53 UTC
Created attachment 171288 [details] [review]
Bug 37886: Make multiselect parameters optional in SQL reports

This bug changes the default starting value for multiselects to be a
newline separated list of all authorised values so the SQL in() function
does not limit the results when no selections are made.

To test:
1. Create an SQL report like
select *
   from items
   where
   homebranch in <<Libraries|branches:in>>
   and
   itype in <<Item type|itemtypes:in>>
2. Run the report without selecting any parameters
3. Notice no results are returned
4. Click 'Show SQL code'
5. Notice '%' is used for the parameter incorrectly
6. Apply patch and repeat step 2
7. Notice results are returned
8. Click 'Show SQL code'
9. Notice the in() function correctly includes
    a comma separated list of all authorised values
10. Run the report again with single and multiple selections
11. Confirm the SQL code is generated as expected
Comment 2 Brendan Lawlor 2024-09-10 20:38:34 UTC

*** This bug has been marked as a duplicate of bug 37050 ***