August 27, 2012

Sorting with Reporting Services

SQL Server Reporting Services provides the following mechanisms for implementing report sorting:

Method #1: Query-level static sort

In Design View, implement a default sort with the ORDER BY clause. It is good practice to add a default sort with an ORDER BY clause to the dataset Query in the Report Data pane. Steps:
  1. navigate to the Report Data pane
  2. click the dataset you are using for the report
  3. click Query
  4. add an ORDER BY clause to the query


Method #2: Table-level static sort

In Design View, define static sort columns. This approach usually results in requests from the end user for multiple reports that use different sort criteria. Steps:
  1. select the report
  2. click the square in the top left corner
  3. click Tablix Properties
  4. click Sorting
  5. click Add
  6. choose a sort column from the dropdown listbox
  7. choose the sort order 



Method #3: column-level interactive sort 

In Design View, define interactive sort columns. Depending on the number of interactive columns defined and the number of report users, this approach may result in performance issues. If you are using this method to implement sorting, it is good practice to add a default sort with an ORDER BY clause to the dataset Query in the Report Data pane. Interactive sorting overrides the default sort order that was implemented using the ORDER BY clause (method #1 above) or inside the report at the Table level (method #2 above). Steps:
  1. right-click the column you want to sort 
  2. click Text Box Properties
  3. click Interactive Sort
  4. click Enable if it is disabled
  5. choose a sort column from the dropdown listbox
  6. In Preview, you will now see an up-down arrow in the column heading that the report user can click to toggle between ascending or descending sort order