Lesson 3: Changing Initial Sort and Interactive Sort Using Parameters

  • Article

New: 17 July 2006

You can define reports that give report readers the ability to interactively change the sort order for one or more columns of report data in a data region or group. You can provide the ability to change sort order using an interactive sort or parameterized sort.

With an interactive sort, you set the UserSort properties of a column or group header text box. When the report renders, a sort control button appears on the column heading, and the report reader can click the button to sort the data. Data is initially unsorted. The first click sorts the data in ascending order. Successive clicks toggle the sort order between ascending and descending.

With a parameterized sort, you create a parameter to set sort order and include an expression with that parameter in the sort expression for a dataset, data region, or group.

In this lesson, you will add both types of sorts. You will add a parameter called InitialSort and set a sort expression on the table column TotalDue to initially sort the table groups by the aggregated total amount. You will create a SortBy parameter that allows the report reader to choose whether to sort by store name or the number of sales orders per store in the Store column of the table. You will add an interactive sort button to the Store column and set its sort expression to depend on the SortBy value.

To open the Advanced Parameters Tutorial report server project

  1. In SQL Server Business Intelligence Development Studio, open the Advanced Parameters Tutorial report server project if it's not already open.

  2. In Solution Explorer, double-click on the Resellers Worldwide report. The report opens in Layout view.

In the next procedure, you change the table group header to show both the store name and the number of sales per store. You will create a report parameter to allow a user to choose how they want to sort the table group: by store name or by number of sales per store.

To add the group count value to the group header

  1. Select the Store text box in the first table group header row.

  2. Replace the expression =Fields!Store.Value with the following:

    =Fields!Store.Value & vbCrLf & vbTab & "(" & 
Count(Fields!SalesOrderNumber.Value, "table1_Store") & ")"

  3. (Optional) Click Preview, and see the store name and the number of sales for each store on a separate line in the first row of the group header.

In the next procedure, you will create a report parameter to allow the user to select which way to sort the table group: by store name or by number of sales.

To add a new report parameter

  1. From the Report menu, choose Report Parameters. The Report Parameters dialog box opens.

  2. Click Add. A new parameter with default values is created.

  3. In the Properties section, in the Name text box, type UserSortBy. Verify that the data type is String.

  4. In Prompt, type How do you want to sort the table group?.

  5. Clear the Allow blank option.

  6. In the Available values section, select Non-queried. Use the following table to enter values.

    Label Value

    Name of store

    Name

    Number of sales per store

    Number

  7. In the Default values section, select Non-queried. In the text box, type Name.

  8. Click OK.

In the next procedure, you will add a UserSort (also known as an interactive sort) control on the group header text box that shows the store name and number of sales. You will set the expression for the control to use the value of the UserSortBy report parameter. When you preview the report, a sort button appears on that column. You can click the sort button to toggle the sort between ascending and descending values, for the field defined by this parameter.

To add an interactive sort button on a group

  1. In the table header row, right-click the first text box named textbox2 and choose Properties. The contents of this text box is the string "Store". The Textbox Properties dialog box opens.

  2. Click the Interactive Sort tab.

    Setting the properties on the Interactive Sort tab is the same as setting properties for UserSort on a text box.

  3. Select the Add an interactive sort to this textbox option.

  4. Click the Expression (Fx) button. The Edit Expression dialog box opens.

  5. Paste the following expression into the expression pane:

    =IIF(Parameters!UserSortBy.Value="Name",Fields!Store.Value,
Count(Fields!SalesOrderNumber.Value, "table1_Store"))

    If you paste this expression directly into the textbox, you will only get the first line of the expression.

  6. Click OK.

  7. In the Data region or grouping to sort section, select Choose data region or grouping. From the drop-down list, choose table1. This is the table data region.

  8. In the Evaluate sort expression in this scope section, select Choose data region or grouping. From the drop-down list, choose table1_Store. This is the store grouping within the table.

    If the UserSortBy parameter is set to Name, this expression evaluates to Fields!Store.Value and the table groups are sorted as they were in the previous lesson, alphabetically by name of store. If the UserSortBy parameter is not Name, the table groups will be sorted by the number of sales in the group in Ascending order.

  9. Click Preview.

    The Store column in the table now has an interactive sort button. The initial sort order is the default value for the table group sort. When you first click the interactive sort button, the column is sorted according to the UserSortBy parameter value. Subsequent clicks on the interactive sort button toggle the sort order between Ascending and Descending.

  10. From the drop-down list, change the parameter value for UserSortBy to Number of sales per store. Click View Report.

    No change in the sort column happens until you reprocess the report so that the parameter expression is reevaluated for this new parameter setting. The column is now sorted by number of sales instead of store name.

In the next procedure, you will add provide a parameter to allow the user to choose how to sort the detail rows of the table.

To add a new report parameter for parameterized sorting

  1. From the Report menu, choose Report Parameters. The Report Parameters dialog box opens.

  2. Click Add. A new parameter with default values is created.

  3. In the Properties section, in the Name text box with InitialSort. Verify that the data type is String.

  4. In Prompt, type How do you want to sort the table detail rows?.

  5. Clear the Allow blank option.

  6. In the Available values section, select Non-queried. Use the following table to enter values.

    Label Value

    Total due

    TotalDue

    Sales order date

    OrderDate

  7. In the Default values section, select Non-queried. In the text box, type TotalDue.

  8. Click OK.

In the next procedure, you will define a sort expression that uses the InitialSort report parameter so the table detail rows will be sorted based on the value you choose.

To add a sort expression to the table detail row

  1. Select the table. Right-click the table border, and select Properties. The Table Properties dialog box opens.

  2. Click the Sorting tab.

  3. From the Expression text box drop-down list, select <Expression>. The Edit Expression dialog box opens.

  4. Replace the equal sign (=) with the following:

    =IIF(InStr(Parameters!InitialSort.Value,"TotalDue")>0,Fields!TotalDue.Value,Fields!OrderDate.Value)

    This tests the parameter for the string value "TotalDue". If found, the TotalDue field is used for the sort order; if not found, the OrderDate field is used. The sort order direction, Ascending or Descending, is not expression-based, so the value you set on the Sorting tab applies to the field specified in the evaluated expression.

  5. Click OK.

  6. (Optional) Change the parameter value for InitialSort, and click View Report. Expand the node for store Efficient Cycling. Verify that the detail rows are sorted according to the InitialSort setting.

Next Steps

You have successfully added two parameters to control whether the interactive sort button on the Store column sorts by store name or the number of sales and whether the detail rows of a table are sorted by order date or total due amount. In the next lesson, you will learn to control the initial drilldown state of this report with a hidden Boolean parameter. See Lesson 4: Adding a Boolean Parameter to Control the Initial Drilldown State.

See Also

Other Resources

Working with Parameters in Reporting Services
Sorting Data in a Report

Help and Information

Getting SQL Server 2005 Assistance