Use Select List, Allow <ALL>

[Alexa_Muhs]

I am trying to create a dashboard that allows me to enter a parameter for either one user account or all user accounts. I am having trouble with the <ALL> part. Is there an example of the SQL used in the "User" value select list in the labs from tech quest?

Thanks!

[Alexa_Muhs · Accepted solution]

I stumbled across an example in one of the canned dashboards that I could use:

SQL Query contains:

WHERE s.usernum = ##OB_OPTIONSELECT_ALL{(@{UserAcct}, -1)}

Select List code:

SELECT ' <<ALL>>', -1UNION ALLSELECT username, usernumFROM hsi.useraccount WHERE username NOT LIKE ('%(deactivated)%')ORDER BY 1

The important piece for me was the -1 instead of {ALL}.

[Grant_Fitzgeral]

Hi Alexa,

I wrote something in Report Services from a couple years ago that should work in Dashboards. In your WHERE statement, set your variable with something like

WHERE hsi.keyitem81 = @{Region}

When you define that region variable, do something like the following for the select list:

SELECT '<<ALL>>', '{ALL}'

UNION ALL

SELECT RTRIM(reg.keyvaluechar), reg.keyvaluechar

FROM hsi.keyitem81 reg

Hopefully this works/helps.

-Grant

[Alexa_Muhs]

Hi Grant,

Thanks for the assistance! I was getting stuck using similar code from a report services report myself. The correction I needed to make to work with how I had my code set up was changing {ALL} to -1 (See my posted answer).

Best,

Alexa

[Alexa_Muhs · Accepted solution]

I stumbled across an example in one of the canned dashboards that I could use:

SQL Query contains:

WHERE s.usernum = ##OB_OPTIONSELECT_ALL{(@{UserAcct}, -1)}

Select List code:

SELECT ' <<ALL>>', -1UNION ALLSELECT username, usernumFROM hsi.useraccount WHERE username NOT LIKE ('%(deactivated)%')ORDER BY 1

The important piece for me was the -1 instead of {ALL}.

[William_Pawul1 · Accepted solution]

Hi Alexa,

Here is a link that contains example data providers and dashboards.

https://community.hyland.com/search/-/media/7b68f1ecd3be4d3598a449ffe8903f5e.zip

Some of these will contain the example SQL that you are looking for.

William