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.
Contoso Schools DW: Click Here
SQL Server Data Tools SSRS Project: Click Here
st.First_Name +' ' +st.Last_Name Student,
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
I have added a little formatting to my School textbox, but your design surface should resemble the above image.
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,
Check out my new book Microsoft SQL Server 2012 Step by Step