Bug 25173

Summary: Create list of parameters to choose from via a query
Product: Koha Reporter: Christopher Brannon <cbrannon>
Component: ReportsAssignee: Bugs List <koha-bugs>
Status: NEW --- QA Contact: Testopia <testopia>
Severity: enhancement    
Priority: P5 - low CC: andrewfh, hebah, margaret, rhouk
Version: unspecified   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:

Description Christopher Brannon 2020-04-16 15:22:36 UTC
Currently, you can use something like <<Library|branches>> to have a parameter prepopulate with choices from predetermined tables (in this case, from branches), or you can point to a authorised list.  But, if you want to prepopulate from other tables, you are out of luck.

I would love to be able to do something like <<List|(SELECT shelfname FROM virtualshelves)>> and have it prepopulate with the results.
Comment 1 Andrew Fuerste-Henry 2020-04-17 11:54:12 UTC
I like this idea. There are lots of things I'd like to be able to pull in as a runtime parameter on a report. An approach like this would spare me having to set those up as authorized values and then maintain those auth values when/if the options change.
Comment 2 Andrew Fuerste-Henry 2020-07-09 19:22:45 UTC
Kicking this around a little more, would it be easier or cleaner to make the parameter point at a saved report? So, for your example, you'd make one report that's "SELECT shelfname FROM virtualshelves" and then in your second report say "WHERE shelfname LIKE <<Pick a list|report_XXX>>"
Comment 3 Christopher Brannon 2020-07-10 19:25:56 UTC
(In reply to Andrew Fuerste-Henry from comment #2)
> Kicking this around a little more, would it be easier or cleaner to make the
> parameter point at a saved report? So, for your example, you'd make one
> report that's "SELECT shelfname FROM virtualshelves" and then in your second
> report say "WHERE shelfname LIKE <<Pick a list|report_XXX>>"

For me, that would drive me nuts.  If I had to maintain an external report whose sole purpose was to be a list of choices for another report, I would go bonkers trying to manage those.

Another way to approach it might be, instead of doing something like <<List|(SELECT shelfname FROM virtualshelves)>>, we could just reference the table and column, like <<List|virtualshelves.shelfname>>.
Comment 4 Katrin Fischer 2020-07-12 11:24:55 UTC
I like the idea, independent of syntax, it might be nice to give an option for a description showing different to the values it adds to the query.

Something like:
<<List|(SELECT shelfnumber, shelfname FROM virtualshelves)>>

First column will be used for the query and as description, if no second column exists. So the second parameter could be made optional, if that makes sense?

<<List|virtualshelves.shelfnumber, virtualshelves.shelfname>>.

I can see that the first syntax would give us a bit more flexibility. Like one could create a list of all shelving locations actually in use:

<<List|SELECT distinct location from items ...>>

And also allowing to pull in a second table for getting descriptions.
Comment 5 Andrew Fuerste-Henry 2020-07-13 11:38:52 UTC
I filed bug 25971, suggesting the ability to just name a database field, the other day.
Comment 6 Ron Houk 2020-08-11 21:14:43 UTC
I like this idea and would find it very useful.
Comment 7 hebah 2021-10-21 14:46:38 UTC
I've had multiple reports recently where a feature like this would be helpful.