Pre-defined lists of values as report parameter input

Has anyone come up with a solution for the following scenario yet?
We have many reports which have input parameters that are lists of values.
As an example the user might want to report on 30 of 100 available product
codes, or 12 of 50,000 account numbers.
What might be an idea would be to store the lists of values that userâ's want
to report on, in a general purpose Lists table. One column would be a List
Name and the other a delimited list of values. This List table could then be
used as the input to a Drop Down box for the relevant report parameter.
Has anybody tried something similar, or does anyone have any other
suggestions?
(Weâ're aware that multi-select list boxes will be available in SSRS 2005,
but that would still be a bit tiresome for a user that needs to select a few
values from a long list).
Regards,
John MarshHi John,
I have done similar thing like that.
i have a lookup parameter report which is a webpage with listbox and the
value is populated by calling a param report. The param report contain 2
fileds and it like name- value pair. The report is called programttically and
the result is returned in xml format so that it can be bind to a dataset and
to tht listbox or dropdown box.
So this act as a lists of values as report parameter input..
Regards
Bava
"John Marsh" wrote:
> Has anyone come up with a solution for the following scenario yet?
> We have many reports which have input parameters that are lists of values.
> As an example the user might want to report on 30 of 100 available product
> codes, or 12 of 50,000 account numbers.
> What might be an idea would be to store the lists of values that userâ's want
> to report on, in a general purpose Lists table. One column would be a List
> Name and the other a delimited list of values. This List table could then be
> used as the input to a Drop Down box for the relevant report parameter.
> Has anybody tried something similar, or does anyone have any other
> suggestions?
> (Weâ're aware that multi-select list boxes will be available in SSRS 2005,
> but that would still be a bit tiresome for a user that needs to select a few
> values from a long list).
> Regards,
> John Marsh
>

Possible to specifying a list as a parameter to a stored procedure ?

I have been converting a VB 6 applications database queries into SQL Server 2000 stored procedures and have come up against a problem where lists are used in search conditions...For example a list of accounts are selected based on their account currency ID being equal to 1, 5, or 7. In the VB 6 query the string looks like...

SELECT tblAccount.txtName FROM tblAccount WHERE (tblAccount.intCurrencyId IN(1, 5, 7))

The list could contain a single value or upto 20 values. Is it possible to pass the currency list (i.e "1, 5, 7, ...") as a parameter to the stored procedure?

Any help much appreciated!The answer is, "maybe".

It depends on your needs for performance. Please take a look at this discussion for more details.

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=15403

Essentially, if you pass the list as a comma delimited string, then you will either need to parse it inside the query or use it in a dynamic SQL query within the SP. Your other choice is to take all 20 objects as single parameters to your SP. Your IN statement would then be a large set of OR statements for each of the 20 items.

I hope this helps,

CC|||I have to write a lot of stored procedures for reports. I always declare my parameters like

level1 varchar(255)

I then look at the incoming value. I use charindex to find ';' or ',' If I find either I know I have to use "in" in the where clause and format the values correctly.

IF CHARINDEX(';',@.LEVEL1)>0
BEGIN
SET @.LEVEL1=REPLACE('('+''''+REPLACE(@.LEVEL1,';',''''+ ','+'''')+''''+')',' ','')
END

If it is prompt is equal to '%' for all I make my where clause a like, if it is a single value I use equal. The trick to making this so flexible is to use dynamic sql. If you don't know what the parameter will be before hand it seems to be the best way.

' AND ISNULL(T1.DIVISION,'+''''+'NONE'+''''+')' +
case when CHARINDEX(',',@.LEVEL1)>0 then 'in '+@.LEVEL1
else
CASE @.LEVEL1 when '%' THEN ' LIKE '+''''+@.LEVEL1+''''+'+'+''''+'%'+''''
ELSE '='+''''+@.LEVEL1+'''' END
END

Sorry for the formating. It looks better in the actual file|||Oh and I just realized something. If the incoming value is '%' then do not add a condition for it in the dynamic where clause. It makes zero sense to add anything to a where clause if you don't need to.