In previous section, we learnt how to create an SSRS report and deploy it to our SSRS Report server. Now, we will learn how to use the Grouping feature in a SSRS report.
Use of Grouping in SSRS Report
- Click Start-> All Programs -> Microsoft SQL Server 2012 -> SQL Server Data Tools. Visual studio IDE will open.
- From main menu option, select File -> Open -> Project/Solution.
- The following dialogue box will appear, now browse for your project in recent sessions (.sln solution file).
- Right click on the Report folder in the Solution explorer pane, and then select Add -> New Item.
- Now, assign a name to your report, then click on the OK button.
- A report (ReportGrouping) will be added, and a designer view of the report will be displayed, as you can see below.
You will need Data Souce and Data Set for the report, as you have done for the previous one. You can use your Previous Shared DataSource for the same Database connection, but you are required to use Embedded Datasource when you need a different database to connect. For using the Shared Datasource, right click on the Datasource folder in the Report Data Pane then select Add Data Source.
Select the option of Use Shared Data Source reference (DSLocal), and give an appropriate name to it. Then, click on Ok Button.
Right click on the report Design, select Insert -> Table. A window will appear for the Dataset properties. Here name your to Dataset, Select the option Use a Dataset Embedded in my report. Datasource: Datasource1, as you have created for report. Type a query for the Data you want to get from the database (an appropriate case where a column has repeated values in the columns as shown in the next figure). Then, click on OK button.
Data returned by DataSet, needs to be grouped by their ProjectID
- Adding a Group in the SSRS Report. Right click on the left most side on the Data row of the table. Then Select Add Group -> Parent Group.
A following screen will appear for which the Group is needed to be created. So, either type ProductId name or Select it from the dropdown option. Then, click on the OK button.
An extra column will be added to the existing Tablix, you may can either delete it or use it. To delete this column you need to right click on first column, and then select the delete option.
It will prompt a pop-up box similar to the one found below, giving you options. Select the option of Delete Columns only.
- Details Group: The Details Group is specified as a group with no group Expression. We need to add a detailed Group to a Tablix, when you want to display the detailed data of a matrix. In our case, we need to Group data as per ProductID, so we need to delete the Details Group. Thus, it will restrict to unique ProjductID
Delete Details Group: Right click on the Details group in the Row Groups Pane, Select Delete Group option. You will again be prompted with an option box for deleting asking, Delete group with related rows and columns, or Delete Group only. Select Delete Group Only.
- Select the appropriate columns for the report as required.
You need to be careful while you select the Last column for the data, because this is an Amount column. It will include the price of each product and you need to sum up all of them for their corresponding related Products. So, just type the Sum Keyword like this SUM(Fields!amount.Value) as shown below. Right click on the column name, then select Expression option.
Type the SUM() outside the Fields!amount.Value column name, as shown below. Then click on the OK Button.
Click on the Preview button, you will see each available Productid with its Product name, and the Amount spent on each Product Type.
- Getting a total Sum for the Purchases : For this you will need to add one more row below. Right click on the ProductId Group in Row Groups Pane, then select Add Total ->
A new row will be added like this. In the first column, it will automatically add the productid, to avoid this, just delete the contents of the first cell of the third row i.e. Sum(ProductId).
Moreover, give an appropriate Report Name, by placing a Textbox at the header, and give some Bolder text property to it.
- Now, click on the Preview button and you will find the Output resembling something like the image below. Now you may deploe
We learnt how to use grouping in SSRS reports. In next blog, we will create SSRS reports using Stored Procedures and Parameters.