Interactive Sorting in SSRS

1. First of all go to visual studio 2010 and open your sql report project. Now in your Solution Explorer, Right click on Reports Folder and Add new Report.

2. A Report Wizard window pos-up.

Then click on Next Button.

3. Then select shared data source which we created earlier. If shared datasource not available than create it as describe in following link :

http://bhushan.extreme-advice.com/create-shared-data-source-in-ssrs

Then click on Next Button.

4. Now enter your query in Query String otherwise you can also build your query with Query Builder:

In this report we use following query :

?

1 2 3 4 5

SELECT      FirstName +' ' + LastName AS Name, PhoneNumber FROM        Person.Person INNER JOIN  Person.PersonPhone             ON Person.Person.BusinessEntityID = Person.PersonPhone.BusinessEntityID WHERE       PhoneNumberTypeID = 1

Then click on Next button.

5. Now select Tabular Report from Report Type. After that click on next button.Then Add Name and PhoneNumber fields in Details Section.

6. Then click on Next Button. After that select table style whatever you want.In next screen you will see summary of report.

7. Then click on Finish Button.So now your Report Design will look like below:

8. Now we are going to add feature of interactive sorting on both columns.For sorting right click on the Textbox of Name and select Textbox Properties from the context menu.

9. A Textbox Properties window pop-up.In that select Interactive Sorting from Left Pane.

10. After that checked option of Enable interactive sorting on this text box.Then select [Name]  from the dropdown list of Sort by.

11. Then repeat 8,9 and 10 steps for phonenumber also.For sorting on phonenumber select [PhoneNumber] from the drop-down list of Sort by.

After that click on Preview Button of Report.

12. Now click on up down arrow which is on the right side of column.So your result will look like below:

13. Now again click on Up arrow of the column.so now your result will look like below:

Congratulations! Interactive Sorting in SSRS Report is completed.

1.     Background

The aim of the article is to describe a way of implementing Interactive sort in a report using SQL Server Reporting Services. Interactive sort provides ability to allow the user to sort the data in the report based on their requirement.

2.     What is Interactive Sort?

• Interactive sort is a feature in SSRS by which we can sort the data in ascending and descending order for rows in a table.
• For Matrix, We can sort the data for rows as well as columns.
• Once we implement interactive sorting in the report, a sort button adds in the column header.
• By clicking the Interactive sort button, we can sort our data in ascending and descending order.
• We can add an interactive sort button to any text box, not just column headers.
• We can implement sort for a specific group also in the report.
• We can also combine fields into a single group expression, and then sort by multiple fields.

3.     Steps to implement Interactive Sort

                 Below is the sample report which we will add Interactive sort for details row as well as for grouped rows :

               

           I. Let’s implement the Interactive sorting for Fiscal Year column, For adding the Interactive sort, Follow the below steps:

• Right click on the Fiscal Year column header text box and select Text Box Properties.
• Click on Interactive sort and select “Enable Interactive Sorting on this text box”
• Choose Detail rows
• Select Sort by FiscalYear

 

    Now click Ok.

                             II. Preview the report:

                             III.      Click on the sorting button as to the result (ascending order):

 

                    (Descending Order)

                         IV.  Now we will see how to sort the data at group level. So assume we have created a group for SalesRegion. After creating Sales Region group, Report will be looks like below one:

                     V. Now we will create interactive sort for Sales Region group. Follow the below steps to create sorting at group level :

• Right click on the Sales Region Text box and Select Text Box Properties:
• Click on Interactive sort and select “Enable Interactive Sorting on this text box”
• Select Group as SalesRegion and Sort by SalesRegion.

Click Ok.

                             VI.  Preview the main report:

Now we can sort the data based on Sales Region Group Click on the sorting button of Sales Region column header (ascending order):

               Descending Order:

                        VII. Now If we want to sort the data for Fiscal Year. Report will sort the data for Fiscal Year within a Group (ascending order):

 

                           Descending Order

Once I click on sort button on Fiscal year column header. It sorts the data for Fiscal Year and Sales Amount only without affecting SalesRegion Group:

4.     Conclusion

By using the above steps, We can add Interactive sort in the Report.

----------------------------------------------------End of Document---------------------------------------------------