Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is it possible to filter Main report based on the values in subreport?


Is it possible to filter Main report based on the values in subreport?

Author
Message
hl6a
hl6a
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 144
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!
Ray M
Ray M
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 1076
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.
hl6a
hl6a
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 144
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!
Ray M
Ray M
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 1076
Yeah,
Your subreport would just return all courses Taught by TeacherID.

And your main report would return all teachers based off your search.
hl6a
hl6a
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 144
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....
Ray M
Ray M
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 1076
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.
Attachments
ReportSubReport.zip (3 views, 3.00 KB)
Mark Fitzgerald-331224
Mark Fitzgerald-331224
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3692 Visits: 1513
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
hl6a
hl6a
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 144
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!!
Mark Fitzgerald-331224
Mark Fitzgerald-331224
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3692 Visits: 1513
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.
hl6a
hl6a
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 144
That is right! The filtering is done on the stored proc side rather than on the report/tablix side. Thanks again!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search