Link to home
Start Free TrialLog in
Avatar of Emi975
Emi975

asked on

Multi-Value Parameter in SSRS report 2005

I would like to show a multi-value option in SSRS 2005 Reporting Services that gives me the option to choose which question number from a survey  (There are 4 questions on the survey). The problem that I am running into is when I go into preview mode, and choose the drop down list, it shows me every answer for every question, meaning  duplicate results for question 1, duplicate results for question 2 and so on. I want to just choose question 1 from the drop down list, and it will filter only question 1.
When I do this in the dataset -------and sr.questionID in (@questionid) it works perfectly.
When I try to do this from the report preview after – the query and using the report parameters functions, it shows me every possible result.
How do I collapse all (questions 1-4) from the preview like it does from the query in the dataset?
 I am  -and sr.questionID in (@questionid) when using the SSRS reporting parameters.
 User generated image User generated image User generated image
select s.personid,hh.name as HouseholdName,i.lastname +', '+ i.firstname as Guardian,r.name as relationship,s.surveyid,sr.questionid as Question,
case sq.description
when 'PTO: Please choose below' then 'PTO permission'
end Description,
case sr.response
when 'No, Do not allow my information to be shared' then 'N'
when 'Yes, I allow my Information to be shared' then 'Y'
end Response,
r.personid2 as StudentPID,p.studentnumber as studentid,
i2.firstname + ', '+
i2.lastname as Student,
h.householdid,
hh.phone
from dbo.SurveyResponse s
inner join [identity] as I on i.personid = s.personid
inner join surveyquestionresponse as sr on sr.responseid =s.responseid
inner join surveyquestion as sq on sq.questionid = sr.questionid
inner join relatedpair as R on r.personid1 = s.personid
left outer join [identity] as I2 on I2.personid = R.personid2
left outer join person as p on p.personid = I2.personid
left outer join householdmember as h on h.personid = p.personid
left outer join household as hh on hh.householdid =h.householdid
where  r.guardian = '1' and h.enddate is null --and sr.questionID in (@questionid)

Open in new window

QuestionID.JPG
Report-Parameters.JPG
ASKER CERTIFIED SOLUTION
Avatar of Emi975
Emi975

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial