Grouping in SSRS Report

Grouping in SSRS Report

In this article we will add grouping in SSRS Report.

1. First of all open Visual Studio 2010 than go to File–> New –> Project

Grouping in SSRS Report-1

2.  A New Project Window pops-up. Select Business Intelligence from installed templates and then select Report Server Project.

Grouping in SSRS Report-2

Now click on OK button.

3.  Then Right click on Reports Folder in your Solution Explorer and select Add New Report from context menu.

Grouping in SSRS Report-3

4. A Report Wizard Window will Pops-up. In that window, click on Next Button.

Grouping in SSRS Report-4

5. Now create shared data source as described in following link:

http://bhushan.extreme-advice.com/create-shared-data-source-in-ssrs

6. Now you can design query with the help of Query Builder or you can use readymade query. We are going to use readymade query.

 Grouping in SSRS Report-5

SELECT      C.FirstName + ' ' + C.LastName AS Employee, DATEPART(Year, SOH.OrderDate) AS OrderYear, 
			DATEPART(Month, SOH.OrderDate)as OrderMonth,SUM(SOD.LineTotal) AS Sales
FROM        Sales.SalesOrderHeader SOH INNER JOIN
            Sales.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN
            Sales.SalesPerson SP ON SOH.SalesPersonID = SP.BusinessEntityID INNER JOIN
            HumanResources.Employee E ON SP.BusinessEntityID = E.BusinessEntityID INNER JOIN
            Person.Person C ON E.BusinessEntityID = C.BusinessEntityID
GROUP BY    C.FirstName + ' ' + C.LastName, SOH.SalesPersonID, DATEPART(Year, SOH.OrderDate), 
            DATEPART(Month, SOH.OrderDate), DATENAME(Month, SOH.OrderDate)

7. Then Report Type Dialogue Box Open. Select Tabular Report type.

Grouping in SSRS Report-6

8. In next step we can select in which area we want fields to show in report.

Here we add Employee & OrderYear into Groups Section and OrderMonth & Sales into Details Section.

Grouping in SSRS Report-7

Then click on Next Button.

9. In next screen select Stepped layout.

Also checked  checkboxes of Include Subtotals and Enable Drill-down.

Grouping in SSRS Report-8

Then click on Next Button.

10. Then Select Table Style and then click on Next Button.

In next screen give the name of Report. Here you also find summary of our report.

Grouping in SSRS Report-9

Then click on Finish Button.

11. Now your Report Design will look like below:

Grouping in SSRS Report-10

Now delete Sum (OrderMonth) from both fields above because there is no need of sum of order month.

Grouping in SSRS Report-11

 

12. Then click on preview button. So your report will looks like below :

Grouping in SSRS Report-12

Congratulations! Our SSRS Report with Grouping is completed.