Using a row wise initialised session variable in a dashboard prompt

I came across a case today where I wanted to use the contents of a row wise intialised variable in a dashboard prompt.  You can't just use the variable directly as the possible list of values for the prompt as you will get an error message like this:

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)

SQL Issued: VALUEOF(NQ_SESSION.) 

 

So instead you can use the SQL Results option for the "Show" section of the dashboard prompt to bring in the column you are prompting and then add a where clauses referencing the variable:

 

SELECT Organisation.Organisation FROM Masterplan where Organisation.Organisation=VALUEOF(NQ_SESSION.) 

This will work with no errors and the list of possible results in the dashboard prompt will the same as those held in the session variable.

 

If you then want one of these to be selected by default (if you use report defaults then a blank entry will be added to the dropdown and this will be selected by default) then you can use some SQL again:

SELECT MIN(Organisation.Organisation) FROM Masterplan where Organisation.Organisation=VALUEOF(NQ_SESSION.ORGANIZATION_NAME)

 

This will select the first one in the list of results when ordered alphabetically.