Manage Select Lists - is there a way to group items via the Select List SQL query?

[Elizabeth_Bond]

Hello everyone,

I'm curious if anyone has experimented with the Manage Select Lists and successfully built a query in them that would allow you to group items then let a user select from that group.

Here is my scenario - I have a list of scan queues that I'd like to group together into smaller subgroups for those applicable areas. Currently since my select statement for my select list is:

select queuename,
queuenum
from hsi.scanqueue
ORDER BY 1

It only provides the user with a list of scan queues to choose from. However, I'd ideally like to only provide a handful of choices based on the grouping we've decided.

Has anyone done that/had success with that/or have ideas how I could accomplish what I'd like to do otherwise?

Thanks for your help!

[Larissa_Armand · Accepted solution]

Hi Beth,

Hopefully I am understanding what you're asking about - this is a list to select from for a parameter on a custom SQL data provider, right?

You can add to the "where" clause to limit the select list however you like. 

Are you looking to only allow users to select from a list of scan queues they have access to via user groups? I'm doing something similar using document types with this as the SQL for the select list, this would need to be adjusted for scan queues and is written for Oracle db so might need adjustment there as well. 

select trim(D.ITEMTYPENAME), D.ITEMTYPENUMfrom DOCTYPE Dwhere D.ITEMTYPENUM in 	(select ITEMTYPENUM 	from USERGROUPCONFIG 	where USERGROUPNUM in 		(select USERGROUPNUM 		from USERXUSERGROUP 		where USERNUM = ##OB_CURRENTUSER_ID))order by 1;

This only lists document types that the logged in user has access to. 

For scan queues, you'd need to use USERGSCANQUEUE instead of USERGROUPCONFIG. 

[Elizabeth_Bond]

Thanks Larissa, yes I'm talking about the manage select lists option when you are configuring the input parameters of a custom SQL query.

Maybe I can combine your info and Laura's and build in a case statement into the where clause of the select list? I won't have time today to give that a shot but hopefully we can try it next week - I'll report back on that.

Thanks for including the info about the doc types too I can definitely see that being useful for future reports!

[Larissa_Armand]

If you let us know what defines the "groups" (is it based on a certain naming convention, doc type groups for the documents assigned to the scan queues, etc.) and whether you want the select list to be ordered by the "group" or only visible/available based on certain criteria we might be able to give you a better suggestion. 

Since the SQL select list can only have two values in the "select" clause (the value that will be displayed to users and the value that is actually passed into your data provider), you could do either: 

1. Concatenate the "group" name into that first display value: 

select 'group name ' || trim(QUEUENAME), QUEUENUM
from SCANQUEUE
order by 1
;

From there you can replace 'group name ' with a substring kind of expression if the scan queues are grouped by a part of their name. Or you could use a case statement and then append the scan queue name: 

select case when <whatever criteria> then <whatever group name> || trim(QUEUENAME) end as ScanQueueName, QUEUENUM
from SCANQUEUE
order by 1
;

2. Use a "cascading" select list using two parameters, where users first select the "group" and then the second parameter list is limited based on that selection. I am looking for an example of that and will update if I find one. 

Edited to add - I couldn't find an existing example but set one up. 

On your main data provider SQL, you will need two parameters, for example: 

select trim(QUEUENAME) as ScanQueueName, QUEUENUM, trim(AUTONAMESTRING),
case when trim(AUTONAMESTRING) = '%D - %U' then 'Default'
else 'Custom' end as GroupName
from SCANQUEUE
where case when trim(AUTONAMESTRING) = '%D - %U' then 'Default'
else 'Custom' end = @{pGroupName} --this uses the value from the other prompt
and QUEUENUM = @{pQueueNum} --this is the user selection prompt
order by 1
;

Then configure a select list for each parameter: 

here's the parameter for the group value: 

select distinct
case when trim(AUTONAMESTRING) = '%D - %U' then 'Default'
else 'Custom' end as GroupNameDisplay,
case when trim(AUTONAMESTRING) = '%D - %U' then 'Default'
else 'Custom' end as GroupNameValue
from SCANQUEUE
order by 1
;

And then for the scan queue number you're already using: 

select trim(QUEUENAME) as ScanQueueName, QUEUENUM
from SCANQUEUE
where case when trim(AUTONAMESTRING) = '%D - %U' then 'Default'
else 'Custom' end = @{pGroupName} --this uses the value from the other prompt
order by 1
;

Then when you view the dashboard, there are two prompts: 

The value for the second one cannot be selected until you choose a value for the first parameter. Once you choose the "group" value, the second parameter list is available and only shows items with the value for the first. 

[Elizabeth_Bond]

Thanks Larissa for offering that. Here is the other information if you are able to come up with something for it.

We would like it to be ordered by the groups/only have the group selector be what the user sees to choose to run the report. It doesn't matter if they are visible/available on the certain criteria just would like to ideally truncate the 100+ scanqueues into a list of 5-10.  So something like this?

Scanqueues 1, 5, 7 is group "Scheduling", Scanqueues 14,15,26 is group "HIM", Scanqueues 4, 18, and 20 is group "Ambulatory" and so on.

If it's better to approach from a naming convention side all of the scan queues do start with the same heading for the group they are in such as HIM all starts with HIM all Scheduling starts with SCH.