How many times have you been asked to develop a report that displays the details for a specified group? For example, maybe you need to produce something like the following report:

The above image shows a report that contains three groupings: School, Location and Event Type. Two values, # of Behaviors and Last Behavior Date, is associated to the Event Type. Directly below each Event Type listed in the report the details can be seen by toggling the # of Behaviors. I have to admit my first try at this worked, but the user experience was absolutely terrible. As a result, a searched Books Online a little and I ran across Lists (Report Builder and SSRS), which provided me with all the information that I needed to effectively solve the problem.

The following short video demonstrates how I solved the problem. I will say one thing, the report is compose of embedded Lists and on table.

Watch the Video

Downloads

Contoso Schools DW: Click Here

SQL Server Data Tools SSRS Project: Click Here

Step-By-Step

1. Prior to following these steps, please restore the Contoso Schools DW database. See the above section labeled Downloads.
2. Once the database is restored you will create a new SQL Server Date Tools SSRS project.
3. Add a Shared Data Source that connects to the Contoso Schools DW.
4. Add a Shared Dataset named StudentDetails that uses the data source from step 4, and use the following query as the source:

   SELECT

       s.School_Id,

       dblt.Behavior_Location_Id,

       bet.Behavior_Event_Id,

       s.School_Name School,

       bet.Behavior_Event_Desc EventType,

       dblt.Behavior_Location_Desc Location,

       st.First_Name +’ ‘ +st.Last_Name Student,

       t.Date,

       1 Behavior

   FROM dbo.Fact_Student_Behavior sb

   INNER JOIN dbo.Dim_Behavior_Event_Type bet

       ON sb.Behavior_Event_Id = bet.Behavior_Event_Id

   INNER JOIN dbo.Dim_Behavior_Location_Type dblt

       ON sb.Behavior_Location_Id = dblt.Behavior_Location_Id

   INNER JOIN dbo.Dim_School s

       ON sb.School_Id = s.School_Id

   INNER JOIN dbo.Dim_Student st

       ON sb.Student_Id = st.StudentId

   INNER JOIN dbo.Dim_Time t

       ON sb.Time_ID = t.Time_ID

5. Right-click on the folder labeled Reports in the Solution Explorer. Select Add | New Item from the menu that appears.

    

   

    

6. Select Report from the list of available items on the Add New Item window.
7. Enter Student Behavior Details.rdl in the textbox labeled Name that is located at the bottom of the window.
8. Click the button labeled Add.
9. In the Report Data section, which should be located to the left of the report, right-click the folder labeled Datasets and select Add Dataset… from the menu that appears.
10. The Dataset Properties window will open.
11. Enter StudentBehaviorDetails in the Name textbox.
12. Select the Use a shared dataset radio button.
13. Select the StudentDetails shared dataset.

     

    

     

14. Click the button labeled OK.
15. Open the Report Toolbox if it is not open, but going to View | Toolbox.
16. Locate the List object and drag it onto the report design surface.

     

    

     

17. Press F4 and the Properties window will open.
18. In the drop down list located directly at the top of the Properties window, below the words property select Tablix1.
19. Scroll down the list of properties and locate DataSetName, click in the cell to the right, and select StudentBehaviorDetails from the drop down list.

     

    

     

20. Directly below the report design surface you will see to sections, Row Groups and Column Groups.
21. Right-click the item labeled Details and select Group Properties from the menu. The Group Properties window will open.
22. Enter Schools in the Name textbox.
23. Click the button labeled Add and a Group On drop down list will appear.
24. Select [School] from the drop down list.
25. Click the button labeled OK.

     

    

     

26. Drag a Textbox object from the Toolbox into the List object.
27. Click in the textbox and a small field list icon will appear.
28. Click the icon and select [School] from the list of available columns. Don’t worry about the inclusion of the First function.

     

    

     

29. Drag a List object from the Toolbox into the existing List object directly below the textbox that was added in Step 27.
30. With the newly added List object selected repeat steps 21 – 25, replacing the Schools with Locations in step 22 and [School] with [Location] in step 24.
31. Drag a Textbox object into the new List object.
32. Click in the textbox and a small field list icon will appear.
33. Click the icon and select [Location] from the list of available columns.

     

    

     

    I have added a little formatting to my School textbox, but your design surface should resemble the above image.

     

34. You may need to increase the length of the two existing List objects before proceeding.
35. Drag a List object into the List object that was created in step 29. It is the same List object that contains the textbox that contains [First(Location)].
36. With the newly added List object selected repeat steps 21 – 25, replacing the Schools with Event_Types in step 22 and [School] with [EventType] in step 24.
37. Drag a Textbox object into the new List object.
38. Click in the textbox and a small field list icon will appear.
39. Click the icon and select [EventType] from the list of available columns.
40. Drag another Textbox directly to the right of the textbox created in step 37.
41. Click in the textbox and a small field list icon will appear.
42. Click the icon and select [Behavior] from the list of available columns.

     

    

     

43. Drag a Matrix object directly below the two textboxes.
44. With the matrix selected Select View | Report Data from the menu. The Report Data option is the very last item in the list.
45. Drag Student from the dataset into the column in the matrix labeled Rows.
46. Drag Date from the dataset and drop it to the right of the word [Student] in the matrix.
47. Drag Behavior from the dataset and drop it into the column in the matrix labeled Data.

     

    

     

48. With the matrix selected press F4.
49. In the properties window locate the Visibility section.
50. Select True for the Hidden property.
51. Click in the cell to the left of the ToggleItem property.
52. Select Behavior from the drop down list.

     

    

     

53. Preview the Report by selecting the tab at the top of the design surface labeled Preview. This report may take 20-30 seconds to run.
54. If you click the (+) plus symbol located to the left of each number you will display the details for a giving Event Type.

     

    

That was a lot of steps, but now you have a report that can show aggregated data a different levels and show details on a single report. You may want to format the report a bit to make a little more visually appealing to your end-users.

Talk to you soon,

Patrick LeBlanc