Updated: May 23, 2016
Applies To: SQL Server 2016
In this tutorial lesson, you will add grouping and totals to your Reporting Services report to organize and summarize your data.
Click the Design tab.
If you do not see the Row Groups pane , right-click the design surface and click View and then click Grouping.
From the Report Data pane, drag the Date field to the Row Groups pane. Place it above the row called (Details).
Note that the row handle now has a bracket in it, to show a group. The table now also has two Date columns -- one on either side of a vertical dotted line.
From the Report Data pane, drag the Order field to the Row Groups pane. Place it below Date and above (Details).
Note that the row handle now has two brackets in it , to show two groups. The table now has two Order columns, too.
Delete the original Date and Order columns to the right of the double line. This removes this individual record values so that only the group value is displayed. Select the column handles for the two columns, right-click and click Delete Columns.
To format the new date column, Right-click the cell with the
[Date]field expression and then click Text Box Properties.
Click Number, and then in the Category field, click Date.
In the Type box, select January 31, 2000.
Switch to the Preview tab to preview the report. It should look similar to the following illustration:
Switch to Design view.
Right-click the data region cell that contains the field
[LineTotal], and click Add Total.
This adds a row with a sum of the dollar amount for each order.
Right-click the cell that contains the field
[Qty], and click Add Total.
This adds a sum of the quantity for each order to the totals row.
In the empty cell to the left of
Sum[Qty], type the label "Order Total".
You can add a background color to the totals row. Select the two sum cells and the label cell.
On the Format menu, click Background Color, click Light Gray, and click OK.
Right-click the Order cell, point to Add Total, and click After.
This adds a new row containing sums of the quantity and dollar amount for each day, and the label "Total" to the bottom of the Order column.
Type the word Daily before the word Total in the same cell, so it reads Daily Total.
Select the Daily Total cell, the two Sum cells and the empty cell between them.
On the Format menu, click Background Color, click Orange, and click OK.
Right-click the Date cell, point to Add Total, and click After.
This adds a new row containing sums of the quantity and dollar amount for the entire report, and the Total label in the Date column.
Type the word Grand before the word Total in the same cell, so it reads Grand Total.
Select the Grand Total cell, the two Sum cells and the empty cells between them.
On the Format menu, click Background Color, click Light Blue, and click OK.
The last page should look similar to the following image. In the toolbar, click the Last Page button.
An optional step is to publish the completed report to the native mode report server so you can view the report from Report Manager.
Click the Project menu and then click tutorial Properties...
In the TargetServerURL type the name of your report server, for example
http://localhost/reportserverworks if your designing the report on the report server.
Note the TargetReportFolder is tutorial, the name of the project. This is the name of the folder that the report will deploy to in the next steps.
On click the Build menu and then click Deploy tutorial.
If you see a message similar to the following in the output window, it indicates a successful deployment.
------ Build started: Project: tutorial, Configuration: Debug ------
Skipping 'Sales Orders.rdl'. Item is up to date.
Build complete -- 0 errors, 0 warnings
------ Deploy started: Project: tutorial, Configuration: Debug ------
Deploying to http://[server name]/reportserver
Deploying report '/tutorial/Sales Orders'.
Deploy complete -- 0 errors, 0 warnings
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========
If you see an error message similar to the following, verify you have permissions on the report server and you have started SQL Server Data Tools with administrator privileges.
"The permissions granted to user 'XXXXXXXX\[your user name]' are insufficient for performing this operation"
Browse to the web portal with administrator privileges, for example, right-click the icon for Internet Explorer and click Run as administrator.
Browse to Reporting Services web portal URL.
Note: The portal URL is "Reports", not the Report Server URL of "Reportserver". For example:
http://localhost/reportsworks if your designing the report on the report server.
Browse to the folder that contains the report. The default name is tutorial, the name of the project or the name you typed into the TargetReportFolder fiedl in the project properties.
Click the name of the report Sales Orders to view the rendered report in the browser.
** You have successfully completed the Creating a Basic Table Report tutorial.**