How to group a report in SSRS(SQL Server Reporting Services)

In my second post I showed how to add a filter to a report in SSRS. Today I am going to show how to create grouping to a report in SSRS.

For this task I am going to use a new report in SQL Server business intelligence development studio. I suppose you know how to add a datasource and the dataset to a report, if you are not aware of that you can refer my first and second posts in the blog. I created a stored procedure for my report dataset.

After setting the dataset, now you can design your report. Here first I added two columns to the table and named them as Name and Address.

According to the report dataset, teams are under groups and groups are under activities. So each activity can have more groups and each group can have more teams. Team can have members. Now what I need is to group the report under activity, group and team levels.

Below the report design view you can see there are two columns "Row Groups" and "Column Groups". Right click on row groups (Details) bar go to Add Group --- Parent Group. Now you can see a dropdownlist and select the report data field you need to group. I select the [Activityname]. Then click ok.

Then you can see your report like this and name the column header "Group1" as you need. I named it as Activity.

Now I need to group the report in group level and it is under the activity level. So you need to add a child group to first group. Right click on Group1 in the "Row Columns" go to Add Group --- Child Group. Then select the report data field you need to group. I select the [GroupName]. Click ok.

Now you can see report like this and rename the "Group2" column header as you need. I rename it as Group. Now I need to group the report in team level. Teams are under groups so again I create a child group under groups. Right click on "Group2" bar in Row Columns and go to Add Group as same as before and set a child group. Select the report data filed as you need. I select the [TeamName]. Click ok.

Rename the column header "Group3" as you need. I rename it as Team. Now my grouping part is over. You can now preview the report. Just click on Preview tab. You will see a report like this.

This is a one of grouping a report. In addition to this if your report contains number columns like "Initial Amount", "Paid Amount" you can set a total row and also a subtotal row in each group levels. For that task what you need to do is right click on the group bars under Row Columns and click on Add Total.

I think You got some knowledge about grouping a report in SSRS. Next time I will discuss how to deploy a report in SSRS. If you find anything useful please leave a comment. Thank you.

How to Add a Filter to a Report in SSRS (SQL Server Reporting Services)

In my first post I showed how to create a report, add a datasource and add a dataset to it. Today I'm going to add a filter to that report and it is very important to add a filter because it make easier the user to search data.

First you need to add a parameter to the report data. In my report you can see there is a dataset field called Gender. I'm going to filter my report gender wise. 

(01). Go to report data and right click on parameter folder add a parameter.

you can see there are two fields Name and Prompt. Name is the name of the parameter and Prompt is the display name of the parameter. 

Then name the parameter as you wish. I named it as gender for parameter name and Gender for for parameter prompt

Then you have to set the parameter type. You can set it using Data type combobox. Here my dataset field Gender is a varchar value so I set the type as text.

Then keep the parameter visibility as visible.

(02). Click on the Available Values in report parameter properties. Now you can see three options listed and select the specify values.

Now you have to add and specify values that your report parameter can have.  Gender field can have Male or female. Click on the add button and set the label as Male and value as Male. Click again the add button and set the label value as Female and value as Female. Then click ok.

Now successfully created the report parameter that is used for filter.


(03). Go to report data again and right click on the dataset and go to dataset properties.

In dataset properties view click on filters. Then click on the add button. There you can see Expression, Operator and value lables. In Expression you need to select the field that you are going to filter. So it should be [Gender]. Then set the operator to be (=). Finally the value, this the most important part. In value field you have to give parameter value you pass to the report. value field set with the @ symbol. It should be as [@gender]. Then click ok. 

Now you added the filter to the report successfully. 

(04). Click on the preview tab of the report. Select a the filter value you need to filter the report. Then click on View Report.

Now you know how to add a filter to a report in SSRS. You can improve your report using filters. You can create filters using different parameter types as datetime, booleon, integer instead of text. I will discuss how to group a report in SSRS in my next post. Thank you.

Create a report in SSRS (SQL Server Reporting Services 2008)

SSRS is a comprehensive server based solution for reporting and it is widely used for creating, managing and delivering web based reports. Let's see a simple way of creating a useful report for a particular application.

(01). First open the SQL Server Business Intelligence Management Studio (Start menu > SQL Server 2008 > SQL Server Business Intelligence Development Studio)

(02). Then add a report server project under Business Intelligence projects.

(03). After adding the project right click on the reports folder in the solution explorer and click add new item. There you can select Report wizard and Report; we start with the report (.rdl).

(04). Now you have to design the report, you can use the toolbox items to add a table and  columns. You can also add a matrix instead of a table.

(05). When the table is added to the report it shows a popup to set the data source for the report.You have to set  a  particular  database  in  your  local  machine  or  a  particular  server  as  the  datasource. 

Make sure that when you set the connection to datasource keep the CheckBox (save my password) checked, otherwise you have to give datasource credentials when the report is previewed.  Now you have successfully created the datasource for your report. Then you have to set a dataset for the report. The dataset  can be created using Query Designer or a stored procedure in the datasource (database). We do it using stored procedure.

(06). Use a stored procedure to set it as the dataset of the project.

Now you can view the report data in a window by clicking the view in menu bar and clicking the report data on it. In report data there are folders named Built-in fields, Parameters, Images and Datasource. Inside the datasource folder you can see your dataset and its fields.

Then you can easily set the dataset fields to report columns as in the following image.

Now your report is ok. If the dataset stored procedure contains parameters they automatically added to the parameters folder in the report data. When you preview the report you need to provide the report parameters. Otherwise the dataset stored procedure doesn't contain parameters just click on the preview tab and report will be generated.

Adding filters, grouping and adding subtotals can be added this report and also add colors to make the report attractive. I'll discuss them in my next post.