Joe Webb

Musing and observations about SQL Server, other technogies, and sometimes just life in general
posts - 85, comments - 857, trackbacks - 0

My Links

News

This blog has moved!

Click here for the new location.

Follow me on Twitter
Add to Technorati Favorites

Search this Blog
 




Archives

Post Categories

About me

Interactive sorting in SQL Server Reporting Services 2005

Business Intelligence Developers Studio (aka BIDS) for Microsoft SQL Server 2005 Reporting Services makes creating engaging and dynamic reports nearly trivial in some respects. You can create drill-down and drill-through reports with just a few mouse clicks.

You can also create reports that provide interactive sorting. Users can click on column headings and have the report automatically sort by that column. This question regularly comes up as I'm leading training sessions and occasionally in the online forums. So, let's walk through a graphical example to demonstrate.

Here's an example of a contact phone list report created in BIDS. It lists contacts from the sample Adventureworks database using a table data region.

InteractiveSort1-2008-07-25

Let's provide a way for our users to sort the list as they wish. In the Layout view, click the table header cell, right click, and choose Properties from the context menu.

InteractiveSort2-2008-07-25

This opens the Textbox Properties window as shown below. Notice the 6th tab is called Interactive Sort. Check the "Add an interactive sort action to this textbox" option. Then click the down arrow in the Sort Expression combobox. Choose the field on which you like to sort when the user clicks this textbox. In our case this this Fields!FirstName.Value expression.

InteractiveSort3-2008-07-25

Repeat this process for each of the columns in the table data region.

Once completed, preview the report. The report is still sorted in the default order. But you'll notice the little arrows beside each column header.

InteractiveSort4-2008-07-25

By clicking on the little arrow beside the column header text, our users can change the sort report. Clicking the same arrow for a second time reverses the sort order.

InteractiveSort5-2008-07-25

And there you have it. With only a few mouse clicks, we've enriched the experience our users have while viewing this report. Of course, this technique only works when the report is rendered in formats that support interactivity. When being viewed as a TIFF, for example, the user will not be able alter its presentation.

Cheers!

Joe

kick it on DotNetKicks.com

Print | posted on Saturday, August 02, 2008 9:14 AM | Filed Under [ Reporting Services ]

Feedback

Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005

RS Folks:

This is also available in 2004, but not as simple as a simple property change.
You will have to create 2 drop downs with the fields you want to sort by and the direction (Ascending / Descending) in the properties section. Then right click the actual table and select properties. Go to the sorting tab and add the following IIF conditions

=IIF(Parameters!Direction.Value = "Ascending", Fields(Parameters!SortBy.Value).Value, 0)

the direction should be Ascending here


And right under this add:

=IIF(Parameters!Direction.Value = "Descending", Fields(Parameters!SortBy.Value).Value, 0)

With the direction being descending.
8/5/2008 9:50 AM | Jon
Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005

Good One.

Can we have dropdowns also in certain columns? If yes, then how we can configure it?

Thanks in advacnce.

Warm regards,
Santosh
8/7/2008 7:47 PM | Santosh
Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005

Can we make it possible to sort on multiple columns?
8/10/2008 11:47 PM | Atul
Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005

Can someone also please describe, how do we SORT on multiple columns?
8/11/2008 11:30 AM | PP
Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005

This doesn't seem to work if you have preselected dates for instance as parameters. ie select between start date and end date then it will not sort in date order.
8/18/2008 5:40 AM | Phil
Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005

Sorting on multiple columns is possible. I'll do a post on it in the next day or two.

Cheers!

Joe
8/20/2008 6:20 AM | Joe Webb
Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005

To sort on multiple columns, press shift and click on multiple sort buttons...
9/30/2008 9:37 AM | Manoj Nair
Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005


Please tell me how to enable or disable databar using soring or interactive sort in datadyanmics reports
11/6/2008 3:12 AM | hari
Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005

Is it also possible to dictate what the default sort action will be. Right now if you click a sortable column, it is sorted in ascending order first and then descending order. Can that be changed such that it is sorted in descending order first?
11/18/2008 12:19 PM | Jeff
Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005

Is it possible to sort the data part based on grouping column? When user the click the data column header, the total displayed for secondary level grouping get sorted first and data displayed within the secondary level should also get sorted either by asc or desc.
12/20/2008 10:01 AM | Ramesh
Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005

Hi..

I want to know how to print report in SRS using sorting from UI..i mean in UI(user interface) i sort first and then i click buttonn print and the report shows exactly as UI..sorting..

I hope you understand..Hope to hear your reply soon

Regards,
Majid
1/7/2009 10:18 PM | majid
Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005

Generally, when you print, the report comes our with the current set of properties. That is, when you expand parts of a drilldown report and then export it to pdf, the exported file comes out looking like what's on the screen.

I haven't tested, but I would expect the same would be true for sorting. Are you experiencing something else?

1/9/2009 5:37 PM | Joe Webb
Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005

I am using this Iif statement to restrict the data not to show a Part in stock on my SSRS report. Here is the statement and it works fine if I doing for one part but If I want restrict more than one Part not to show how to do it. Let me know and here is the script that works with one part.

=Iif((Fields!PRTNO.Value)="256",True,False)
4/17/2009 10:37 AM | moe
Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005

hey vry nice article

thks dr...

7/17/2009 1:23 PM | Smitha
Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005

Is it possible to sort the data part based on grouping column? When user the click the data column header, the total displayed for secondary level grouping get sorted first and data displayed within the secondary level should also get sorted either by asc or desc.
2/15/2010 3:10 AM | AmmatAllah
Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005

i am using sql server reporting services 2005. Can someone please tell me how can i build a report with more than 3 columns?
5/27/2010 1:34 AM | Amrish
Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005

Is it also possible to dictate what the default sort action will be. Right now if you click a sortable column, it is sorted in ascending order first and then descending order. Can that be changed such that it is sorted in descending order first?
7/9/2010 4:48 PM | Fred
Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005

Here's an example of a contact phone list report created in BIDS. It lists contacts from the sample Adventureworks database using a table data region.
10/15/2010 12:42 AM | snow boots for women
Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005

Once completed, preview the report. The report is still sorted in the default order. But you'll notice the little arrows beside each column header.
10/15/2010 12:43 AM | columbia jackets
Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005

Here's an example of a contact phone list report created in BIDS. It lists contacts from the sample Adventureworks database using a table data region.


womens snow boots | columbia sportswear | cheap makeup | cheap north face jackets
10/20/2010 2:17 AM | furry boots
Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005

Nice article buddy
3/17/2011 6:06 AM | Mila
Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005

I have duplicate record on body and I can group the record on body but during the sum of column it give the wrong figure is option to group during the sum
5/30/2011 11:55 PM | Vivek Banafar
Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005

How would you apply the interactive sorting on a group column.
for example you have a report in which you have a groupby column and a groupby row.
How would you implement the interactive sorting to each column from the groupby column?
6/10/2011 9:23 AM | goofyman
Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005

Can this be done using Visual Studio 2008, or only using BIDS?
6/21/2011 8:44 AM | Brian
Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005

I have this expression in a table cell:
=RunningValue(Fields!Dava.Value, Sum, "GroupKonto")
Can I use interactive sort for this column?
4/20/2012 5:40 AM | Biljana
Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005

Great post! Thanks for sharing this...

Cheers!
Zel
6/7/2012 1:10 AM | zel
Gravatar

# re: Interactive sorting in SQL Server Reporting Services 2005

When my report is exported the interactive sorting goes away.Is there anyway to have interactive sorting attributes flow through to excel?
10/16/2012 9:36 AM | Todd
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET