Avatar of rschmehl
rschmehl
Flag for United States of America asked on

SSRS IN statement size trouble passing parm to query

I have a report which uses an IN statement to generate the groups(Parms)in  a SSRS module,  Problem is that in VS i can run the report on a population size i know to be around 2500.  But when i deploy to a 2008 R2 report server it will not run. stops dead. but if i adjust the parm to bring in a smaller population or select one or two members to report on it will run.
I think this an issue with the size of the IN statement passed to the query and I am trying to find a way around or a different way of using  parm as I have had a lot of issues with the "read ahead" and setting the groups in this report as it does call 2 subreports.
Microsoft SQL ServerSSRSMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
rschmehl

8/22/2022 - Mon
Ganapathi

Do you have index on the column that you are searching with the IN values?
ASKER CERTIFIED SOLUTION
ste5an

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
rschmehl

ASKER
The values for the IN are coming from a seperate dataset query,  and there is no index on that field.  this field is being used to filter the table.  And yes there can be  3000 values , mostly 10 character .   The weird thing is that it works in VS but when i deploy to 2008 r2 it won't.  
i think know what you are driving at with the temp table, but my date parms keep getting in the way.  right now i am athering the data based on the date range and then filtering the table group on the memberid and passing the memberid to the subreports. bbecause i want the member id in a drop down i can't limit it by the date ranges parms
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck