Adding Dynamic Order By to a report

This example shows how to add Dynamic Order By to sort report results in a list by the Full Name, Team, or Status columns. When the report is run, the results are sorted by the column and direction based on the parameters selected by the user.

  1. Create a new report using the sample report template.
  2. Add a List to the third block on the report results page and add these query items to the list:
    Namespace > Query Subject > Query Item
    Work > Employee > Full Name
    Work > Team > Team Name
    Work > Employee > Status
  3. Add the OrderUp column, which is used to sort the results in ascending order:
    1. In the Toolbox pane, expand Textual and drag the Query Calculation object into the list.
    2. Specify this information in the Data item Expression dialog box:
      Name
      Specify OrderUp.
      Expression Definition
      Specify this expression: 
       if (?OrderBy? = 'Full Name' and ?Direction? = 'Ascending') then
([Work].[Employee].[Full Name])
else if (?OrderBy? = 'Team' and ?Direction? = 'Ascending') then
([Work].[Team].[Team Name])
else if (?OrderBy? = 'Status' and ?Direction? = 'Ascending') then
([Work].[Employee].[Status])
Else
('NA')
    3. Click OK.
  4. Repeat step 3 to create the OrderDown column, specifying this information in the Data item expression dialog box. The OrderDown column is used to sort the results in descending order.
    Name
    Specify OrderDown.
    Expression Definition
    Specify this expression: 
     if (?OrderBy? = 'Employee' and ?Direction? = 'Descending') then
([Work].[Employee].[Full Name])
else if (?OrderBy? = 'Team' and ?Direction? = 'Descending') then
([Work].[Team].[Team Name])
else if (?OrderBy? = 'Status' and ?Direction? = 'Descending') then
([Work].[Employee].[Status])
Else
('NA')
  5. Click Show properties.
  6. Select the List and double-click the Grouping & sorting property.
  7. Drag OrderUp to the Detail Sort List folder.
    The OrderUp column is added to the Detail Sort List folder with an upward-facing arrow, which indicates the column is sorted in ascending order.
  8. Drag OrderDown to the Detail Sort List folder.
  9. Double-click the OrderDown column in the Detail Sort List folder.
    The arrow is pointing down, which indicates the OrderDown column is sorted in descending order.
  10. Click OK.
  11. Optionally, complete these steps to hide the OrderUp and OrderDown columns from the report view:
    1. Select the heading row of the OrderUp column.
    2. Click Select ancestor in the Properties pane and select List column.
    3. Select No in the Render property.
    4. Repeat steps 11.a to 11.c to hide the OrderDown column.
      The OrderUp and OrderDown columns are no longer displayed in the report results. The columns are still part of the underlying query and are used to sort the report results.
  12. Open a prompt page, and add a Value prompt for the OrderBy parameter:
    1. Click Pages and open a prompt page.
    2. From the Toolbox pane, expand Prompting and drag the Value prompt item to the right pane.
      The Prompt Wizard dialog box is displayed.
    3. Select the Use existing parameter option and select OrderBy in the drop-down box.
    4. Click Next.
    5. Clear the Create new query check box and click Finish.
    6. In the Properties pane, double-click the Static Choices property.
      The Static Choices dialog box is displayed.
    7. Click +.
    8. Specify Full Name in the Use field and FULL NAMES in the Display field.
    9. Click OK.
    10. Repeat steps 12.g to 12.i to add these values:
      Use Display
      Team TEAMS
      Status STATUS
    11. Click OK.
  13. Repeat step 12 to create another Value prompt for the Direction parameter, and add these options to the Static choice property:
    Use Display
    Ascending Ascending
    Descending Descending
  14. Click Run options > Run HTML.
When the report is run, you are prompted to select the sort column and direction. If you choose to sort by TEAMS in Ascending order, the OrderUp column is populated with Team data and sorted in ascending order. Because this column is sorted, the entire list is also sorted based on Team. Every row in the OrderDown column is filled with a placeholder string (NA), so the OrderDown column does not affect the results of the data.