SSRS parameter points to dataset field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

SSRS parameter points to dataset field

Author

Topic

pavlos
Yak Posting Veteran

59 Posts

Posted - 2012-04-16 : 03:40:25

Hey guys

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?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-16 : 07:18:17

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).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-16 : 12:21:30

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.

see

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pavlos
Yak Posting Veteran

59 Posts

Posted - 2012-04-16 : 22:04:34

I have added the value in a dataset
but it will only allow me to use the first value or that dataset in an available value.

pavlos
Yak Posting Veteran

59 Posts

Posted - 2012-04-16 : 22:23:33

ok let me restructre my question.

I want to specify 3 vales.

Label 1:
Region A
Value:
SELECT regionid FROM xxx

Label 2:
Region B
Value:
SELECT regionid FROM xxx

Label 3:
Region C
Value:
'1231','2131','1231'

I have saved the 'select regionid' sql statements in a dataset but dont know how to enter them in to the values.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-17 : 13:37:06

you specify values by use of parameters.
see how you need to setup parameters for it to accept comma seperated list of values in last link

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources