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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Apr 19, 2005

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

By William Pearson

We return to the Layout view of the Report Designer. We will test our enhancements by again previewing the report.

18.  Click the Preview tab to execute the report.

The report executes, using the parameter defaults. We will rerun it with new settings.

19.  Ensure that Total Value is selected in the Sort Upon parameter selector.

20.  Select Ascending in the selector labeled Ascending or Descending?

The selectors in the report appear as shown in Illustration 26.

Click for larger image

Illustration 26: Parameter Selectors Prior to Executing Report

21.  Click View Report to execute the report.

The report executes and returns data.

22.  Expand the Northwest territory row, by clicking the "+" sign to its left, as we did before.

23.  Expand Pamela Ansman-Wolfe, appearing under the expanded Northwest row, once again.

Ms. Ansman-Wolfe's sales order information appears, sorted by sales value in ascending order, as partially depicted in Illustration 27.


Illustration 27: Sorting by Total Value in Ascending Order

We thus see that our solution has proven effective, as the data is sorted, based upon our parameter selections. Let's try another sort to confirm our evaluation. This time, we will sort by Sales Order Number, descending. (We note that the Order Numbers are not currently sorted in descending order.)

24.  Select Sales Order No. in the Sort Upon parameter selector.

25.  Select Descending in the selector labeled Ascending or Descending?

The selectors in the report appear as shown in Illustration 28.


Illustration 28: Parameter Selectors Prior to Executing Report

26.  Click View Report to execute the report.

The report executes and returns data.

27.  Expand the Northwest territory row, by clicking the "+" sign to its left, as we did before.

28.  Expand Pamela Ansman-Wolfe, appearing under the expanded Northwest row, once again.

Ms. Ansman-Wolfe's sales order information appears, sorted by Sales Order Number in descending order, as partially depicted in Illustration 29.


Illustration 29: Sorting by Total Value in Ascending Order

We thus see that our solution once again produces the desired results, and meets the business requirements expressed by the information consumers.

29.  Select File --> Save All from the main menu to save all work to the present.

30.  Exit Visual Studio when ready.

Conclusion ...

In this article, we continued our exploration of conditional formatting, once again in a basic application, and this time focusing upon the creation of parameterized conditional sorting to allow users to control sorting in an ad hoc manner at report runtime. After making structural changes to a copy of a sample report, based upon the sample AdventureWorks database (both of which accompany an installation of Reporting Services), we set about to meet the business requirements of a hypothetical group of information consumers.

We created a couple of report parameters, which provided the basis for conditional sorting. We associated the parameters with the sorting conditions in the data region of the report under consideration through conditional logic we put into place in the sorting properties of the report. Once we married the conditions of sorting with the parameters we created, we previewed the report to ascertain the effectiveness of our solution. Finally, in our testing of the ad hoc sorting capabilities we had added, we discussed the results obtained with the development techniques that we had exploited.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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