Thursday, November 8, 2012

SSRS- How to Implement Interactive Sort in Report



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---------------------------------------------------

4 comments: