Reporting Services is a great tool for reporting on hierarchial data. This hierarchial data will form the foundation of our data architecture for the report. It is very important that we structure our dataset (that our report will consume) in a manner that satisfies our business requirement as well as a consistent and correct data architecture. In this article I will create a simple report called Employee Leads by Product. The business requirements for this report will display all bank employees by the financial products they have sold, along with the customer demographic who have purchased that particular product. The customer in this context is referred to as a "Lead" or more simply a "Sales Lead".
Below is a table definition that will describe the Data Architecture of the report:
create
table SalesLeads
(
employee
varchar(50),
product
varchar(50),
leadname
varchar(50),
leadacct
varchar(50),
leademail
varchar(50),
leadphone
varchar(50),
revenue
money
)
From this table definition, we can describe our business requirement and data architecture. After populating the table with some data, we can see how are data architecture and hierarchies will flesh out.
The Employee column is the first level in our hierarchy. Here you see the employees Jane Smith and Heather Pierce repeated on multiple lines. The second level in the hierarchy is the Product and you will notice also that there are repeating values in here as well. This is correct and necessary for the hierarchies to function properly. It may look funny, but the important rule to remember here is that: All hierarchy names/id's will be repeated on every line for their level AND sublevel for every transaction (or line item) being reported on. For example, we see that Jane Smith is repeated 6 times. How we would describe this is that Jane Smith sold 3 financial products (Mortgage, Money Market, and COD) to a total of 6 people. That fact that she sold a Mortgage, Money Market, and COD to Tim Harris doesn't consolidate the total, it expands it because they are all separate transactions under the Product hierarchy.
Now on the Reporting Services design side, I have a table control that is populated with a very simple SQL statement:
SELECT * FROM SalesLeads
Since we have our data architecture defined and populated properly on the table we don't need to have any GROUP BY clauses in our SQL statement. We will let Reporting Services handle the hierarchies through Groupings. Below is the property page for the table control with 2 groups (Hierarchies) defined. The first one is the employee and the second is the Product. That's it! The customer demographic will reside in the Details line of the table.
Now let's table a look at the table in Report Designer:
Here we placed the employee column in the 1st group header and the employee column in the 2nd group header. The details line will simply contain the customer/"Sales Lead" that will fall neatly into each Grouping/Hierarchy in which it belongs.
The final result of the report looks like this:
Did you like this article?
This topic and many more will be covered
in my new Reporting Services class.
Check it out, this class is AWESOME!
http://www.endtoendtraining.com/rs.aspx
I also do on-site training for SSIS, SSAS, and SSRS
www.PragmaticWorks.com
To view more of these articles, please visit my blog site at:
http://pragmaticworks.com/community/blogs/
In the next article, I will show how to use the visibility and toggle item properties of Reporting Services to show/hide your groupings in a tree-view.