I have a report that runs a query such as this: SELECT name, state, region FROM details
where region in (@region)
Now this is fine..
But i want to create a query to get my region values: eg. SELECT distinct region FROM region
Where pasta in ('2','23') and part = 'west'
So my question is
I want to assign a whole lot of different region numbers in an 'available value' list in the parameter box. I want to call a quey that returns a field and not just the first result in a set and assign that to a name.
Is this possible?
FOR EXAMPLE in the parameter section available values: (specify)
label value region 1 sql statement region 2 sql statement
can this be done? where can I store these sql statement?
If I understood you correctly, in the "Available Values" tab of the parameter properties dialog, you can select the "Get values from a query" option to accomplish this. You will need to add a dataset that returns the regions available (using your second query).
you can use option from query option for getting the value. However, keep in mind that you can pass a comma separated list of values into query through a variable and use IN to filter on them. for that you need eithrr dynamic sql or use string parsing function to get into a table and filter using it.