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.
select s.personid,hh.name as HouseholdName,i.lastname +', '+ i.firstname as Guardian,r.name as relationship,s.surveyid,sr.questionid as Question,
when 'PTO: Please choose below' then 'PTO permission'
when 'No, Do not allow my information to be shared' then 'N'
when 'Yes, I allow my Information to be shared' then 'Y'
r.personid2 as StudentPID,p.studentnumber as studentid,
i2.firstname + ', '+
i2.lastname as Student,
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)