|
-->
Interactive Sorting in SQL Server Reporting Services
By Bhargava Pandey | |
Rating: 2.9 out of 5
Rate this article
|
email this article to a colleague
suggest an article
|
Introduction
SQL Server 2005 Reporting Services has an interactive sorting feature built-in.
It allows you to click on the header of a column in a report and sort the report
by that column in either ascending or descending order.
Unfortunately SQL Server 2000 does not include this feature.
However; we can use the Render Report by URL technique to implement
interactive sorting in SQL Server 2000 Reporting Services.
To achieve this we render the report by creating the entire URL for the report
which has all the parameters and their values in the URL generated with the
"SORTBY" and "Direction" parameters (these two are added to achieve the interactive sorting).
We then append the value of the column header (Field Value in details section)
as "SORTBY" while the value of the "Direction" parameter
toggles between ASC to DESC. We also add the code to sort TAB of property of
Table on which sorting is to be implemented.
Step-by-Step Walkthrough
-
Create data source
-
Provide correct credentials
-
Create Report (as shown below)
-
Add the two parameters DIRECTION and SORTBY. These two parameters are in addition to any existing parameters.
And set their properties as follows:
DIRECTION
Prompt = DIRECTION
DataType = String
Available Value = Non Queried
SORTBY
Prompt = SORTBY
DataType = String
Available Value = Non Queried
-
Select Table -> Properties
-
Select Sorting Tab
Enter expression as
=iif(Parameters!DIRECTION.Value="Asc",Fields(Parameters!SORTBY.Value).Value,0) Direction Ascending
and
=iif(Parameters!DIRECTION.Value="Desc",Fields(Parameters!SORTBY.Value).Value,0) Direction Descending
As shown below
-
Select the Column Header -> Property -> Advanced
-
Select Navigation Tab
-
Select Jump to URL
-
Enter expression as
="http://localhost/reportserver?%2fArticle%2fNorthwind"& "&SORTBY=OrderID&DIRECTION=" & iif(Parameters!DIRECTION.Value="Asc","Desc","Asc")
-
Repeat the previous step for each column.
Please note to change the value of the SORTBY parameter to equal the field name.
Like OrderID for first column in this example.
-
Deploy the report on Server.
Run the report and click on the header of the column
by which you want to sort the report.
Conclusion
This article showed how you can use the Render Report by URL technique
to implement interactive sorting in SQL Server Reporting Services.
|
|
|
|
|
|
|
Other Articles
|
|
|
|
|
Apr 28, 2005 - New Files and Folders in ASP.NET 2.0
|
|
|
With the release of ASP.NET 2.0, Microsoft has greatly increased the power of ASP.NET by introducing a suite of new features and functionalities. As part of this release, ASP.NET 2.0 also comes with a host of new special files and folders that are meant to be used to implement a specific functionality. This article examines these new files and folders in detail and provides examples that demonstrate how to utilize them to create ASP.NET 2.0 applications.
[Read This Article] [Top]
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Dec 15, 2004 - A Sneak Peek at ASP.NET 2.0's Administrative Tools
|
|
|
With ASP.NET 2.0, Microsoft has made great strides in increasing developer productivity and has made implementing previously complex solutions relatively easy. Where this version of ASP.NET really shines, however, is in its new administrative tools that allow developers to spend less time managing the configuration of the servers and software and more time developing great code.
[Read This Article] [Top]
|
|
|
|
Nov 17, 2004 - The ASP.NET 2.0 TreeView Control
|
|
|
Thiru Thangarathinam introduces ASP.NET 2.0's new TreeView control which provides a seamless way to consume and display information from hierarchical data sources. The article discusses this new control in depth and explains how to use this feature rich control in your ASP.NET applications.
[Read This Article] [Top]
|
|
|
Mailing List
Want to receive email when the next article is published? Just Click Here to sign up.
|
|
