April 22, 2013 at 8:52 am
I have a main report listing teacher's information and use a subreport to show the courses a teacher taught. How can I hide a teacher if she/he doesn't teach the course I search?
I thought about using filter feature, but how to refer to a field in subreport from main report? Or are there any other ways to achieve this?
Thanks for any help!
April 22, 2013 at 10:21 am
You can't change an outer dataset from a subreport.
You can change your main reports dataset to return teachers that teach the Subject your looking for.
Then the subreport that lists courses would run for each teacher returned in the outer dataset.
Or our main report would return courses you searched for.
Then your subreport would return teachers that teach that subject.
April 22, 2013 at 10:29 am
Do you mean add the filter parameter to the outer dataset instead of do filtering on reports?
Teacher's Name Courses (using sub-report)
John Smith Course A - description
Course B - description
Joe Anderson Course A - description
I would like to see John Smith with all the courses he taught (Courses A and B) only if search Course B.
Thanks!
April 22, 2013 at 11:58 am
Yeah,
Your subreport would just return all courses Taught by TeacherID.
And your main report would return all teachers based off your search.
April 22, 2013 at 12:25 pm
That would mean have duplicate course list in the main report and subreport.
I was hoping there is a way to hide the main report record if the subrepot query didn't anything....
April 22, 2013 at 3:57 pm
Sorry I'm confused.
My thought would be your main report would not list Courses, It would just list Teachers that teach the subject you searched for.
And the Subreport should simply return courses for the TeacherID passed in.
I've attached rdls demoing something very simple. check them if you want.
April 23, 2013 at 7:29 am
As Ray M stated the report you need to run first (main report) is to select all teachers who perform a selected course:
Select T.* from
Teachers T
inner join PerformsCourse P on T.TeacherID = P.TeacherID
inner join Courses C on P.CourseID = C.CourseID
where C.CourseTitle = @CourseParameter
This would return the list of teachers who perform a course.
To see what else these teachers teach then use a subreport to list courses they perform.
Select C.* from
PerformsCourse P
inner join Courses C on P.CourseID = C.CourseID
where T.TeacherID = @TeacherParameter
...where the @TeacherParameter is the parameter passed to the subreport.
Fitz
April 23, 2013 at 7:40 am
I am digesting the approach. I was hoping the users see all the teachers with the courses they taught. And if the users wanted to find teachers who teach particular subject, put down the subject value into a parameter, then run the report again to find out...Maybe I can manipulate my DataSet's stored proc to do so... Thanks very much!!
April 23, 2013 at 7:49 am
If you want to see all teachers with the courses they teach by default, then your report parameter should all for a ALL COURSES or ANY option. The teachers would then show and be filtered if a specific course is chosen.
April 23, 2013 at 7:53 am
That is right! The filtering is done on the stored proc side rather than on the report/tablix side. Thanks again!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply