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 Apr 19, 2005

MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Sorting with Parameters - Page 6

By William Pearson

The completed Report Parameters dialog for the sort direction parameter appears as depicted in Illustration 20.

Click for larger image

Illustration 20: Completed Report Parameters Dialog for Sort Direction

7.  Click OK to accept input and close the dialog, returning to the Layout tab.

We now have parameters in place to support the two sort considerations for the information consumers. Let's modify the report to leverage our new structures and bestow the capability to control sorting on an ad hoc basis.

8.  Select the Preview tab, to begin a quick check of connectivity and overall report operation.

The report executes, and appears as depicted in Illustration 21.

Click for larger image

Illustration 21: The Report Executes and Appears in Initial "Rolled Up" View

The report appears in its initial "rolled up" state, with the Total Sales summarized at the Territory level.

9.  Expand the Northwest territory row, by clicking the "+" sign to its left.

10.  Expand Pamela Ansman-Wolfe, which appears under the expanded Northwest row.

Ms. Ansman-Wolfe's sales order information appears, as partially shown in Illustration 22.

Illustration 22: "Drilled Down" View of the New Report

We will be able to evaluate the effectiveness of our enhancements best from this level, as we will be working with the Order Number and Total Sales values on the Layout tab in the following steps.

11.  Click the Layout tab to move to the Layout view.

12.  Click the "cell" containing the words "Order Number," within the table data region to "activate" the region, enabling us to see the border with gray selection handles around the table, as depicted in Illustration 23.

Illustration 23: Border and Handles Appear

13.  Right-click the upper-left-corner handle of the region.

14.  Select Properties from the context menu that appears, as shown in Illustration 24.

Illustration 24: Right-Click the Top Left Handle, and Select Properties ...

NOTE: The gray handles seem to disappear, with a semi-transparent border appearing in their place, when we perform the right-click. Selecting Properties from the context menu still gets us where we need to be, assuming the right-corner was selected when performing the right-click.

The Table Properties dialog appears, opening on the General tab.

15.  Click the Sorting tab within the Table Properties dialog.

16.  Input the values shown in Table 3 into the Sort on table:






=IIF(Parameters!SortAscOrDesc.Value="Desc", Fields(Parameters!SortSelection.Value).Value,0)



Table 3: Sort On Table Entries for the Sorting Tab, Table Properties Dialog

The Sort on table, on the Sorting tab of the Table Properties dialog, appears as depicted in Illustration 25.

Illustration 25: Our Entries in the Sort On Table, Sorting Tab of the Table Properties Dialog

17.  Click OK to save the entries, and to close the Table Properties dialog.

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