MSSQL Server Reporting Services: The Authoring Phase: Overview Part II - Page 5

March 29, 2004

Filtering the Dataset

Let's filter the dataset to make it smaller for purposes of reporting.

1.  On the Data tab, click the ellipses ("...") to the right of the Dataset Selector, as shown in Illustration 19.

Click for larger image

Illustration 19: Select the Ellipses ...

The Dataset dialog appears, as shown in Illustration 20.

Click for larger image

Illustration 20: The Dataset Dialog

2.  Click the Filters tab.

3.  Click the first row in the Expressions column.

A selector becomes enabled.

4.  Select the following from the options that are available in the Expressions selector:


5.  In the Operator column of the same row, select the "=" ("equal") sign, if not already in place

6.  Select <Expression...> in the Value column of the same row.

The Expression Editor opens.

7.  Type the following word into the Expression list:


We are specifying that we wish to filter the dataset to return the Clothing category only. Note that this is a filter of the dataset, and affects reports that rely upon the dataset; it is not a filter of the data source, as we shall see.

The Edit Expression dialog appears as shown in Illustration 21.

Illustration 21: The Edit Expression Dialog

8.  Click OK to accept the expression and close the dialog.

We are returned to the Filters tab of the Dataset dialog, which now appears as shown in Illustration 22.

Illustration 22: Dataset Dialog - Filters Tab

9.  Click OK once again.

The Dataset dialog closes, and we arrive at the Data tab of Report Designer.

10.  Click the Run button to populate the Result pane.

The Result pane is populated by the Dataset. We see not only the Clothing category, but all others from our initial query, as well. This is because, as I mentioned earlier, the filter lies between the Dataset (what we see in the Result pane) and the report.

11.  Click the Preview tab to process the preview of our report.

Processing begins, and data begins populating the report even as processing is occurring. We see that the Clothing category is the only category returned in the report, as partially depicted in Illustration 23.

Illustration 23: Report Preview (Partial View)

Our understanding of the filter operation is confirmed.

12.  Click the Layout tab to return to the Design Surface

Our new data column, Line Total, appears for selection in the Fields pane, as shown in Illustration 24.

Illustration 24: Line Total Joins the Field Selection

We are now ready to add the Line Total field to our report. But first, let's save our work to free up system resources, as well as to avoid any mishaps.

13.  Select File --> Save RS02_Authoring.rdl As ... from the main menu.

14.  Navigate to the location of choice in the Save File As dialog.

15.  Name the file as follows:


We will add a new data field and a subtotal next.