|
In June
we had a look at the basics of customizing fields. I repeat that the concept
of customization was introduced in version 2000, so those of you that have
Project 98 are out of luck! This month we’ll have a look at Value Lists,
customizing a column to show high cost tasks, and creating a new Table for
use with Graphical Indicators of cost warnings.
Setting
To give us something more meaty to work on, I’ve created
a project to build a suspension bridge, and we’ll use a bit of it. Open the
file “Bridge 15 Indicators.mpp” by clicking on this link:
http://www.mousetrax.com/pub/Bridge15.zip
Have a quick look at the Gantt
Chart view to familiarise yourself with the plan. Now right-click
on the Predecessors column heading and select Insert
Column… and in the Field Name selector
choose Text1 and OK.
Customize Fields Dialog.
Right-click the Text1 column heading
and select Customize Fields…
We’re going to look at field Type:, Rename... function, Value
List… and Formula…, and finally, Graphical Indicators….
Field Types
The Customize Fields dialog
opens with all the Text fields showing and its Type: is shown
as Text. Each field can have various types of data defined
by selecting from the Type drop-down pick list.
Try selecting one or two of the others
and see how the field names change to offer the type you want. To repeat
what I said in Microsoft
Project: 14 – Customizing Fields, Project has three
sets of Custom fields: one for Task tables, one for Resource tables
and one for Assignment tables. Each one is unique and cannot be read
or seen in the other. Data entered in custom fields like the Text1 to Text30 or Number1 to Number 20 fields,
any one cannot be seen in the other two. If you want to copy the fields between
the 3 table types, you can adapt the macro seen in Project
MVP FAQ #37 - Custom Fields in Tables at
this site: http://www.mvps.org/project/faqs.htm.
Rename
Rename… will
give the field an alias that can then be referred to throughout Project.
With Text1 selected, click on the Rename… button and enter “Department”,
as we are going to indicate which tasks belong to which responsible departments.
When you click OK, the new name appears
in the Field list with the default name in brackets, showing the original
field name doesn’t change, but allowing the alias.
Value Lists
Value List… will
enable a dialog to create lists of values that can be entered into the field.
When activated, wherever the column is displayed, a drop-down arrow will
reveal this pick list, from which a selection can be made. Click on the Value
List… button and fill it in with the departments in rows 1 to 5, as shown
in the picture at arrow #1.
Note that you can:
- Cut, Copy,
Paste, Insert or Delete rows.
- Change the order of a row by using
the Move arrows.
- Select a default value.
- Restrict data entry to the values
set or allow new values to be inserted in the Value List.
- Change the sort order.
- Import Value
List… from another project.
Click OK, and then click on a
task cell in the new Department column. Click the drop-down arrow
and a pick list will appear, from which you can select an appropriate value
from those we’ve already typed in the Value List. For example, the Open
Access milestone can be attributed to Project Management. Note
that if you try to type in something other than in the Value List,
Project will not allow the entry.
 .
Fill in the rest of the task departments
as you see fit. Having entered the data, you can now use the value list data.
For example, we can use Autofilter to show only the construction department
tasks.
Formula
So, how can we use formulae to give indications
of individual tasks being, say, highly costly and thus we should concentrate
on those, or of a sufficiently low cost that we don’t have to worry too much
about their planned cost? Let’s View/Table:Entry/Cost. Right-click
on the heading for the Baseline column, and insert a new column for Text2, then
right-click on its heading and Customise Fields… and then click the Formula… button.
We had a look at inserting formulae in Microsoft
Project: 14 – Customizing Fields: have another
look to refresh your memories. Again we will use the basic IF structure:
so select Function > General > IIf( expression,
truepart, falsepart ) to insert the formula.
Select one of its elements and enter the data or click the Field button
and select. For example: to show cost cell values above £10,000 and the word “Low” if
less than £10,000, enter the formula: IIf([Cost]>10000,[Cost],"Low") .
This is “saying” if the data in the Cost field is greater than 10000,
then show the data in the Cost field, otherwise show the word “Low”.
OK/OK and
in the Text2 column you will see the word “Low” where the cost is
less than 10000 and the cost figure itself if it is greater than 10000.
Graphical Indicators
New Table
Now, let’s create the conditions whereby
Project will give you a Graphical Indicator (Stop lights or
Traffic lights) depending upon the cost range of each task. Firstly, we will
create a new table in which to view our results. So View/Table: Entry/More
Tables…
Check that the Task radio button
is selected and then click New…
In the Table Definition dialog,
enter the Name of the table: Cost Stoplight or whatever you
want to call it. If you want the new Table to show in the View menu,
click the adjacent box. Click in the first Field Name cell and then
the drop-down arrow and select ID from the pick list. Continue down
the cells to include the fields as shown below, inserting the Titles as
shown, and noting that the other parameters are automatically provided for
you. You can, of course, change these, but we’ll leave them as default. OK/Apply.
Before we continue, try View/Table:
Cost Stoplight/More Tables… to see that the new table is included in
the pick list, from where you can edit it if you need to.
In the view you should now see the columns
we inserted, with the Warning column (the Cost1 field) and
the Total Cost column (the Cost field) filled with costing
data based on the resources’ costs plus fixed costs.
Graphical Indicators
Let’s say we want to apply tests
to a task Warning cell, which will show a graphical indicator in
that cell for that condition using Boolean arithmetic. For example, if the
cost is greater than £10,000 show a red sphere, if less and £5,000 show a
green square, and if between the two, show a yellow diamond. To do this we
have to customize the Warning (Cost1) field to give us the Graphic
Indicators. So, as before, right-click the Warning column heading
and select Customise Fields…. In the Customize Fields dialog,
click the Graphic Indicators… button.
Notice that the Graphical Indicators dialog
still specified the Cost1 field, which we have renamed Warning. Also
that you can include Summaries and you have the ability to edit the
computation by using the buttons for Cut, Copy, Paste, Insert and Delete rows
in the table.
Click in the first cell for the Test,
and select is greater than.
Now click in the Value(s) cell
and scroll through its pick list to get a feel for what’s on offer. In this
case we’ll just type a value of 10000. What we’re telling Project is to give
us a graphical indicator if the value in the Warning field is greater than
10000. Now click in the image cell, again scroll through the pick list to
see what’s on offer, finally clicking on the red sphere. (No, you cannot
have any other images than are shown in the pick list – there’s no way to
create your own or import from elsewhere – sorry!)
Continue down the list to enter the other
tests, to give you a dialog like in the picture.
OK/OK will
then show the indicators in the Warning column, but they are all green
squares, indicating that the values in each cell are less than 5000.
This is because we haven’t put any values
into the cells! What we want is the Total Cost values to populate
these cells. So, highlight the Total Cost column by right-clicking
on its heading and select Copy Cell. Then right-click on the Warning column
heading and select Paste Special….
In the Paste Special dialog, select
the Paste Link: radio button, read the note below in the Results box
and also note that the cell data will be passed as Text Data. Using Paste
Link allows the data to be updated as changes are made in the original
cells. When you OK that, the indicators will change to represent the
conditions that you want applied to each cell (if necessary,click in a cell
to remove the column highlighting). Check it out by changing something to
increase or reduce the cost, e.g. insert the Fixed Cost field and
add 10000 to a low cost task, or shorten the Duration of a high cost
task, or change the Cost Rates for the resources assigned. Try making
the Total Cost to be exactly 10000 and then 9999. Always check out
your efforts by inputting data to test the limiting values you’ve defined
in your formulae.
Summary
The examples I have demonstrated have
shown you specific detailed methods for obtaining an output. The techniques
and procedures will allow you to develop Value Lists for inputting
data into customized columns. We then looked at creating a new table which
we then customized to indicate when cell conditions met certain specific
criteria, and followed this up by populating the cells with Graphic Indicators to
emphasize the output of the conditions set. I hope this has given you an
insight into the powerful and valuable facilities the Project provides to
get your point across to whomever, and is really limited only by your imagination.
Good hunting!
Next Issue
In the next issue, I plan to
have a look at automating your work by using macros.
|
