Interactive Sorting for a SQL Server Reporting Services Report

By:   |   Comments (6)   |   Related: > Reporting Services Formatting


Problem

You created a report for your client and the report default sorting is in descending order which was applied at the dataset level. But what if the client wants to see the data in ascending order? Or what if the client wants to be able to switch back and forth as needed? In this tip we look at how to make the sorting option interactive, so after the report has been rendered the user can changed the sort order.

Solution

This tip assumes that you have previous real world work experience building a simple SSRS Report. In this tip I will describe how to add interactive sorting in a Tablix in SQL Server Reporting Services. To demonstrate the solution, I will use the AdventureworksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Service.

SQL Server Reporting Services comes with out of the box functionality known as interactive sorting. This feature allows users to sort the data at the report level in either ascending or descending order. The user can sort the data accordingly without the need to modify the dataset or report.

Step 1: Create New SSRS Report

Let's create a sample report for demo purposes. Please change the report name to Interactive_Sorting. You can refer to the below image.

Adding New Report

Step 2: Create Data Source and Dataset in SSRS

I have already created a data source connection to the AdventureworksDW2008R2 sample database and let's create the below dataset for this sample report.

Main Dataset: This dataset will be used for the report body and it returns 4 data fields. I have applied ascending sort order on the YearlyIncome data field.

SELECT 
FirstName, LastName, Gender, YearlyIncome
FROM  DimCustomer
ORDER BY YearlyIncome ASC

After creating the Main dataset, the Report Data Pane looks like the below image.

Report Data Pane After Adding Dataset

Step 3: Add Tablix in Reporting Services

Let's add a Tablix for data viewing purpose. I will pull all 4 data fields from the Main Dataset into the Tablix. After adding the Tablix, the sample report looks like the below image.

Adding Tablix

Let's preview this sample report, as you can see from the below image our sample report displays all the records in ascending order based on the YearlyIncome data field value.

Report Preview when data is sorted at dataset level

Now a user wants to see YearlyIncome data in descending order. To do this, either the dataset has to be modified with descending sorting on the YearlyIncome data field or you have to change sorting order at the report level on the YearlyIncome data field.

What if a user wants a choice to sort data in either ascending or descending order? To handle this kind of requirement SQL Server Reporting Services comes with out of the box functionality known as interactive sorting. We can provide interactive sorting at the report level on the YearlyIncome field.

Step 4: Adding Interactive Sorting to SSRS

Let's add interactive sorting on the YearlyIncome column. Right click on the YearlyIncome column and select Text Box Properties, you can refer to the below image.

Adding Interactive Sorting YearlyIncome Textbox

Once you click on the Text Box Properties, a new Text Box Properties window will open. In the Text Box Properties window click on Interactive Sorting, you can refer to the below image.

YearlyIncome Textbox Interactive sorting Properties Window

Now we have to enable the sorting option for the YearlyIncome textbox. Check the "Enable interactive sorting on this text box" check box as shown below.

Enable Interactive Sorting Option For YearlyIncome Textbox

Once you enable Interactive Sorting, other options will be enabled. You can apply this interactive sorting on Detail rows or Groups. You have to select this as per your requirement. In our sample report we don't have any groups, so we want to sort base on detail rows.

Select the Detail rows radio button and select the column we want to sort by. In our case we want to sort on YearlyIncome, so I have selected it in the "Sort by:" drop down option.

If you want to apply this sorting to all groups and data regions then you can check that check box. In our case it is not required, so I am not checking it.

Applying interactive sorting on Yearlyincome Textbox

Step 5: Report Preview

We have done all the necessary changes, now let's preview the report.

For the first time when you will preview the report you will find two signs (up and down) next to the YearlyIncome column. These two signs represent the interactive sorting functionality. If we applied any sorting at dataset level then the report will show the sorted data by default, if no sorting is applied then report will show unsorted data.

As you can see from the below image, the report is sorted based on YearlyIncome by default. It is because we sorted the data at the dataset level.

Report Preview With Interactive Feature Default Order

Users can change the sorting at the report level just by clicking on the interactive sorting icon. Once the user clicks on the interactive sort icon, the interactive sort sign will change and the report will show the sorted data. If the interactive sort sign changed to single arrow and points down then it means the report is sorted in descending order. You can refer to the below image.

Report Preview With Interactive Feature Desc Order

If the user clicks on the interactive sort down arrow, the report will be sorted in ascending order.

Report Preview With Interactive Feature Asc Order
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ghanesh Prasad Ghanesh Prasad leads a team in Microsoft Business Intelligence and has over 5 years of experience working with SQL Server.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Tuesday, June 12, 2018 - 6:10:11 PM - Corey Vaughn Back To Top (76202)

 

This was a perfect explanation. Thank you!

Friday, September 11, 2015 - 9:56:51 AM - Kris Back To Top (38650)

I carried all the steps and it didn't work.

Do you think I need to enable anything?

Monday, August 24, 2015 - 11:39:13 PM - Kris Maly Back To Top (38534)

Dear PhyData

Mr Ganesh is explaining the feature.

But one should know when to apply and for what report to be applied.

Say if user asks provide me interactive sorting  on table having millions of recs that is not right.

As a developer we have to convince the customer with Pros and Cons of Sorting.

Please note I am not defending Ganesh but he just explained clearly about this tip and also he is not recommending.

You might have read the tip Ganesh clearly said If Sort is made and the DataSet how it behaves and if it not sorted on DataSet how it differently behaves.

Any how teaching others and helping others should be our motive.

Thanks

Monday, August 24, 2015 - 11:28:09 PM - Kris Maly Back To Top (38533)

Wow this is a cool feature.

I enjoyed reading this article/Tip and recommend others to read and implement.

It's very simple Tip but writing an article like this needs lots of patience and effort.

I tried this tip and works very well.

I wish you shoud publish on doing an interactive sorting on another option i.e. on Groups.

Thanks for educating the community snd sppreciate your volunteership.

Good Luck!

Thanks a lot

 

 

Tuesday, August 18, 2015 - 9:54:51 AM - PhyData DBA Back To Top (38467)

I would not be typing this if removing the Order By in the dataset ADHOC select statement AND THEN adding the interactive sort to the report was mentioned.

You should never sort a Dataset for a SSRS report (or anything else)  in the select statement for the report.
Always sort your data at the report level so it can be sorted as needed at the client.

This has been in online help and best practices information since SSRS was in beta.
It is also in every .NET best practice and instruction about datasets I have ever read.

This is a good way to make a slow report return data even slower and slow down data access on the server also.
Resorting data on the client after having sorted on database server is one of the many new SQL smells I expect MS SQL tips to ask people to stop, not to recommend.

 

Tuesday, August 18, 2015 - 9:32:15 AM - J Back To Top (38466)

Good tip ! Using SSRS for more than a year, I never noticed that option of interactive sorting.

A more complicated sorting requirement had to be addressed for a set of invoices, more specifically the list of available invoices in a multi-select parameter.

The invoice list in the drop-down window of the multi-valued parameter was to be sorted by any of these criteria:

- sort by customer name and invoice date (descending)

- sort by customer name and invoice date (ascending)

- sort by invoice number desc (note that the invoice date even with the time portion does not necessarily corresponds to highest/lowest invoice number)

- sort by invoice number asc

- other combinations ...

This required a first dataset holding the various sorting choices to feed a sorting selection parameter "@ls_OrderBy". The value of the parameter chosen by the user is then used in the sorting of the second dataset providing the list of invoices shown in the multi-select parameter. Make sure that the order of the parameters in the report is (1) "@ls_OrderBy" and (2) the invoice list parameter.

The T-SQL statement for the invoice list of the multi-select parameter was something like:

SELECT InvoiceNumber, InvoiceDate, CustomerName, InvoiceStatus, InvoiceAmount, Currency, isCreditNote, ...

-- here fabricate the three sorting levels

--first sorting level: Customer in ascending alphabetical order or Invoice number in ascending number (InvoiceNumber must be the same type as the customer name) or a constant value 'x' to ignore the first level

CASE
        WHEN @ls_OrderBy = 'Customer and Document Date (most recent date first)' OR @ls_OrderBy = 'Customer and Document Number (highest number first)'
        THEN I.CustomerIdName
        WHEN @ls_OrderBy = 'Document Number (lowest number first)' THEN InvoiceNumber
        ELSE 'x'
END as Sort1,

-- second sorting level:  InvoiceDate descending or InvoiceNumber descending  

CASE
        WHEN @ls_OrderBy = 'Customer and Document Date (most recent date first)' THEN

            CONVERT(char(4), YEAR(InvoiceDate))  +  RIGHT('0'+ CONVERT(varchar(2), MONTH(InvoiceDate)), 2)  + RIGHT('0'+ CONVERT(varchar(2), DAY(InvoiceDate)), 2)

         WHEN @ls_OrderBy = 'Customer and Document Number (highest number first)' THEN InvoiceNumber
        WHEN @ls_OrderBy = 'Document Date (most recent first) and Number' THEN ...

        ELSE 'y'
    END AS Sort2,

 

--level 3 of sorting: if the first or second level was the customer name or invoice date, descending invoice number or a constant value

CASE
        WHEN @ls_OrderBy = 'Document Date (most recent first) and Number'  OR @ls_OrderBy = 'Customer and Document Date (most recent date first)'
        THEN InvoiceNumber
        ELSE 'z'
END AS Sort3,

 

FROM Invoice

ORDER BY Sort1, Sort2 DESC, Sort3 DESC














get free sql tips
agree to terms