Grouping in SSRS Table Reports

In this article we will show you, How to Add Parent Groups and Child Groups to Tables Reports in SQL Server Reporting Services 2014.

We are going to use below shown report to explain, Grouping in SSRS Table Reports. Please refer SSRS Table Report, Data Source and Dataset articles to understand the creation of Data Source, Dataset and Table report. If you observe the below screenshot, It was a normal Table report with Product Name, Color, Sales Amount and Tax Amount columns.

Add Grouping to SSRS Table Reports

First, goto Row groups pane and right-click on the Details will open the context menu. From the context, Please select Add Group and then select the Parent Group option as shown in below screenshot

Once you select the Parent Group option, a new Tablix group window will be opened to configure the grouping.

• Group By: Here you have to specify the grouping column name. Either you can select the column name from drop down list or else click on the fx button to create an expression.
• Add Group Header: Please check mark this option, If you want to add header to this group
• Add Group Footer: Please check mark this option, If you want to add Footer to this group

In this example, We are selecting English Product Category Name as a Parent group for this detailed row.

Click Ok to finish creating Parent Group (English Product Category name). Click on the Preview button to see the report preview

Now we will show you, How to Add Child Group to SSRS table Reports.

First, Select the English Product Category name in the Row Groups pane and right-click on it will open the context menu. From the context, Please select Add Group and then select the Child Group option as shown in below screenshot

As we all know, English Product Sub Category name is a sub group of English Product Category Name so, We are selecting English Product Sub Category Name as a Child group for this row.

Click Ok to finish creating Child Group (English Product Sub Category name)

From the below screenshot, You can see the created Row groups under the Row Group pane

Click on the Preview button to see the report preview

Thank You for Visiting Our Blog

In this article we will add grouping in SSRS Report.

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

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

Now click on OK button.

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

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

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.

 

?

1 2 3 4 5 6 7 8 9

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.

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.

Then click on Next Button.

9. In next screen select Stepped layout.

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

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.

Then click on Finish Button.

11. Now your Report Design will look like below:

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

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

Congratulations! Our SSRS Report with Grouping is completed.