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.
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.
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>>"
(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>>.
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.
I filed bug 25971, suggesting the ability to just name a database field, the other day.
I like this idea and would find it very useful.
I've had multiple reports recently where a feature like this would be helpful.