Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 29, 2004

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

By William Pearson

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.


MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM