Joe Webb Blog

Musing and observations about SQL Server, other technogies, and sometimes just life in general

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

Legacy Comments


Jon
2008-08-05
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.

Santosh
2008-08-07
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

Atul
2008-08-10
re: Interactive sorting in SQL Server Reporting Services 2005
Can we make it possible to sort on multiple columns?

PP
2008-08-11
re: Interactive sorting in SQL Server Reporting Services 2005
Can someone also please describe, how do we SORT on multiple columns?

Phil
2008-08-18
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.

Joe Webb
2008-08-20
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

Manoj Nair
2008-09-30
re: Interactive sorting in SQL Server Reporting Services 2005
To sort on multiple columns, press shift and click on multiple sort buttons...

hari
2008-11-06
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

Jeff
2008-11-18
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?

Ramesh
2008-12-20
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.

majid
2009-01-07
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

Joe Webb
2009-01-09
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?


moe
2009-04-17
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)

Smitha
2009-07-17
re: Interactive sorting in SQL Server Reporting Services 2005
hey vry nice article

thks dr...


AmmatAllah
2010-02-15
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.

Amrish
2010-05-27
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?

Fred
2010-07-09
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?

Mila
2011-03-17
re: Interactive sorting in SQL Server Reporting Services 2005
Nice article buddy

Vivek Banafar
2011-05-30
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

goofyman
2011-06-10
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?

Brian
2011-06-21
re: Interactive sorting in SQL Server Reporting Services 2005
Can this be done using Visual Studio 2008, or only using BIDS?

Biljana
2012-04-20
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?

zel
2012-06-07
re: Interactive sorting in SQL Server Reporting Services 2005
Great post! Thanks for sharing this...

Cheers!
Zel

Todd
2012-10-16
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?