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 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply