Creating Custom Filters

 <  Day Day Up  >  

Before you read this section, be sure to read the section "Exploring the Standard Filters" in Chapter 19, "Using the Standard Views, Tables, Filters, and Groups." This section builds on the information in that section by discussing how to create your own customized filters.

A good way to begin creating your own filters is to examine the definitions of the standard filters. To look at a filter definition, choose Project, Filtered For and select More Filters from the list that appears. Select a filter from the list (except the All Tasks filter or the All Resources filter) and click the Edit button. The Filter Definition dialog box appears. Figure 21.24 shows the Filter Definition box for the In Progress Tasks filter.

Figure 21.24. The In Progress filter definition sets criteria to display tasks that are already underway.

graphics/21fig24.gif

The In Progress Tasks filter applies two tests. The first test examines the Actual Start field to ensure that the value is not equal to NA (that is, a date has been entered, which indicates that the task has been started), and the second test examines the Actual Finish field to see whether the value is equal to NA (that is, the task has not finished). The logical operator And has been entered in the And/Or field, meaning that both the first and the second conditions must be met in order for a task to be selected.

Filters compare database fields to values you enter in the filter to values supplied by user input when the filter is applied or to values in other database fields. When multiple tests are combined with an And condition, all tests must be true; if multiple tests are combined with an Or condition, only one test must be true.

To define a filter, choose More Filters from the Project, Filtered For menu. If you want to create a new filter that is unlike any filter already defined, click the New button. Otherwise, select an existing filter name from the Filters entry list if you want to edit or copy an existing filter. If you edit an existing filter, the original definition is lost; start by creating a copy with which to experiment. Whether you click New, Edit, or Copy, the Filter Definition dialog box is displayed.

The following sections describe how to develop an overbudget filter that displays all tasks that are overbudget by at least $1,000.

graphics/tip_icon.jpg

A useful custom filter to create is one that will display only the detail tasks of the project, which are ones that are to be linked together and assigned to a resource. This filter actually removes from the view all the summary tasks and milestone tasks within the project. The custom filter is set for Summary Tasks=No and Milestones=No. This leaves only detail tasks.


graphics/new_icon.jpg

When creating filters in Microsoft Project, in the Values field, you can choose values from a pick list.

Naming a Filter

You provide a name for the filter by typing a name in the Name field of the Filter Definition dialog box. If the filter name is to appear in the Filtered For menu, use an ampersand ( & ) before the letter that will be the hotkey. The check box labeled Show in Menu at the top of the dialog box must be selected in order for the filter name to appear in the menu and in the filter drop-down list on the Formatting toolbar.

To create an overbudget filter, enter the name as Cost O&verbudget by 1000 (with v as the hotkey), and mark the Show in Menu box so that the filter is placed on the Filtered For menu (see Figure 21.25).

Figure 21.25. This is the completed Cost Overbudget by 1000 custom filter definition.

graphics/21fig25.gif

Defining Filter Criteria

For each test to be imposed on the database, you must fill in a row of the Filter area in the Filter Definition dialog box. Each row must identify a field name, the nature of the test to be conducted in the field, and the value(s) to be looked for in the field. If multiple tests are to be imposed as part of the filter, the And/Or column must indicate the relationship of the criterion rows.

Selecting a Field Name

To select a field on which to filter, you need to type the field name or select a field name from the drop-down list. In the example shown in Figure 21.25, the field name is Cost Variance.

Selecting a Test

To select a test to use for the filter, you need to select the cell in the Test column and use the drop-down list to view the tests you can select. Then select the appropriate test or type the test phrase. In the example shown in Figure 21.25, the test is to be greater than .

Table 21.2 describes the items that can appear in the Test entry list.

Table 21.2. Filter Test Options

Test

Meaning

Equals

The field values must match the test value(s) exactly.

Does not equal

The field value must differ from the Value(s) column entry.

Is greater than

The field value must be greater than the Value(s) column entry.

Is greater than or equal to

The field value must be equal to or greater than the Value(s) column entry.

Is less than

The field value must be less than the Value(s) column entry.

Is less than or equal to

The field value must be equal to or less than the Value(s) column entry.

Is within

The field value must lie on or between the range of the Value(s) column entries. Two values are required.

Is not within

The field value must lie outside the range of the Value(s) column entries. Two values are required.

Contains

The field value must contain the string in the Value(s) column.

Does not contain

The field value must not contain the string that is in the Value(s) column.

Contains exactly

The field value must contain the exact string that appears in the Value(s) column.

Entering the Value(s) Column Entries

To enter the test value for a filter, you first select the cell in the Value(s) column. You have three options for specifying test values: Type a value for the test, include a prompt for interactive filters, or specify another database field name for calculated filters. The drop-down list for this column is used for calculated filters and contains the names of the fields, with each field name automatically enclosed in square brackets, as required by this type of calculated filter. In Figure 21.25, the value 1000 was typed in (numeric formatting is allowed but not required).

Completing the Filter Definition

To edit a filter definition, you use the Insert Row button on the Filter Definition dialog box to insert a blank row before the criterion row you have selected. You use the Delete Row button to remove a criterion row from the definition.

If the filter is to appear in the Filtered For menu, be sure that the Show in Menu check box is selected. Select the Show Related Summary Rows check box if you want the selected task's associated summary task(s)to also be displayed.

Click the OK button to complete the definition and return to the More Filters dialog box. Click the Apply button or the Highlight button to apply the filter immediately, or click Close to save the filter definition but not apply the filter at this time.

Using More Filter Criterion Tests

Defining tests requires some knowledge of the database fields, field types, and acceptable data values. This section illustrates various types of filter criteria. These samples should help you design almost any kind of filter.

Testing for Logical Values

Many of the fields in the Project database contain only the logical values Yes and No. For example, the Milestone field contains Yes for Milestone tasks and No for all other tasks. The standard filter for Milestone tasks looks for the value Yes in the appropriate field (see Figure 21.26).

Figure 21.26. The Milestone task filter searches for the value Yes in the Project database Milestone field.

graphics/21fig26.gif

Using the Within and Not Within Tests

You use the Is Within test to look for values that lie within and include the upper and lower values in the Value(s) column. The Is Not Within test identifies values that fall outside a range of values. You enter the range of values being used in the test into the Value(s) column, with a comma separating the lower and upper values.

Using the Contains Test

Some text fields (most notably Resource Names, Predecessors, and Successors) can contain lists of entries separated by commas. The Resource Names field contains the list of all the resources assigned to a task, and the Predecessors field contains a list of all the predecessors to the task. The Resource Names and Predecessors fields are really text fields. The Contains test examines the text to see whether a string of characters that is entered in the Value(s) column is contained within the field contents. The Contains test is useful when you want to locate all the tasks whose names include a specified string of characters . Figure 21.27 shows a filter that looks for the tasks whose names not only include the word design but other words as well, either before or after sales. Note that the match is not case sensitive.

Figure 21.27. The Contains test is useful for character searches in filters.

graphics/21fig27.gif

The Contains Exactly test varies slightly, but significantly, from the Contains test. Contains Exactly is for use in fields with comma-separated lists and treats each item in the list as a separate entity. Thus, a Group filter that uses Contains to search for the value "writer" would select all four resources listed in Table 21.3. But one that uses the Contains Exactly test and searches for "writer" would select all but writer extraordinaire.

Table 21.3. Contains Test Groups and Resources

Resource

Group

Robbie

writer

Robbie Smith

writer, instructor

Robbie Jane Smith

writer extraordinaire

Roberta Smith

writer, instructor

Using Wildcards in a Value(s) String

You can search text field entries by using wildcard characters in the search string. You must use only the Equals or Not Equals test for strings that include wildcards. The wildcard characters in Microsoft Project are similar to wildcard characters used in DOS ”the asterisk (*) and the question mark (?).

A wildcard can match any character that falls in the same place as the wildcard in the search comparisons. Therefore, the test string ab ? d is matched by any single character in the third position, as long as the a, b , and d are in the right places. The asterisk represents any number of missing characters or no characters, whereas the question mark represents just one character. Here are some examples:

Test String with Wildcard

Possible Matches

f?d

f a d, f b d, f c d, f d d, f e d, f 2 d

f??d

f in d, f or d, f oo d, f 22 d

f*d

fd, f a d, f ee d, f ormatte d

f*

f, f 1 , f 122 , f ind this text

12-?06

12- A 06, 12- 1 06, 12- X 06

12-*06

12- A 06, 12-06, 12- abc0 06

If you want to search for a wildcard character ( * or ? ), you have to use the caret ( ^ ) symbol before it. For example, searching for *^?* would find all the records that contain a question mark somewhere in the text field. And searching for *^^* would find entries containing a caret symbol. Unlike in Excel, in Project, searching with wildcards for a field value that contains a string somewhere in the field requires a leading and ending asterisk. If you left off the leading and ending asterisks in the previous example, and searched for ^? , Project would only display records that had a single character in them (a question mark).

Using Interactive Filters

An interactive filter is a versatile filter that searches for different values in a field from one time to the next . When an interactive filter is applied, Project prompts the user for input, and uses that input as the test value(s). This type of filter can be included on the Filtered for cascading menu and is automatically included on the filter drop-down list in the Formatting toolbar.

NOTE

Each supplied interactive filter has an ellipsis ( ) after its name in the Filter drop-down list on the Formatting toolbar.


You create an interactive filter by typing a message, enclosed in double-quotes and followed by a question mark, in the Value(s) column of the Filter Definition dialog box. When the filter is applied, the message is displayed in a dialog box as a prompt for the user, and the question mark causes Project to wait for the user to fill a blank that follows the message. For example, the message "What words are you looking for in task names?" is a suitable prompt. The entry in the Value(s) column of the filter definition would look like the entry in Figure 21.28.

Figure 21.28. An interactive filter prompts the user for input each time it is applied.

graphics/21fig28.gif

When an interactive filter is run, one or more dialog boxes are presented for user input. In Figure 21.29, the Is Within test is being used, so two test values are required. The input area for both the beginning and ending filter dates are contained in a single dialog box. Figure 21.30 shows the dialog box that appears when the filter is applied.

Figure 21.29. An interactive filter can prompt for more than one input value.

graphics/21fig29.gif

Figure 21.30. Two separate data entry areas can be included in a single dialog box.

graphics/21fig30.gif

Creating Calculated Filters

A calculated filter compares the value in one field of a task or resource with the value in another field for the same task or resource. For example, in our Overbudget filter, a task that is overbudget has a Baseline Cost field value that is less than the value in the Cost field. To filter overbudget tasks, the criterion needs to compare the Cost field with the Baseline Cost field (see Figure 21.31).

Figure 21.31. Calculated filters compare values in the Project database fields.

graphics/21fig31.jpg

Creating Multiple Criteria Filters

If more than one test must be used to create a filter, each test is placed on its own row of the filter definition table. The first column (And/Or) is used to designate how each row is to be used with the results of tests above it. If it is necessary that the tests on both rows be satisfied in order to satisfy the filter, the operator And is placed in the And/Or column.

The Cost Overbudget filter shown in Figure 21.31 includes two tests. To be included in the filter results, a task's current cost must be more than the baseline cost for the task. But the filter also requires that the baseline cost must be greater than 0; this task must have a budget amount to be used for comparison.

If, however, passing either of the tests is sufficient to satisfy the filter, the operator Or is placed in the And/Or column. If the Or operator is placed in this column in Figure 21.31, all tasks with either a cost exceeding the planned amount or a positive baseline cost would be included.

If more than two rows are used to define a filter, the tests are evaluated from the top down. Therefore, the first two rows are evaluated by using the operator on the second first row, and then the third row test is added by using the operator on the third row, and so on ”until all rows have been considered . For example, if And were replaced with Or in Figure 21.31, the filter would locate all tasks costing more than planned and then add to the results any tasks with a planned cost of zero.

You can group multiple criteria together to create more complex filters. For example, the Late/Overbudget Tasks Assigned To filter (shown in Figure 21.32) looks for tasks that are assigned to a resource (whose name is prompted for) and whose baseline has been set, as well as a finish that is later than planned or a cost that is greater than planned. The first two rows must both be met, and then either of the last two rows must be met.

Figure 21.32. Entering a condition on a blank row creates separate groups of criteria.

graphics/21fig32.gif

To create a grouped series of criteria, you select an And or Or on a blank row between the two groups. Then you move to the next row, without entering any other criteria on the And/Or row. Notice the shaded row in Figure 21.32.

 <  Day Day Up  >  


Special Edition Using Microsoft Office Project 2003
Special Edition Using Microsoft Office Project 2003
ISBN: 0789730723
EAN: 2147483647
Year: 2004
Pages: 283
Authors: Tim Pyron

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net