sql server reporting services: Multi-Value Parameters: Need 25 Distinct Product Codes -- sql server reporting services

You're in the

sql server reporting services

group:

Multi-Value Parameters: Need 25 Distinct Product Codes

BillB
6/15/2006 11:36:02 AM

sql server reporting services:

Hi
Looking to set up a parameter for 'Product Groups'
Code says:
WHERE
D.FISCAL_YEAR = '2006' AND
D.FISCAL_PERIOD = '12' AND
D.PRODUCT_GROUP IN (@ProdGroup ) AND etc etc

@ProdGroup set up using Layout/Report/Report Parameters. As a string

when I try to run it (as in other threads listed here) one value of
for example 170 it works ok.
If I enter into the parameter field: 110,130 it produces nothing.
('110','130') also fails.
I need the ability to enter 25 different product groups
per run of the program.
Is my SQL statement correct or not?
Am I entering the wrong syntax into the parameter field? If not what should
the syntax look like?
I'm new to Reporting Services & getting nowhere, even though I read all the
other threads appearing here.

Re: Multi-Value Parameters: Need 25 Distinct Product Codes

Bruce L-C [MVP]
6/15/2006 2:01:45 PM

It sounds like you are running RS 2000. You cannot do this in RS 2000
without resorting to dynamic SQL. You can make your query the result of an
expression.

In RS 2005 there is support for multi-select parameters and what you want to
do will work.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Re: Multi-Value Parameters: Need 25 Distinct Product Codes

BillB
6/16/2006 6:24:01 AM

Thanks for responding Bruce. I am using RS 2005 Visual studio 2005
Version 8.0.50727.42. I'm still stuck. Could it be the way I've described
the parameter? I have nothing in the "available values" section. Do I have to
list
here all the possibilities (25 Product Codes) for this to work?

Thanks Bill B

[quoted text, click to view]

Re: Multi-Value Parameters: Need 25 Distinct Product Codes

Bruce L-C [MVP]
6/17/2006 10:50:16 AM

If you set it as multi-valued parameters then the whole point of it is to
have a list the user picks from. You can hard code it in the Parameters page
or you can base the parameter on a query. But, it has to have a list. The
intent is not for you to free form enter a coma separated list. For one
thing, you open yourself up to an injection attack (i.e. it is a security
hole). And, as you have seen, it does not work.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.

home · blog · groups

Questions? Comments? Contact the dn