In Part 9 of Building a Report from the Ground Up, we’re going to cover how to add groups to your report. Groups can be used to increase the readability of the report as well as increase your end user’s understand of the data. In this example, we’ll take a look at both using row groups and column groups.
For this example on creating groupings, I’ll be using a report that displays some data about Adventure Work employees in the AdventureWorksDW database, their gender, their income, and the number of children at home. The stored procedure that returns the data is seen here:
CREATE PROCEDURE [dbo].[RP_GenderIncomeKids]
-- Add the parameters for the stored procedure here
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT DimCustomer.Gender, DimCustomer.YearlyIncome, DimCustomer.NumberChildrenAtHome, DimGeography.CountryRegionCode
FROM DimCustomer INNER JOIN
DimGeography ON DimCustomer.GeographyKey = DimGeography.GeographyKey
Here’s what the report looks like before we get started. You can see it displays a column for the number of children at home and two columns displaying the average income for each gender:
The first group I’ll add to the report is a row group at the bottom of the matrix to display the average incomes of both females and males regardless of the number of children they have at home. To do this, click the arrow next to the NumberChildrenAtHome row group in the Row Groups pane.
Select Add Total, and click After. A group at the bottom of the matrix will be added that automatically creates an average for each gender (To cause the group to total the values, simply change the text box's expresson to read =sum(someexpression).
From this new grouping we can see the average income of all Females and all Males.
Now let’s add a column grouping that groups together employees based on the number of children they have. Click the arrow next to the Gender group under Column Groups, click Add total, and select After.
Now I have automatically created another column group that average all employees incomes based on the number of children they have.
Here you can see the finished report:
Let me just mention that this is only the beginning to groups. You can create groups inside of groups inside of groups that will display data in a unique way that adds another level of insight to the end users. It’s possible to group on any field and apply expressions to those groups, as well. So give it a try and see what you can come up with.