Creating a paramenter based on select statement? 2

[SomeoneTalked]

I'm running XI and trying to create a dynamic parameter based off of a certain type of database entry. The result set for a directory is several thousand entries and what I need is the parent names from the directory which is roughly 300+ entries. I have created a separate report that pulled the information which excludes a directory type of ON CALL and imported the 300+ entries as a static list. This is one way of doing this but it could become a problem should the directory parents be updated often.

My question would be this: Is there a way to create a parameter based off of a select statement to exclude the data I do not want to select from? I have attempted creating a formula and creating several different parameters but they do not seem to work.

Please let me know if more info is needed or if this is too vague.

Thanks for any info.

 

[Turkbear]

Hi,
One way might ( I have not tested this) be to create a Command Object based on the select statement you need to reduce the list of values and then base a Dynamic Parameter ( new in XI) on the relevant field from that object rather than the database table..
Join the Object and the Table by the appropriate field(s)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

You could create a command (database->database expert->add command) that was something like:

Select table.`name`, table.`type`
From `table` table
Where table.`type` <> 'ON CALL'

Then link the command to your table on {table.name}. In the parameter setup screen choose "dynamic" and then use the Insert dropdown to select {command.name} to populate your parameter list.

In the record selection formula set:

{table.name} = {?yourparm}
//don't use {command.name} as you will get record inflation, I think.

-LB

 

[SomeoneTalked]

Sweet. Looks like this will definitely work. Just have to tweak a few things to make the data come out properly.

Thank you for the posts!