Bug 25173 - Create list of parameters to choose from via a query
Summary: Create list of parameters to choose from via a query
Status: NEW
Alias: None
Product: Koha
Classification: Unclassified
Component: Reports (show other bugs)
Version: unspecified
Hardware: All All
: P5 - low enhancement with 10 votes (vote)
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2020-04-16 15:22 UTC by Christopher Brannon
Modified: 2021-10-21 14:46 UTC (History)
4 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
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.