Filter data in a Data View

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

When you create a Data View, you can apply a filter so that the view includes only data that satisfies the criteria that you specify. For example, you may have a data source that includes hundreds or thousands of records. However, you want to view only a subset of those records. You can create a filter so that only that subset of records is included in your Data View.

When you use Office SharePoint Designer 2007 to filter data in a Data View, you limit the records that are actually available in the Data View. You can also add a toolbar to the Data View so that visitors can filter data through the browser. However, the toolbar affects only the presentation of data; the toolbar does not limit the amount of data that is actually available in the Data View. 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 examples, or to learn more about Data Views, see Create a Data View.

What do you want to do?


Add a filter

Northwind Traders, a gourmet food distribution company, keeps their product list in XML format. This product list also includes products that have been discontinued. Your team wants to create a Data View that displays only current products, so that the Data View on your team site is not cluttered with products that Northwind Traders no longer carries. To do this, you add a filter.

When you create a filter, you build one or more expressions that are applied to the data. Data that satisfies these criteria is included in the Data View. There are three basic parts to an expression: the field name (the name of the field on which you want to filter), the operator (the rule that you want to apply to the value, such as equal to, greater than, or less than), and the value (the data that you are looking for).

There are three steps to creating an expression. The first step is to identify the field that determines whether a record appears in the filtered results. In this example, the Discontinued field is the basis for your filter because the data in this field must be checked for the records to be included in or excluded from the results. The second step is to specify the operator that you want to apply. You want only those records where Discontinued equals 0, so you specify Equals as the operator. The data in the field that you are looking for is 0 (Northwind Traders uses 0 to indicate false), so the value is 0.

  1. Open the page that contains the Data View that you want to filter.
  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 Filter.
  2. In the Filter Criteria dialog box, click Click here to add a new clause.
  3. Click the Field Name box, and then click the field that contains the values that you want. To follow the Northwind Traders example, click Discontinued (Number).
  4. Click the Comparison box, and then click the operator that you want. To follow the Northwind Traders example, click Equals.
  5. Click the Value box, and then type or select the criteria that you want. Because Northwind Traders uses 1 to indicate discontinued products and 0 to indicate products that have not been discontinued, type 0.

The sample expression in the Filter Criteria dialog box

The sample expression in the Filter Criteria dialog box
  1. If you want to view the expression and edit it by using XPath, click Advanced.

 Note   All filter criteria that you create in the Filter Criteria dialog box are actually XPath expressions. For more information on advanced filters, see the later section Create an advanced filter.

  1. Click OK.

You now have a Data View that shows only current products and excludes discontinued products.

With Discontinued Equals 0 expression, four of five records appear

Top of Page Top of Page

Add a clause to an existing filter

You can use multiple expressions in a single filter. You do this by adding new clauses that specify additional criteria that the data must satisfy.

For example, in the previous section you filtered the Data View so that it includes only current Northwind Traders products. Now suppose that you want your Data View to include only current products where the units on order equals zero, so that you can decide whether to order additional units.

  1. Open the page that contains the Data View that you want to modify.
  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 Filter.

In the Filter Criteria dialog box, you can see any filter criteria that are currently applied to the Data View.

  1. At the end of an existing clause, click the And/Or box, and then do one of the following:
    • To create a filter where the data must match the criteria in both clauses, click And.
    • To create a filter where the data must match the criteria in only one clause, click Or.

To follow the Northwind Traders example, click And, because you want your Data View to include only products that are not discontinued and where units on order equals zero.

  1. On the next row, click Click here to add a new clause.
  2. Click the Field Name box, and then click the field that contains the values that you want. To follow the Northwind Traders example, click UnitsOnOrder (Number).
  3. Click the Comparison box, and then click the operator that you want. To follow the Northwind Traders example, click Equals.
  4. Click the Value box, and then type or select the criteria that you want. Because you want to see only products that have no units on order, type 0.

A filter composed of two expressions as it appears in the Filter Criteria box

  1. Click OK.

You now have a Data View that shows only current products that have no additional units on order.

Of five sample data records, two now appear

Top of Page Top of Page

Group the clauses in a filter

When your filter contains multiple clauses, you may want to group two or more clauses so that they are applied before another clause. By determining the order in which the clauses are applied, you have precise control over what data appears in your Data View.

For example, in the previous sections, you created a filter with two clauses to see current products that have no additional units on order. This Data View helps you to decide whether to order additional units. To make this Data View even more useful, you want to see current products where either units on order equals zero or units in stock is less than 20. To do this, you add a third clause and group it with the second clause.

  1. Open the page that contains the Data View that you want to filter.
  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 Filter.

In the Filter Criteria dialog box, you can see any filter criteria that are currently applied to the Data View.

  1. At the end of the second clause, click the And/Or box, and then click Or.
  2. In the Filter Criteria dialog box, click Click here to add a new clause.
  3. Click the Field Name box, and then click the field that contains the values that you want. To follow the Northwind Traders example and add a third clause to your filter, click UnitsInStock (Number).
  4. Click the Comparison box, and then click the operator that you want. To follow the Northwind Traders example, click Less Than.
  5. Click the Value box, and then type or select the criteria that you want. To see when inventory is running low, you want to see products where units in stock is less than 20, so type 20 in the box.
  6. Hold down SHIFT while you click the arrows in the leftmost column of the clauses that you want to group, and then click Group. To remove grouping, click Ungroup.

To follow the Northwind Traders example, hold down SHIFT while you click the arrows in the leftmost column of the second and third clauses.

The sample expression in the Filter Criteria dialog box, including a bracket indicating the grouped clauses

The bracket connecting the clauses indicates that those clauses are grouped.
  1. Click OK.

To help you track the products that need to be ordered, you now have a Data View that shows only current products where either units on order equals zero or units in stock is less than 20. This Data View contains four records. Note that if you had not grouped the second and third clauses, the Data View would contain two records, because the clauses would have been evaluated consecutively. By grouping clauses, you determine the order in which they are evaluated.

Of five sample data records, only four appear

Top of Page Top of Page

Modify a clause

You can view and modify any filter that was applied to a Data View by opening the Filter Criteria dialog box and making changes.

  1. Open the page that contains the Data View that you want to filter.
  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 Filter.

In the Filter Criteria dialog box, you can see any filter criteria that are currently applied to the Data View.

  1. In the Filter Criteria dialog box, click an existing clause, and then do any of the following:
    • Click the Field Name box, and then click the field that contains the values that you want.
    • Click the Comparison box, and then click the operator that you want.
    • Click the Value box, and then type or select the criteria that you want.

Top of Page Top of Page

Delete a clause

  1. Open the page that contains the Data View with the filter that you want to modify.
  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 Filter.
  2. Click the the arrow in the leftmost column of the clause that you want to delete, and then press DELETE.

Top of Page Top of Page

Delete a filter

  1. Open the page that contains the Data View with the filter that you want to delete.
  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 Filter.
  2. Hold down SHIFT while you click the the arrow in the leftmost column of each clause, and then press DELETE.

 Note   If you are using an advanced filter, the text Using advanced expression appears in the Filter Criteria dialog box. To delete an advanced filter, click Advanced, and then delete all text in the text box.

Top of Page Top of Page

Create an advanced filter

There may be times when you need to create a filter that uses more complex criteria than you can specify in the Filter Criteria dialog box. For example, suppose that Northwind Traders wants to filter a Data View so that it displays only the products for which the value of the stock in inventory exceeds $1,000.00. In such a case, you can create an XPath expression by using the options in the Advanced Condition dialog box.

In Office SharePoint Designer 2007, you can use XPath to create advanced filters. 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 filters on data.

 Note   XPath filtering is performed on the XML that is the basis for the Data View. 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 filter is applied, so the performance of the filter may be slower than expected.

  1. Open the page that contains the Data View that you want to filter.
  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 Filter.
  2. In the Filter Criteria box, click Advanced.
  3. In the Advanced Condition 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.

In the example, to create the Data View that displays only products with an inventory value greater than $1,000, 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 filter in the Northwind Traders example, double-click UnitsInStock, and then press SPACEBAR.

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

In the example, double-click > (greater than), and then type 1000.

The final expression looks like the following.

the XPath expression to display products where the inventory value is greater than $1000

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

 
 
Applies to:
SharePoint Designer 2007