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

April 19, 2005

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.