Microsoft in Education Blog
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
Hi Patrick, your example was really helpful. Do you perhaps have one where you are using graphs/charts?
Man you are a live savior, really great article
This is just what I needed. THANK YOU!
great example - thank u