Last month we had a look at customizing Tables. However, there will come a time when the data being displayed in the table is in too much detail, whether it be for your own use in developing a project or problem solving, or simply wanting to communicate a more limited amount of data up or down the hierarchy. Project allows us to do this through the use of Filters.
In Microsoft Project: 8—Printing Reports and Views I introduced the use of the built-in filters provided by Project, and I hope you have tried out some of them already. The time will come, though, when none of the filters gives you precisely what you want. Not surprisingly, Project lets you create new filters or edit an existing filter, much the same as we did last month for customizing tables.
So, this month we’ll look into ways of customizing Filters.
Again, I always recommend that when you need a filter, it’s best to make a copy or create a new one rather than change an existing filter, as this will leave the built-in filter alone. As the process of copying or creating a new filter is similar, I would suggest you copy the nearest looking filter, rename it and then make the changes. Let’s have a go.
Setting
As usual, we need something to work with, so once again download my Aircraft A file that I introduced in Microsoft Project: 17 – Multiple Projects . I have changed the file a little since then, so download the file by clicking here: http://www.mousetrax.com/pub/AircraftA_April.zip .
Using a Filter
Filters are accessed through Project/Filtered for: All Tasks/More Filters…
This will open up the More Filters dialog. Notice the similarity to the More Tables dialog that we saw last month with the selection of Task or Resource filters, the list of built-in filters, the buttons on the right, and the Apply and Cancel buttons. Try selecting the Critical filter and Apply.
Note the discontinuity of the ID numbers and that all the task bars are red showing that they’re critical.
That’s how the filters work; they hide any tasks that do not meet the criteria. Try applying the Using Resource… filter. As there is more than one resource assigned, we need to select the one we want. This done by Project offering the Using Resource dialog, from which we can select, say, Airframe, then OK.
We should be looking at a list of all the tasks that have the Airframe resource assigned.
Other filters are accessed in the same way – try a few if you haven’t done so before (remember we select the All Tasks filter to remove any applied filter).
Creating a Filter
Now, supposing we wanted to see all the tasks for the Airframe trade that are also critical – how can we do that? Here’s were we create our own filter. Open up the Using Filter dialog, select Critical and then Copy… This will open up the Filter Definition dialog. This is one of the nice things I like about Project: the dialog box looks the same as the one for Tables that we tried last month and we don’t have to go through the learning curve again! Notice the similar layout with similar buttons in the same places. Note in particular the meat of the filter which defines the Field Name that we selected and then the Test to be applied to each task and the Value of that test that we want to accept.
So, give the filter a name, say, Critical/Airframe. Select the next space in the Field Name column and from the drop-down pick list select the Resource Names field
Then we need to define the test in a similar way, so select equals (note the other options whilst we’re there).
Then for the Value: select Airframe.
Click OK to create our Critical/Airframe filter, which we can now see highlighted in the More Filters listing.
And Apply it.
We now have only Airframe tasks that are Critical. But the task list is blank! That is because there are no Tasks with Resource Names field that only have the word “Airframe”, as all the tasks with Airframe assigned have more than one resource. Project is very picky and requires exact argument. In this case, the Test “equals” gives a result we’re not looking for. We need to changes the Test. Get back to the Filter Definition and click in the Test field for the Resource Names field and select contains instead of equals.
OK and Apply will give what we were originally after.
Boolean Arithmetic
There is one column in the Filter Definition dialog that I have not mentioned yet: that is the one headed And/Or. This is a bit of Boolean arithmetic that project allows us to use in filters. We can select it by clicking in the cell and choosing from the pick list. Be advised that the And operator is exclusive and will show you fewer results as they have to contain all of the criteria. Whereas the Or operator in inclusive and will reveal more tasks, in that it will accept a task that contains any one of the criteria. This feature allows us to select the features we want to show, and provided there is a field for it, it can be filtered. This is also particularly useful when combined with customised fields which I first covered in Microsoft Project: 14–Customizing Fields
Try changing the operator to Or and Apply. We will see more tasks: those that are Critical, OR have an Airframe tradesman assigned.
As with the Table Definition dialog, the Filter Definition box allows more rows of criteria to be added, and we can adjust the position of the rows using Cut Row and Paste Row buttons. Make sure the And/Or operators are correctly selected before you OK the filter.
User Input
There may be occasions when we might want to be able to define a Value before the filter is activated. As an example, we might want to be able to select which of the tradesmen we filter without having to edit the filter each time or copy it and create a new one for each resource (although they are both valid procedures). Open the Critical/Airframe filter in Edit mode, and change the name of the filter to Critical/Resource. Then put an appropriate question in the Value cell which will then appear in a form for us to enter the criterion we choose. In this case, firstly change the Or back to And, and in the Value cell insert “Which Resource ?”? It is essential that the text we want to see in the form is between inverted commas (quotes) and there is also a question mark immediately OUTSIDE the quotes.
OK and Apply the filter. An input form will appear headed with our filter name: Critical/Resource which is asking us Which Resource? which we can now select from the pick list.
If we select Propulsion we should see only tasks with assigned Propulsion tradesmen and are also critical.
Note that for any filter we can elect to show the related summaries or not. We can also elect to show the filter in the Filter menu.
Short Cuts
Project does supply some very useful short cuts. In the Formatting toolbar we can see a pick list with the currently applied filter, which should still be showing our Critical/Resource, though it does not show which resource we selected. We can use the pick list to select quickly any of the filters, including our new one. Incidentally, it’s well worth keeping an eye on this box before printing the view as, if you want everything to show, you need to select the All Tasks filter, which in effect, removes any applied filter.
Autofilter
There also is an Autofilter option. If we click on the Autofilter button (which looks like a filter funnel with an equals sign), each of the columns will show a drop-down arrow (Excel has an identical feature). It is, perhaps a good idea to show All Tasks before filtering by column. If, for example, we select the Duration pick list, we can see what’s on offer. Note the (All) and (Custom) options, and the more obvious Boolean selections. Otherwise, the pick list will include every different entry in that field (column). Use the scroll bar to see them all.
If we select 3 days, then only tasks with a Duration = 3 days will be shown. If we select (Custom) we are given another dialog related to the field where we can set our own criteria, like filtering for any task with a Duration of 2 or 3 days.
The autofilter is a very powerful device and offers most of the options people may need, particularly when we realize that we can apply an autofilter to any or all of the displayed columns, and we can Save the custom autofilters for future use. Note that the column heading is highlighted in blue when an autofilter is applied. Remember to check all the columns before printing to ensure that we have only the correct filtered headings highlighted in blue.
Turn an autofilter off by selecting (All) from the pick list and the blue highlighting will revert back to normal.
Next Issue (August)
We now have the wherewithal to create our own Tables and our own Filters to those tables. So, in the next issue in August we’ll have a look at combining these to create customized Views.
Remember that if you have Project queries, there’s a wealth of expertise at your fingertips in the Project newsgroups. Try this address to start with: http://www.mvps.org/project/faqs.htm#Project%20&%20Project%20VBA%20Newsgroups.
Have a great Summer if you’re in the Northern hemisphere and a great Winter if down under!
|
