May 21, 2012 at 2:46 pm
I am wondering if SSRS 2008 R2 has the ability to "focus" on groups when drilling, or to hide the groups that were not expanded. As an example, I will show enrollment in a list of courses at a university, which rollup to departments, which rollup to colleges.
+College of Arts 1234
+College of Sciences 2345
+College of Engineering 3456
Then I want to expand the College of Sciences and hide all the other colleges data.
+College of Arts
+College of Sciences 2345
+Biology 987
+Chemistry 234
+Physics 456
+College of Engineering
Then I want to expand the Chemistry department and hide all the other departments.
+College of Arts
+College of Sciences 2345
+Biology
+Chemistry 234
+CHEM100 99
+CHEM200 88
+CHEM300 47
+Physics
+College of Engineering
I know this may require complex logic in the hide/show expressions, so any help would be appreciated.
Mike
May 22, 2012 at 12:30 am
I fancied a challenge.
I managed to do this using recursive report actions (i.e. a report calls itself as a report action) passing across two parameters college and department.
1. Create two parameters (@CollegeToShowExpanded and @DepartmentToShowExpanded). Set both to allow blank as a valid parameter value. Add a default to both as "" (empty string).
2. In your tablix (in my case a classic table). Add groupings as before for department and college. The column for attendees should have sum(attendees) at the group levels.
3. In the Department row group properties change the Visibility page to have a formula for hide/show based on an expression as below. This will make visible if the college is correct:
=iif(Parameters!CollegeToShowExpanded.Value=Fields!College.Value,False,True)
4. In the Details row group properties change the Visibility page to have a formula for hide/show based on an expression as below. This will make visible if the department is correct, this will need to be expanded upon if two or more departments share the same name:
=iif(Parameters!DepartmentToShowExpanded.Value=Fields!Department.Value,False,True)
5. On the attendees column of the college row change the expression for the text color as shown below. The college will only show (hidden white on white) the attendees sum if the college is selected or no college is selected.
=iif(Fields!College.Value=Parameters!CollegeToShowExpanded.Value OR Parameters!CollegeToShowExpanded.Value="","Black","White")
6. On the attendees column of the department row change the expression for the text color as shown below. The department will only show (hidden white on white) the attendees sum if the correct college and department are selected or either the college or department are empty strings.
=iif((Fields!College.Value=Parameters!CollegeToShowExpanded.Value AND Fields!Department.Value=Parameters!DepartmentToShowExpanded.Value ) OR Parameters!CollegeToShowExpanded.Value="" OR Parameters!DepartmentToShowExpanded.Value="","Black","White")
7. Lastly create actions on each of the college, department and column heading to call an report action to call the same report passing accross the following:
Header (i.e. do not specify a college or department)
@CollegeToShowExpanded = ""
@DepartmentToShowExpanded = ""
College (i.e. specify college)
@CollegeToShowExpanded = fields!College.Value
@DepartmentToShowExpanded = ""
Header (i.e. do not specify a college or department)
@CollegeToShowExpanded = fields!College.Value
@DepartmentToShowExpanded = fields!Department.Value
8. Deploy the report and enjoy.
RDL sample attached (please rename from Recursive Actions RDL.txt to Recursive Actions.rdl). The example uses the local tempdb and a hardcoded demo select query (college,department,course,attendees).
Fitz
May 22, 2012 at 1:27 pm
The gauntlet was thrown and now you own it. 🙂 Very impressive answer. I had only heard about recursive calls to the same report, but this is actually pretty cool. I had considered the white on white hiding, but not sure how long it would take me to get to recursive calls. Thanks again.
Mike
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply