Large selection in multiple parameters

I have a problem and was wondering if someone might have some good
suggestions. I've created a report that shows the number of loans that
have been given to students at various colleges by various vendors.
The report has parameters for vendor and for college, and I created the
parameters as multi-value drop down boxes. The source for the schools
box is a query against my data table for the distinct Schools that
appear.
The problem I'm having is this: There are approximately 4000 schools
in the table, and when the user does a Select All on the web site, the
report chugs away for a while and then returns nothing. It works fine
in Visual Studio (slowly, but it returns everything) but not on the
website. I don't get any error messages.
The WHERE clause in my main script is simply:
Where SchoolName in (@.schoolName)
My guess is that there are simply too much being shoved into the
parameter when I try to select all. Just wondering if anyone has a
reasonable way to get around this. I've managed a rather kludgy method
where I have an additional boolean parameter and then logic to bypass
the WHERE if it's true, but I'd rather just have the thing work
normally (via the drop-down) if that's possible.
The drop down for the Vendors works just fine, whether I select one,
many, or all of the vendors. But there are only 30 of those, so the
cause seems to be the number of choices. Is there a set limit to the
number of choices in a drop down, or a set size that can be passed
perhaps?
Using Reporting Services 2005.How about doing a bit of processing before the query gets run. Something
along the lines of
IIF parameter!School.Item(0) = true, select data regardless of school (no
whereclause on Schoolname) else select data with whereclause.
I'm not quite sure how to check if the Select All parameter was selected or
not, but it might be food for thought anyway.
Kaisa M. Lindahl Lervik
<cphite@.gmail.com> wrote in message
news:1162419349.183710.148290@.h48g2000cwc.googlegroups.com...
>I have a problem and was wondering if someone might have some good
> suggestions. I've created a report that shows the number of loans that
> have been given to students at various colleges by various vendors.
> The report has parameters for vendor and for college, and I created the
> parameters as multi-value drop down boxes. The source for the schools
> box is a query against my data table for the distinct Schools that
> appear.
> The problem I'm having is this: There are approximately 4000 schools
> in the table, and when the user does a Select All on the web site, the
> report chugs away for a while and then returns nothing. It works fine
> in Visual Studio (slowly, but it returns everything) but not on the
> website. I don't get any error messages.
> The WHERE clause in my main script is simply:
> Where SchoolName in (@.schoolName)
> My guess is that there are simply too much being shoved into the
> parameter when I try to select all. Just wondering if anyone has a
> reasonable way to get around this. I've managed a rather kludgy method
> where I have an additional boolean parameter and then logic to bypass
> the WHERE if it's true, but I'd rather just have the thing work
> normally (via the drop-down) if that's possible.
> The drop down for the Vendors works just fine, whether I select one,
> many, or all of the vendors. But there are only 30 of those, so the
> cause seems to be the number of choices. Is there a set limit to the
> number of choices in a drop down, or a set size that can be passed
> perhaps?
> Using Reporting Services 2005.
>|||What I have done in the case is as follows. I found this function
CREATE FUNCTION [dbo].[fn_MVParam](@.RepParam nvarchar(max), @.Delim char(1)=',')
RETURNS @.VALUES TABLE (Param nvarchar(max))AS
BEGIN
DECLARE @.chrind INT
DECLARE @.Piece nvarchar(max)
SELECT @.chrind = 1
WHILE @.chrind > 0
BEGIN
SELECT @.chrind = CHARINDEX(@.Delim,@.RepParam)
IF @.chrind > 0
SELECT @.Piece = LEFT(@.RepParam,@.chrind - 1)
ELSE
SELECT @.Piece = @.RepParam
INSERT @.VALUES(Param) VALUES(@.Piece)
SELECT @.RepParam = RIGHT(@.RepParam,LEN(@.RepParam) - @.chrind)
IF LEN(@.RepParam) = 0 BREAK
END
RETURN
END
This will break the comma separated string passed into your stored proc into
a table. Thow the results of this function into a temp table, and filter you
results with a join, instead of an in.
Ken
"Kaisa M. Lindahl Lervik" wrote:
> How about doing a bit of processing before the query gets run. Something
> along the lines of
> IIF parameter!School.Item(0) = true, select data regardless of school (no
> whereclause on Schoolname) else select data with whereclause.
> I'm not quite sure how to check if the Select All parameter was selected or
> not, but it might be food for thought anyway.
> Kaisa M. Lindahl Lervik
> <cphite@.gmail.com> wrote in message
> news:1162419349.183710.148290@.h48g2000cwc.googlegroups.com...
> >I have a problem and was wondering if someone might have some good
> > suggestions. I've created a report that shows the number of loans that
> > have been given to students at various colleges by various vendors.
> > The report has parameters for vendor and for college, and I created the
> > parameters as multi-value drop down boxes. The source for the schools
> > box is a query against my data table for the distinct Schools that
> > appear.
> >
> > The problem I'm having is this: There are approximately 4000 schools
> > in the table, and when the user does a Select All on the web site, the
> > report chugs away for a while and then returns nothing. It works fine
> > in Visual Studio (slowly, but it returns everything) but not on the
> > website. I don't get any error messages.
> >
> > The WHERE clause in my main script is simply:
> > Where SchoolName in (@.schoolName)
> >
> > My guess is that there are simply too much being shoved into the
> > parameter when I try to select all. Just wondering if anyone has a
> > reasonable way to get around this. I've managed a rather kludgy method
> > where I have an additional boolean parameter and then logic to bypass
> > the WHERE if it's true, but I'd rather just have the thing work
> > normally (via the drop-down) if that's possible.
> >
> > The drop down for the Vendors works just fine, whether I select one,
> > many, or all of the vendors. But there are only 30 of those, so the
> > cause seems to be the number of choices. Is there a set limit to the
> > number of choices in a drop down, or a set size that can be passed
> > perhaps?
> >
> > Using Reporting Services 2005.
> >
>
>|||Ken Reitmeyer wrote:
> What I have done in the case is as follows. I found this function
> CREATE FUNCTION [dbo].[fn_MVParam](@.RepParam nvarchar(max), @.Delim char(1)=> ',')
> RETURNS @.VALUES TABLE (Param nvarchar(max))AS
> BEGIN
> DECLARE @.chrind INT
> DECLARE @.Piece nvarchar(max)
> SELECT @.chrind = 1
> WHILE @.chrind > 0
> BEGIN
> SELECT @.chrind = CHARINDEX(@.Delim,@.RepParam)
> IF @.chrind > 0
> SELECT @.Piece = LEFT(@.RepParam,@.chrind - 1)
> ELSE
> SELECT @.Piece = @.RepParam
> INSERT @.VALUES(Param) VALUES(@.Piece)
> SELECT @.RepParam = RIGHT(@.RepParam,LEN(@.RepParam) - @.chrind)
> IF LEN(@.RepParam) = 0 BREAK
> END
> RETURN
> END
> This will break the comma separated string passed into your stored proc into
> a table. Thow the results of this function into a temp table, and filter you
> results with a join, instead of an in.
Ken,
When I use this function it works for one selection, but when I select
more than one school it tells me I have too many parameters.|||This is how I am using the function to pass a long parameter list
--temp table to hold parameters
declare @.tbl_WorkCenters table
(
work_center int
)
declare @.sql varchar(max)
--@.work_centers is the comma separated parameter list passed in from the
reprot
set @.sql = 'select ltrim(param) from fn_MVParam(''' + @.work_centers + ''',
'','')'
insert into @.tbl_WorkCenters
exec (@.sql)
Not sure if that answers your question or not.
"cphite@.gmail.com" wrote:
> Ken Reitmeyer wrote:
> > What I have done in the case is as follows. I found this function
> >
> > CREATE FUNCTION [dbo].[fn_MVParam](@.RepParam nvarchar(max), @.Delim char(1)=> > ',')
> > RETURNS @.VALUES TABLE (Param nvarchar(max))AS
> > BEGIN
> > DECLARE @.chrind INT
> > DECLARE @.Piece nvarchar(max)
> > SELECT @.chrind = 1
> > WHILE @.chrind > 0
> > BEGIN
> > SELECT @.chrind = CHARINDEX(@.Delim,@.RepParam)
> > IF @.chrind > 0
> > SELECT @.Piece = LEFT(@.RepParam,@.chrind - 1)
> > ELSE
> > SELECT @.Piece = @.RepParam
> > INSERT @.VALUES(Param) VALUES(@.Piece)
> > SELECT @.RepParam = RIGHT(@.RepParam,LEN(@.RepParam) - @.chrind)
> > IF LEN(@.RepParam) = 0 BREAK
> > END
> > RETURN
> > END
> >
> > This will break the comma separated string passed into your stored proc into
> > a table. Thow the results of this function into a temp table, and filter you
> > results with a join, instead of an in.
> Ken,
> When I use this function it works for one selection, but when I select
> more than one school it tells me I have too many parameters.
>