Sort and group data in a Data View

With Microsoft Office SharePoint Designer 2007, you can easily sort and group data in a Data View.

When you sort data in a Data View, you modify the order in which the data appears. Sorting is a technique that you can use to rearrange records in a way that makes it easier for you to understand your data. For example, common sort orders include alphabetical or numerical order. You can also perform advanced sorts that are based on an expression.

When you group data in a Data View, data within the Data View is grouped by the criteria that you specify. Sorting and grouping are related, and you cannot group fields unless you first specify a sort order. For example, if you sort a product list alphabetically so that products starting with "A" appear together, that is a basic form of grouping. Each group in a Data View can be expanded or collapsed by using the group header.

When you use Office SharePoint Designer 2007 to sort or group data in a Data View, by default the Data View displays the data sort or group order that you specify. You can also add a toolbar to the Data View so that site visitors can sort or group data through the browser. Find a link to more information about how to add such a toolbar in the See Also section.

To create the sample data and Data View that are used in the following example, or to learn more about Data Views, see Create a Data View.

What do you want to do?


Sort data

By sorting data, you present the information in an order that works best for you, instead of the order in which the data was originally entered. For example, Northwind Traders, a gourmet food distribution company, created a Data View of their product list. You suggest that they sort the list alphabetically by product name to make it easier to find products in the list.

You can also sort on multiple fields by adding more fields to the sort order list. For example, to help make the products in the list easier to find, you can sort the Data View to display the product list by category, and then by product name within each category.

  1. Open the page that contains the Data View that you want to sort.
  2. Right-click the Data View, and then click Show Common Control Tasks on the shortcut menu.

 Tip   You can also click the Data View and then click the arrow Button image in the upper-right corner to show the Common Data View Tasks list. Click the arrow again to hide the list.

  1. In the Common Data View Tasks list, click Sort and Group.
  2. In the Sort and Group dialog box, under Available fields, click the field that you want to sort on, and then click Add.

If no fields appear under Available fields, double-click More Fields, click the field that you want to sort on, and then click OK.

To follow the Northwind Traders example and sort the sample data alphabetically by product name, click ProductName.

  1. Under Sort Properties, do one of the following:
    • To sort a field from lowest to highest (for example, A, B, C, or 1, 2, 3), click Ascending.
    • To sort a field from highest to lowest (for example, Z, Y, X, or 3, 2, 1), click Descending.

 Tip   To change the sort order of a field from ascending to descending, or the reverse, double-click the field in the Sort order list. Notice that the triangle next to the field name indicates whether a field is sorted in ascending or descending order.

  1. Click OK.

Data View showing products in alphabetical order by product name

The Northwind Traders product list is now sorted on one field, ProductName. Next you want to sort the list on multiple fields.

  1. To sort on multiple fields, add more fields to the Sort order list.

For example, to sort the product list by category and then by product name within each category, you need to add the CategoryID field to the sort order. Under Available fields, click the CategoryID field, and then click Add.

CategoryID and ProductName now both appear in the Sort Order list.

  1. Click OK.
  2. You can also change the order in which the fields are sorted. To change the order of the fields, click a field in the Sort order list, and then click Move Up or Move Down.

For example, you just sorted the Northwind Traders product list first by ProductName and then by CategoryID. To sort the product list first by CategoryID and then by ProductName, in the Sort Order list, click CategoryID, and then click Move Up.

Sort and Group dialog box showing CategoryID field first, followed by ProductName field

The result is a list that is sorted by CategoryID and then sorted by ProductName.

Data View showing products sorted first by category and then by product name

Top of Page Top of Page

Create an advanced sort order

There may be times when you need to create a sort order that uses more complex criteria than you can specify in the Sort and Group dialog box. For example, suppose that Northwind Traders wants to sort a Data View so that it displays products by the total value of the product that you currently have in stock. In such a case, you can sort on an XPath expression that you create by using the options in the Advanced Sort dialog box.

In Office SharePoint Designer 2007 , you can use XPath to create advanced sort orders. XPath is a language for locating and processing information in an XML document. The XPath Expression Builder provides IntelliSense for XPath, making it possible for both novice and advanced users to create XPath expressions that perform complex sort orders on data.

 Note   XPath sorting is performed on the XML that is the basis for the data. If you perform an advanced XPath query on an SQL data source such as a Microsoft SharePoint list or library or an SQL database, the data is first rendered as XML before the XPath sort order is applied, so the performance of the sort order may be slower than expected.

  1. In the Sort and Group dialog box, under Sort Properties, click Edit Sort Expression.

 Tip   You can also open the XPath Expression Builder by double-clicking Add Sort Expression under Available Fields in the Sort and Group dialog box.

In the Advanced Sort dialog box, under Select a field to insert, double-click the field that you want to insert in the expression. To insert the full path of the field, hold down CTRL while you double-click the field.

For example, to sort by the total value of the product in stock, double-click UnitsInStock. UnitsInStock appears in the Edit the XPath expression box.

  1. In the Edit the XPath expression box, position the insertion point immediately after the field name, and then press SPACEBAR.

The IntelliSense list of XPath operators appears. IntelliSense for XPath helps you by providing a list of available fields or functions that are valid in the context of the expression.

  1. In the IntelliSense list of operators, double-click the operator that you want.

In the example, double-click the asterisk (*). Now an IntelliSense list of available fields appears.

  1. In the list of available fields, double-click the field that you want.

To create the advanced sort order in the Northwind Traders example, double-click UnitPrice. The final expression looks like the following.

XPath expression for calculated columns as it appears in Edit XPath Expression box

At the bottom of the XPath Expression Builder, you can see a preview of the results of the filter as you build the expression in the Edit the XPath expression box.

Top of Page Top of Page

Remove a sort order

  1. Open the page that contains the Data View from which you want to remove the sort order.
  2. Right-click the Data View, and then click Show Common Control Tasks on the shortcut menu.

 Tip   You can also click the Data View and then click the arrow Button image in the upper-right corner to show the Common Data View Tasks list. Click the arrow again to hide the list.

  1. In the Common Data View Tasks list, click Sort and Group.
  2. In the Sort and Group dialog box, under Sort order, click the field that you want to remove from the sort order, and then click Remove.

To remove the entire sort from the Data View, remove all fields from the sort order.

Top of Page Top of Page

Group data

To group data, you must first create a sort order. See the earlier section Sort data.

When you group data in a Data View, for each field in the sort order, you can choose to display a header or footer for the group and choose whether the group is expanded or collapsed by default. You can also choose to keep your groups together when paging through the Data View and show the column names or totals for each group.

Data is grouped by using the first field in the sort order. For example, in the first section of this article, you sorted a Data View of Northwind Traders products by CategoryID and then by ProductName. Because the first field in the sort order is CategoryID, when the data is grouped, the data is grouped by Category ID.

To make the Data View even easier to browse, now you want to group the products by category so that each category can be expanded or collapsed.

  1. Open the page that contains the Data View that you want to group.
  2. Right-click the Data View, and then click Show Common Control Tasks on the shortcut menu.

 Tip   You can also click the Data View and then click the arrow Button image in the upper-right corner to show the Common Data View Tasks list. Click the arrow again to hide the list.

  1. In the Common Data View Tasks list, click Sort and Group.

If you earlier created the sort order for the Northwind Traders example, under Sort order, you should see the CategoryID field followed by the ProductName field. If the Data View has no sort order, you must first create one to make available the options under Group Properties.

  1. In the Sort and Group dialog box, in the Sort order list, click the field that you want to group by. To follow the Northwind Traders example, click CategoryID.

 Note   Each field in the sort order can have individual group properties applied to it. Make sure that you select the field that you want before you apply the group properties.

  1. Under Group Properties, to show a header at the beginning of each group, select the Show group header check box, and then do one of the following:
    • To have groups in the Data View appear as expanded by default, click Expand group by default. To follow the Northwind Traders example, choose to show a group header, and then click this option.
    • To have groups in the Data View appear as collapsed by default, click Collapse group by default.
  2. To show a footer at the end of each group that displays how many records are in that group, select the Show group footer check box.

After you select either the Show group header or Show group footer check box, the Advanced Grouping button becomes available.

  1. Click Advanced Grouping, and then do any of the following:
    • To hide the individual records in the list, select the Always hide group details check box.
    • To show all items in a group on one page, even if the total exceeds the specified number of items to display in a list, select the Maintain groups when paging check box. To follow the Northwind Traders example, click this option.

 Note   You can view your paging options on the Paging tab of the Data View Properties dialog box. In the Common Data View Tasks list, click Data View Properties.

  • To insert a row in each group that displays the column names, select the Show column names per group check box.
  • To insert a row in each group that displays the total number of records within each group, select the Show column totals per group check box.

Data View sorted by CategoryID and ProductName, and then grouped by CategoryID

In the Northwind Traders example, you now have a Data View that is sorted by CategoryID and ProductName, and then grouped by CategoryID. Each group has a header, and each group will always appear on the same page in the Data View because you chose the option to Maintain groups when paging.

Top of Page Top of Page

Remove a group order

  1. Open the page that contains the Data View that you want to remove the group order from.
  2. Right-click the Data View, and then click Show Common Control Tasks on the shortcut menu.

 Tip   You can also click the Data View and then click the arrow Button image in the upper-right corner to show the Common Data View Tasks list. Click the arrow again to hide the list.

  1. In the Sort and Group dialog box, under Sort order, click the field from which you want to remove a group order.
  2. Under Group Properties, clear the Show group header and Show group footer check boxes.

 Note   Each field in a sort order can have its own group properties. To remove all group orders from a Data View, check each field in the sort order.

Top of Page Top of Page

 
 
Applies to:
SharePoint Designer 2007