Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is it possible to filter Main report based on the values in subreport? Expand / Collapse
Author
Message
Posted Monday, April 22, 2013 8:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 2:14 PM
Points: 38, Visits: 140
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!
Post #1445023
Posted Monday, April 22, 2013 10:21 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 12:03 PM
Points: 1,480, Visits: 1,028
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.
Post #1445058
Posted Monday, April 22, 2013 10:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 2:14 PM
Points: 38, Visits: 140
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!
Post #1445062
Posted Monday, April 22, 2013 11:58 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 12:03 PM
Points: 1,480, Visits: 1,028
Yeah,
Your subreport would just return all courses Taught by TeacherID.

And your main report would return all teachers based off your search.


Post #1445097
Posted Monday, April 22, 2013 12:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 2:14 PM
Points: 38, Visits: 140
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....
Post #1445108
Posted Monday, April 22, 2013 3:57 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 12:03 PM
Points: 1,480, Visits: 1,028
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.


  Post Attachments 
ReportSubReport.zip (3 views, 3.18 KB)
Post #1445221
Posted Tuesday, April 23, 2013 7:29 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 11:56 PM
Points: 3,128, Visits: 1,356
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
Post #1445415
Posted Tuesday, April 23, 2013 7:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 2:14 PM
Points: 38, Visits: 140
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!!
Post #1445426
Posted Tuesday, April 23, 2013 7:49 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 11:56 PM
Points: 3,128, Visits: 1,356
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.
Post #1445431
Posted Tuesday, April 23, 2013 7:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 2:14 PM
Points: 38, Visits: 140
That is right! The filtering is done on the stored proc side rather than on the report/tablix side. Thanks again!
Post #1445434
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse