Microsoft SQL Server 2008 Business Intelligence Development and Maintenance: Developing SSRS Reports

Lesson 4: Applying Dataset Filters and Groups

Estimated lesson time: 25 minutes

After you have created the dataset that will provide data to the report, you need to create the report items that will display the data to the user. These items are structured as data regions.

Assigning Datasets to Data Regions

As you saw earlier in this chapter, a data region is a report item that displays rows of data from the source datasets. SSRS has three data regions: Tablix, chart, and gauge. There is no Tablix report item in the Toolbox window; you start working with Tablix by using a List, Table, or Matrix report item. Up to now, you have used the Report Wizard to create data regions and assign datasets automatically, but you can also create data regions and assign the datasets manually. To create a data region, you need to navigate to the Report Designer’s Design tab, and then drag one of the five report items described in the following sections to the report layout region.

The List Report Item

The List report item is a data region that lets you present information without any predefined structure. To use the List report item, drag the report item onto the Layout pane, and then drag a field from the Dataset pane and onto the List report item. The Report Designer will automatically create a new text box report item with the value of the dropped field and will automatically map the List report item to the dataset you dragged the field from. You can verify the bound dataset in the Properties window DataSetName property. You can use other report items inside the List report item to create the user interface for the report. You start with a single column and row; however, you can enhance your list design by adding additional detail rows and nested or adjacent row groups or column groups.

A list is actually a container—you can use it to display grouped data if you edit the Details group. You just have to specify a name and group expression. You can also have other data regions, such as Table or Chart data regions, embedded inside the list, bound to the same dataset as the list. Nested report items with grouped data repeat once for every group value.

The Table Report Item

The Table data region presents information in a tabular format. Table data regions have a predefined number of columns and present one row for each row in the dataset. The Table data region also allows multiple levels of data grouping over rows and the inclusion of graphical elements within the table. Of course, because the Table report item is just another template for the Tablix data region, you can enhance the table to use column groups, thus changing the table to a matrix.

The Table report item lets you sort, group, and filter information based on dataset fields and expressions, as Figure 10-6 shows. You will learn how to use these features in the section “Applying Filters, Groups, and Sorts to Data Regions” later in this lesson.

You can add totals for detail data. You can also change or add groups by using the Row Groups and Column Groups panes. In addition, when you select a table report item, the Report Designer displays handles, which you can use to configure different table options. The table report item has the following handles. (The numbers correspond to the numbers in Figure 10-6.)

• Table handle (1) Lets you select the table. When you have the table selected, you can right-click the table item and then click Properties to display the Tablix Properties window, where you configure table properties such as sort order, visibility, and filters.

• Column Header handle (2) Lets you select a column. With a column selected, you can change the properties of all column cells simultaneously, change the visibility of a column, and add or delete columns.

• Table Header handle (3) Displays column titles for the table. With a table header selected, you can change the row visibility, add or delete rows, and change properties such as the font for all row cells simultaneously.

• Group Header handle (4) Can be displayed or hidden. With the Group Header handle, you can also edit row group properties and add additional row groups—either parent, child, adjacent above, or adjacent below.

• Detail handle (5) Lets you configure the final level of grouping of the table, including the row visibility. You can also add rows to the details section or delete them from the section.

• Group Footer handle (6) Displayed. You can change the row visibility, add or delete rows, change group properties, and add or delete row groups.

• Table Footer handle (7) Frequently used to present table totals. You can add or delete rows in this area and change visibility.

The Matrix Report Item

A Matrix data region is similar to a Table data region except that a Table data region has a predefined set of columns and the Matrix data region can dynamically expand the number of columns it contains in order to pivot information. Matrix data regions can have dynamic or static rows or columns, and you can use matrices to create crosstabs and PivotTable reports.

Working with a Matrix data region is similar to working with a Table data region, but it has two dimensions for working with data: columns and rows. You start with a single row and a single column group; you can add additional row and column groups, either parent, child, adjacent before, or adjacent after. Because the Matrix report item is again just another template for the Tablix data region, you can change a matrix to a table by adding detail rows.

The Chart Report Item

The Chart report item is different from other data regions in that it summarizes information from the dataset; other data regions render one row for each row in the dataset. You use charts to summarize data in a visual format.

The Gauge Report Item

The Gauge report item, new in SSRS 2008, is a one-dimensional data region. You can display a single value in it. However, a gauge is positioned in a gauge panel, where you can add additional gauges.

Applying Filters, Groups, and Sorts to Data Regions

After data has been assigned to a data region, you can filter, group, or sort the information that is presented to the user.

Grouping Data by Using Data Regions

A central feature of the Tablix data region is that it lets you configure multiple groups of data. The Table report item can use row groups, and the Matrix report item can use row and column groups. The List data region has only one grouping level: the detail group. And the Chart data region always groups all data in the set.

To add a group in a Tablix data region, follow these steps:

1. Select the Design pane, which lets you add groups in multiple ways.

2. If you have a table with detail data only, without any groups, you can right-click a column in the table header, and then on the Textbox shortcut menu, click Add Group. Then select either Column Group or Row Group. You can also add a group by simply dragging a grouping column to the Row Groups or Column Groups area in the Design window.

3. If you already have some row or column groups in the Row Groups or Column Groups area, you can right-click a group and then click Add Group.

4. In the Tablix Group window, you can define the grouping expression and add a group header and group footer.

5. When your group is created, you can change its properties by right-clicking the group and then clicking Group Properties. In the Group Properties window, you can change general properties (including Group Name and Expression), page break options, sorting, visibility, filters, group variables, and advanced properties (including Recursive Parent and Document Map).

Nesting Data Regions

In addition to using a data region with multiple grouping levels, you can also use nested data regions within other data regions. For example, you can use a List report item to group information by year and then drag another List report item onto the first List report item to group information within the Year List report item by quarter. Last, you can also drag a table element onto the Quarter List report item and configure the table with two groupings: Month and Week.

The List report item is not the only data region that can be nested. You can drag report items onto table and matrix report items and create a nested data region. For example, you can add a gauge inside a matrix to display summary data graphically.

Filtering Data

After data has been retrieved from the database, you can apply filters to the information that will be presented to the user. Filtering data is not the same as using parameters in the dataset; you will learn more about query parameters in Chapter 11. Filtering occurs after data has been retrieved from the source, and it lets you set two or more regions to use the same dataset but show different information to the user. For example, you could have a table show 6 months of data and a graph show 12 months of data—both from the same dataset.

SSRS lets you use filters in many different places:

• At the dataset

• At the data region

• At the row or column groups in a Tablix

• At the details group in a Tablix

• At the series or category groups in a chart

Filters at the dataset can be configured as follows:

1. Select the Report Data window.

2. In the Report Data window, right-click the dataset you want to filter, and then select Dataset Properties.

3. In the Dataset Properties dialog box, click the Filters tab.

4. In the Change Filters pane, click Add.

5. Configure the filter expression, data type, operator, and value. If your filter expression is simple, such as a single data field, the data type is assigned automatically from the field data type.

Filters at the dataset are very similar to parameters because they affect the data that is presented to all regions. However, filters always occur after data has been retrieved from the data source and might not perform as well as parameters because the query pulls all the data, not just the specific data that the user needs.

The main advantage of filters is that information can be shared between multiple users if you configure the report to use report execution snapshots. In report execution snapshots, the first execution of the report pulls the data from the server, and successive executions use the snapshot information stored in the report server.

You can configure filters at the data region as follows:

1. In the Report Designer, click the Design tab.

2. In the design area, select the data region you want to configure.

3. Right-click the data region, and then select Data Region Properties.

4. In the data region Properties dialog box, click the Filters tab. (Note that there are different names for the data region properties dialog boxes for different data regions, such as the Tablix Properties dialog box for a Tablix data region.)

5. Configure the filter expression, data type, operator, and value.

Filters at data regions affect only the report item they are filtering, and different data regions can apply different filters to the same dataset.

Filters at the data grouping can be configured as follows:

1. In the Report Designer, click the Design tab.

2. In the Row Groups or Column Groups area, right-click the group you want to modify, and then click Group Properties.

3. In the Row Properties or Group Properties dialog box, click the Filters tab.

4. Configure the filter expression, data type, operator, and value.

Sorting Data in Data Regions

As with filters, sorting can be used in different places in your report. You can sort the data in your report in a dataset query, data region, or grouping. To sort data in the dataset query, use the appropriate syntax to sort the information. For example, in SQL, you use the ORDER BY clause to sort data in the dataset.

To configure the sort order in a data region, follow these steps:

1. In the Report Designer, click the Design tab.

2. Select the data region.

3. Right-click the data region, and then select Data Region Properties.

4. In the Data Region Properties dialog box, click the Sorting tab, and in the Change Sorting Option tab, click Add.

5. Configure the column to sort by and the order (that is, the direction of the sort).

6. Click OK.

To configure sorting at the grouping level, follow these steps:

1. In the Report Designer, click the Design tab.

2. In the Row Groups or Column Groups area, right-click the group you want to modify, and then click Group Properties.

3. In the Group Properties dialog box, click the Sorting tab, and in the Change Sorting Option dialog box, click Add.

4. Configure the column to sort by and the order.

5. Click OK.

Applying Aggregates to Data Regions

When adding groups to the data regions, you can also select to display header and footer rows for each group level. The headers and footers can be displayed or hidden by using table or matrix handles.

You can use the headers and footers to provide the user with data aggregates. The aggregates can include any of the aggregate functions such as SUM, AVG, COUNT, and many others.

To add an aggregate, click the icon displayed to the right within a text box from a header or footer and then select a dataset field, or drag a field from the Report Data window to the text box. If the field is numerical, the Report Designer will automatically use the SUM function to calculate the value. You can change the value with any other aggregate function. If the field is not numerical, the Report Designer does not use an aggregate function. In such a case, it is easier to right-click the text box where you want to have the aggregate, and then select Expression on the Textbox shortcut menu. You can then edit the expression manually in the Expression dialog box.

In your reports, you can also use a special type of aggregate—called a running aggregate—that adds one row at a time. A running aggregate is calculated in each of the rows, not only at group levels. Running aggregates are useful in studying the accumulated performance of a field.

SSRS supports two running aggregate functions: RowNumber and RunningValue. The RowNumber function returns a running count of rows in the dataset. The RunningValue function lets you specify the aggregate function that the report should use to calculate the value. For example, you can use the SUM function to have a running accumulated value, or you can use the AVG function to have a running average value.

Practice: Creating Advanced Data Regions

In this practice, you will use BIDS to create a report based on two data sources. The report will have two sections. The first section will show summarized data in matrix format from an SSAS UDM cube, including a graphical presentation with a gauge. The second data region will show detail data with running totals from a SQL Server source. You will use the report with an empty layout and the two datasets you created in the Lesson 2 practice, “Creating Report Datasets”.

EXERCISE 1: Create the Matrix Data Region

In this exercise, you create the first data section of the report and then configure a matrix that uses the TMDimensional data source.

1. In BIDS, open the TK 448 Ch10 SSRS Purchasing project.

2. In the Reports folder, double-click the TargetMail.rdl report to open the Design tab in the Report Designer for this report.

3. Select Body in the Properties window, and then change the size of the body by setting the Width property to 6.5in and the Height property to 4in.

4. In the Toolbox window, drag a Textbox report item to the top-left corner of the report.

5. In the Properties window, change the width of the text box to 2.5in. Change the font to Arial, size 14pt, weight SemiBold. Type Target Mail Overview in the text box as the report header.

6. Drag the Matrix report item onto the report body, under the report header.

7. In the Report Data window, drag fields from the TMDimensional dataset, as shown in the following table.

   Field	Area
   Bike_Buyer	Data
   Number_Cars_Owned	Rows
   Gender	Columns

8. Right-click the data text box containing the Sum(Bike_Buyer) expression. From the pop-up menu, in the Tablix section, select the Insert Column submenu. On the submenu, select the Inside Group—Right option. The new column should be inside the Gender column group.

9. Drag the Gauge report item to the new details cell, to the right of the Sum(Bike_Buyer) cell. Select the Bullet Graph gauge, the last one in the Linear group. Click OK.

10. Click the gauge inside the text box to select only the gauge. A new Drop Data Fields area should appear at the top of the gauge. Drag the Bike_Buyer column to the LinearPointer1 box inside the Drop Data Fields gauge area.

11. Right-click the gauge, and from the pop-up menu, select the Gauge submenu and then select Pointer (LinearPointer1) Properties.

12. In the Linear Pointer Properties dialog box, click the Pointer Fill tab. Change the secondary color to Red. In the left pane of the Linear Pointer Properties window, click the Pointer Border tab. Change the Line Style option to Solid, and then click OK.

13. Right-click the gauge again, and from the pop-up menu, select the Gauge submenu, and then select Linear Scale Properties.

14. In the Linear Scale Properties dialog box, click the General tab. In the Value And Interval Options group, change the Maximum value to 2,000, and then click OK.

15. Click the matrix to select it. Click the header (the first row) handle. Press Ctrl+B to make the text bold in the row. Enlarge the first column to be 1.8 inches wide (click the column header to select it, and then use the Properties window of this column to change the Width property).

16. Preview the report. You can see the total of bike buyers across gender and the number of cars owned in numbers and graphically.

EXERCISE 2: Add a Table Data Region

In this exercise, you add a table to show detailed data from the relational source.

1. In the Toolbox window, drag the Table report item to the Design window. Position the Table report item under the matrix.

2. In the Report Data window, from the TMRelational dataset, drag the following fields to the Data area: LastName in the first column, FirstName in the second, and BikeBuyer in the third.

3. In the Row Groups area, right-click the Details group, click Add Group, and then click Parent Group.

4. In the Tablix Group dialog box, use the Gender field as the Group By Expression. Select the Add Group Header check box, and then click OK.

5. In the Row Groups area, right-click the Group1 group, click Add Group, and then click Parent Group.

6. In the Tablix Group dialog box, use the NumberCarsOwned field as the Group By Expression. Select the Add Group Header check box, and then click OK.

7. In the Tablix header area (first row), change the value of the first text box to Number Of Cars Owned and of the second text box to Gender. Make the first column 1.8 inches wide.

8. In the NumberCarsOwned header area (second row), click in the Bike Buyer column (last column), and then click the displayed icon to view a list of the fields in the dataset. Select the BikeBuyer field. You should get the Sum(BikeBuyer) expression.

9. In the Gender header area (third row), click in the Bike Buyer column (last column) to get the quick field icon. Select the BikeBuyer field. You should get the Sum(BikeBuyer) expression.

10. In the Row Groups area, right-click Group1, and then click Group Properties. In the Group Properties window, click the Visibility tab. Change the initial visibility to Hide, and then select Group2 as the toggle item.

11. In the Row Groups area, right-click Details, and then click Group Properties. In the Group Properties window, click the Visibility tab. Change the initial visibility to Hide, and then select Group1 as the toggle item.

12. Preview the report.

EXERCISE 3: Create a Running Total

In the last exercise of this practice and this chapter, you add a running total to the table.

1. Right-click the BikeBuyer column in the detail area (last row, last column).

2. From the pop-up menu, from the Tablix section, select Insert Column, and then select Right.

3. Right-click the new detail column (last row, last column), and from the pop-up menu, from the Textbox section, select Expression. Enter the following expression to get the running sum of bike buyers in the scope of the Gender group:

   =RunningValue(Fields!BikeBuyer.Value, Sum, "Group1")

4. In the Tablix header area, in the running total column (first row, last column), type Running Total for the header.

   Your report design should look like the one shown in Figure 10-7.

   

   Figure 10-7 Target Mail Overview report

5. Preview the report.

6. Save the solution, and then close BIDS.