I was recently try to make sample grouping in report using reporting service. I working with Sql Server Management Studio (2005) and Microsoft Visual Studio 2005.

First Step :  I working with with Sql Server Management Studio (2005)

In SQL Server 2005 I create a table, entry data to the table and make Store Procedure as the link to Reporting Services to view report. For example I will create my own table, Employee Table.

This is the content of Employee Table

And then make simple Store Procedure, I make SP_ViewEmployees procedure. This is the script:

Second Step : Working with Microsof Visual Studio 2005

The step to create reporting service  are as below:

- I start with open this menu : File - New - Project, In the New Project Window I  choose Other Project Types, and then choose Visual Studio Solution.

- I give Report01 as the solution name, Right click the solution and then Add New Project from this menu : Add - New  Project.

- In New Project window I choose Business Intelligence Project, and the click Report Server Project.And I give Report01 as the project name.

- Right click Share Data Sources Folder in Report01 Project, choose Add New Data Source

To set the properties, click button Edit and follow the below picture to set Connection String properties base on your database connection setting.

- Right click Reports Folder in Report01 Project, choose Add - New Item and then click Report, I give rptGrouping as the report Name

- In Data tab see Dataset drop down list. Click the drop down list -  choose DataSet and  entry all requirement as the below picture

- In the report Layout tab you can make the design as below picture by using table and in the detail rows insert groups

Set the Group properties as picture below, uncheck  include group header and include group footer

To set the value of each column you can right click in the column detail and choose expression.To Employee ID, Name, Gender  and Group No value  follow this step. For example Employee ID column, right click  in the Employee ID detail and then choose expression,after edit expression window appears,choose Fields (Dataset1)  double click Employee_ID Fields (DataSet1) and then click button OK.

I make the grouping in the column No, so right click in the No column detail and then choose expression.

And give the expression value as below picture.

- In the preview tab you will see the result like this, before insert the year value. For example 2008

Hope you enjoyed trying this!