Session 12: SSRS Grouping and Totals AnandFriday, December 19, 2014 0 comments

                              Session 12: SSRS Grouping and Totals

Many times we need to group the data in order to drill down further.
To put it simply, imagine you have a report which gives you sales amount for each of the continent.

You might want to drill down into each of these continents and see the sales amount for each country in the continent.

Next, you might want to drill down further to see the sales amount for each state in a country.

Next you might want to drill down from yearly sales to say quaterly...and so on a so forth.

This is where we introduce Groups and drill downs  If you have used the Group By clause, for aggregation, you might know, what I am talking about.

In any case, lets take an example from the adventure works database.

Lets consider 4 tables from AdventureWorks 2008 R2
1. Fact Internet Sales
2. DimDate
3. DimSalesTerritory
4. DimProduct
5. DimCustomer.

If you run the below query in SQL Server, you will get the following:
Query:


SELECT D.CalendarYear AS [Year]
,D.CalendarQuarter AS [Quarter] 
,D.EnglishMonthName AS [Month]
,D.FullDateAlternateKey AS [Date]
,B.EnglishProductName AS [ProductName]
,C.FirstName + ' ' + LastName AS [CustomerName]
,ST.SalesTerritoryRegion AS [SalesRegion]
,ST.SalesTerritoryCountry AS [SalesCountry]
,A.SalesOrderNumber AS [OrderNumber]
,A.SalesAmount 
FROM FactInternetSales A
JOIN DimProduct B 
  ON B.ProductKey = A.ProductKey 
JOIN DimCustomer C 
  ON C.CustomerKey = A.CustomerKey 
JOIN DimDate D 
  ON D.DateKey = A.OrderDateKey 
JOIN DimSalesTerritory ST 
  ON ST.SalesTerritoryKey = A.SalesTerritoryKey 

Now we have some data with us to work with.

Lets use this data in our report and group the data.
Then we will introduce drill through  in our reports.

Step 1: Make a report, with a table control by dragging and dropping CustomerName, OrderNumber and SalesAmount.
Hit preview - Your report should look something like this.

Step 2:
Now lets Add a product name, and lets put the customer Name,  order number and Sales Amount under it.

Right click on the grouping area --> Add group --> Parent Group --> Group by 'Product Name' (Also Add a group header)

Hit preview. Your report should look something like this.

So lets add more groupings.
Year--Quarter--Month--Date--SalesRegion--SalesCountry--ProductName-Details

So your design view should look something like this:

And your output should look something like this: