Tag Archives: Paul Litwin

Microsoft SQL Server 2008: Creating Groups

It’s difficult to create a very useful report without needing to group the data in some way. A report without any groups is either very simple—and there’s nothing wrong with a simple report—or very disorganized.
Groups are a great way to organize data in a report into a more manageable assemblage of information. If you need to create subtotals or other statistics you will likely need to create groups.

NOTE While the focus of this chapter—and all of the examples—is tabular reports and row groups created within a Table data region, some of the principles also apply to matrix and list reports, as well as hybrid reports that have attributes of tabular, matrix, and list reports.

The Grouping Pane

While previous versions of Reporting Services supported grouping, SQL Server 2008 Reporting Services has brought report groups to the forefront with the addition of the Grouping pane to the design surface. From the Grouping pane, you can easily view and manage your groups. You can see the Grouping pane at the bottom of the report design surface in Figure 1.

CreatingGroups1Figure 1. The Grouping pane appears at the bottom of the report design surface.

NOTE This chapter will focus on the row groups that are part of tabular reports. Elsewhere in this course you will find a discussion of Column Groups that are used on matrix reports.

The Details Group

By default, Reporting Services adds a details group—labeled (Details) in the Row Groups pane—to every Table and List data region. (Matrix data regions do not contain a details group and Chart and Gauge data regions do not use the Grouping pane.) The details group is unique in that it is a group that is not based on a grouping expression. Instead, it represents the detail rows in a Table or List data region.

Adding a Row Group

You can add a new row group to a Tablix data region either by dragging dropping a field from the Report Data window and dropping it on the Grouping pane or by using the Grouping pane’s popup menu.

Dragging and Dropping

Drag a field from the Report Data window and drop it onto the Row Groups area of the Grouping pane to create a new group. The key to getting the group into the correct place in the group hierarchy for the report is to carefully position the mouse cursor before letting go of the mouse button. As you hover over the existing groups, Reporting Services will draw a blue line to indicate where the new group will be inserted as shown in Figure 2.

CreatingGroups2Figure 2. The new group will be created as a child of the Country group and as a parent of the details group.

When you create a group using drag and drop, you can only create parent or child groups; you cannot create an adjacent group using this technique. Nor can you control the presence of group header and footer rows, or create a group based on an expression. If you need any of these group options, you’ll want to employ the Grouping pane menus to create your group.

Using the Grouping Pane Menus

To add a row group to a report using the Grouping pane menus, click on the down arrow to the right of an existing group and select Add Group from the menu as shown in Figure 3. A submenu will present several choices including Parent Group, Child Group, Adjacent Before and Adjacent After. (Creating adjacent groups will be discussed in more detail in the next section.)

CreatingGroups3Figure 3. Adding a row group using the Grouping pane.

After selecting the type of group that you want to create, Reporting Services displays the Tablix group dialog box that is shown in Figure 4.

CreatingGroups4Figure 4. The Tablix group dialog box.

To finish creating the group, select the Group by field using the drop-down list or click the fx button to group on an expression instead. Don’t forget to check the Add group header and Add group footer check boxes as appropriate before clicking OK, because Reporting Services makes it difficult to recreate the group header and footer rows once you have dismissed this
dialog box.

Adding Row Groups without the Grouping Pane
As an alternative to using the Grouping pane, you can right-click on a tablix row to
add groups to a report. Just click on a row selector of a detail or existing group row
and select Add Group from the popup menu.
Depending on the context when you right-click on a row, some grouping options may
be disabled or invisible. In general, you’ll have better success creating groups using
the Grouping pane.

Adding an Adjacent Row Group

Rather than add a group that is a child or parent of an existing group, you can add a group that is adjacent (that is, a sibling of) an existing group by selecting Adjacent Before or Adjacent After from the Add Group popup menu (see Figure 3).

Adding an adjacent row group is similar to adding a second tablix region to your report. The major difference is that each tablix region can be bound to a different dataset, whereas all of the groups within a tablix share the same dataset.

When you add an adjacent row group, you may be surprised to find that the new group will not have any detail rows. Fortunately, you can add a child details group to the adjacent row group.
paul_LitwinThis post is an excerpt from the online courseware for our Microsoft SQL Server 2008 Reporting Services course written by expert Paul Litwin.

Paul Litwin is a developer specializing in ASP, ASP.NET, Visual Basic, C#, SQL Server, and related technologies. He is an experienced trainer, has written articles for MSDN Magazine and PC World, and is the author of several books including ASP.NET for Developers (SAMS) and Access 2002 Enterprise Developer’s Handbook (SYBEX). Paul is a Microsoft MVP, the chair of the Microsoft ASP.NET Connections conference, and a member of the INETA Speakers Bureau.

SSRS 2008: Reporting Services and SharePoint

SQL Server 2008 Reporting Services supports two different installation options:

  • Native mode. The Report Server controls all report execution, rendering, and management.
  • SharePoint integrated mode. Report Server runs as part of a SharePoint server farm. SharePoint provides front-end access to content, management, and security. The Report Server takes care of report execution and rendering.

In order to use SharePoint integrated mode, you must have installed Windows SharePoint Services 3.0 or Office SharePoint Server 2007. You also need to configure Report Server for SharePoint integrated mode.

TIP:An alternative to deploying Reporting Services in SharePoint Integrated mode is to deploy Reporting Services in Native mode, but to use the Reporting Services Web parts to find and view reports from SharePoint.

Why Use SharePoint Integrated Mode?

SharePoint Integrated mode makes the most sense when your organization has made a strong investment in SharePoint and you want to leverage that investment when creating, executing, and managing reports.

A number of Reporting Services features are only available when working in SharePoint Integrated mode. When in integrated mode, you can:

  • Manage reports, data sources, and report history using SharePoint.
  • Use the document management and collaboration features of SharePoint with reports.
  • Make use of SharePoint managed authentication and authorization for reports. This includes the use of Forms authentication.
  • Distribute reports outside of a firewall using SharePoint.
  • Deliver reports using a SharePoint delivery extension.
  • Create custom integrations between a SharePoint site and Reporting Services using the ReportingServices Web services API.

A number of Reporting Services features are not supported when the Report Server is configured for Integrated mode. These include:

  • Report Manager
  • My reports
  • Linked reports
  • The rs.exe command-line utility

NOTE While there is much in common across Native and Integrated modes, where there is a difference, this course will cover the Native mode approach.

TIP: See SQL Server 2008 Books Online and your SharePoint documentation for guidance on SharePoint-specific features and interfaces.

paul_LitwinPaul Litwin is a developer specializing in ASP, ASP.NET, Visual Basic, C#, SQL Server, and related technologies. He is an experienced trainer, has written articles for MSDN Magazine and PC World, and is the author of several books including ASP.NET for Developers (SAMS) and Access 2002 Enterprise Developer’s Handbook (SYBEX). Paul is a Microsoft MVP, the chair of the Microsoft ASP.NET Connections conference, and a member of the INETA Speakers Bureau.