MS Project: how to get more than the 10 default local custom fields?

Hi,
Those days, I've been playing around with the Enterprise Global (as you can also see in another recent post), quite a while!! Recently a user who was working with MS Project standalone (meaning not connected to Project Server or Project Online) had the following concern. He needed to use more than the 10 cost custom fields. Indeed if you go to the project tab and click on "custom fields", you'll see that you are limited to 10 cost fields. 

Figure 1: cost custom fields in MS Project

The first workaround is obviously to use text custom fields and/or number custom fields. But there are some limitations:

• the number custom fields cannot be displayed as cost with the currency symbol,
• the text custom fields cannot be rolled up unless you write some formula or do some VBA.

So how to have more than 10 cost custom fields? You need to have in your professional network someone who has access either to a Project Server or Project Online instance. Indeed you have no other limitations than performance for enterprise custom fields. This means that you can create almost as many cost custom fields as you need. But this is actually not as simpler as it might be. Indeed if you check out the enterprise global and open the organizer on the "fields" tab, you'll see that you cannot select either the global.mpt or the checked-out enterprise global. Thus you cannot copy items into the local project arghh..

So here are the steps: 

1- Create the extra cost enterprise custom field(s) on a Project Server / Online tenant

This is the first step, quite straight forward. Connect to your instance, go to server settings and create the extra enterprise cost custom field(s). Be sure to configure the rollup at summary task level as needed, for example with a sum.

Figure 2: extra cost enterprise custom field created from the server settings

2- Save the global items locally

Open MS Project Pro connected to the given PWA instance and save a blank new project locally. Be sure to save in the local file all enterprise custom fields and global items.

Figure 3: project saved locally with the global items

You can now close MS Project.

3- Copy the local extra field into the global

Now the issue is that you'll have ALL enterprise custom fields in your local file. This might not be suitable since the initial need comes from a user who is working in a standalone mode which is more than likely not your enterprise context. Thus he probably doesn't want to be polluted by all your enterprise metadata. 

Logically this last operation should be done by the initial user which has the need since it will copy the extra cost field(s) in his global. So he has to reopen MS Project not connected to Project Server and open the local file which will contain ALL enterprise custom fields from your instance. Finally he opens the organizer and copies the extra custom field(s) from the local file to his global.mpt. You can also do it by yourself: open the local file, copy only the extra cost fields to your global, open a new blank file and save it locally, eventually overwriting the previous file containing all the enterprise custom fields.

Figure 4: copy the extra cost field from the local file to the user's global

Now the user will have his extra cost custom field(s) in his global for any new project, having more than the 10 initial cost custom fields. Here is the final result with an extra 20 cost custom fields in addition to the 10 default local ones.

Figure 5: 20 extra cost custom fields available locally in addition to the 10 default local cost custom fields

Note that in the image below, even if I started MS Project not connected to Project Server, I still have the 20 extra custom fields (from 11 to 30) flagged as "enterprise". Just for information, if connected to a Project Server / Online instance, you'll have the following icon in the status bar:

Figure 6: icon in the status bar when connected to Project Server / Online

Any limitations?
If you go to the project tab then custom fields, you'll not see those 20 "enterprise local" fields, thus you cannot configure anything such as the rollup. You have to think about all settings before doing the operation.

Figure 7: the 20 extra custom fields not appearing in the list of cost custom fields

Another limitation to mention is that while copying the extra fields to another project file, the user in question will see that all settings about sum and rollup are lost. So basically the principle is to stick to the initial file and reuse it.

What do you think? Do you have another ways to extend MS Project capabilities in a standalone context?

Master the Multiple Choice Custom Fields in your Excel Reports!

It happens sometime that there is a need to report information stored in a Multiple Choice Custom Field:

If this happened to you, you must have seen that the custom field is not part of the standard views such as MSP_Project_UserView.
You will have instead a specific view for each Multiple Choice Custom Field named MSPCFPRJ_<CustomFieldName>_AssociationView.

Then, you will have to write down a clever request giving you the list of choices made by projects.
Don't worry, you can find this clever information right below!

SELECT Proj.ProjectName,
       lt.MemberFullValue AS 'VLookupField'
FROM dbo.MSP_EpmProject_UserView AS Proj 
LEFT OUTER JOIN [dbo].[MSPCFPRJ_<YOUR_CUSTOM FIELD NAME>_AssociationView] AS MVassoc -- view for multi value field 
ON proj.ProjectUID = MVassoc.EntityUID 
LEFT OUTER JOIN dbo.MSP_EpmLookupTable AS lt 
ON MVassoc.LookupMemberUID = lt.MemberUID 
order by    ProjectName asc

I would recommend to do the post treatment in the excel itself. it would be possible to link the below request with the one that will gather all your project information but, be careful, it will multiply the number of lines per the number of choices made on projects.

Then, I recommend to create a specific tab in your excel report to show the information
This will return you something like this while rendered in an Excel pivot table with data connection:

Be sure to have checked the "Repeat All Items Label" check box

Now, you may want to return these information on another sheet gathering other project information.
To do so, I will create a one cell concatenation of the selected project.
First step is to gather the information based on the project on several cells

The formula to gather several lines of a specific project is quite ugly:
{=IF(ISERROR(INDEX($A$1:$B$5000,SMALL(IF($A$1:$A$5000=$G$4,ROW($A$1:$A$5000)),ROW(1:1)),2)),"",INDEX($A$1:$B$5000,SMALL(IF($A$1:$A$5000=$G$4,ROW($A$1:$A$5000)),ROW(1:1)),2))}

$A$1:$B$5000 = Where to search
$A$1:$A$5000 = Where are the project names
$G$4 = The project name to search
1:1 = The line number you want to fetch
Be careful, this is array formulas, that means that you will have to click on CTRL+ENTER to validate your formula!

Here is the overall list of formulas, as you can see, only the parameter number changed (I return here a maximum of 12 entries)

To finish, the easiest part is to put all that in a single cell with the following formula.
To render it nice, I created a second column containing the separator so that a separator is only created while a value is returned. I then concatenated the overall in a cell

Separator cells are looking if there is a value after the current one and if yes displays the separator:
=IF(G6<>"",$H$19,"")

Result cell is a concatenation of the entire table:
=CONCATENATE(G5,H5,G6,H6,G7,H7,G8,H8,G9,H9,G10,H10,G11,H11,G12,H12,G13,H13,G14,H14,G15,H15,G16)

Tips: you can use =CHAR(10) as separator to render in a cell on several lines

You are now able to render multi-choice custom field in your excel services report!

Find here an example of all I just said in an excel file!

Help from Microsoft : http://msdn.microsoft.com/en-us/library/office/ee767688.aspx

Cascading custom fields

Department feature introduced with Project Server 2010 is a really great feature which allows to reduce the number of  PWA instances by having various configuration per business units. But even in organizations where this feature is not in use, departments can actually be used for answering specific customer needs.

In this post I'll detail one of this specific configuration that I recently deployed for one of my customer: project custom fields cascading.

Basically the need is to automatically display values in a lookup table custom field depending on the value select in another custom field. In our example below, we'll use the department field containing 3 values:

• IT
• HR
• Finance

Depending on the value selected, a specific custom field will be automatically displayed. Here are the steps to follow (unfortunately all screenshots are in french but the meanings are quite obvious) :

1. Add the business units' values in the out-of-the-box department look-up table.

Figure 1 : adding values to the department look-up table

2. Create 3 look-up tables for each department with appropriate values.

Figure 2 : look-up tables corresponding to the departments

3. Create 3 project custom fields, each one linked to the appropriate look-up table and department.

Figure 3a : 3 custom fields linked to the corresponding look-up tables and departements

Figure 3b : detail of the HR custom field configuration

4. Create a new PDP with the basic info webpart, inserting the department field in first position, then the 3 project custom fields.

Figure 4 : PDP creation with basic information webpart

5. Update the EPT adding the new PDP.

Figure 5 : EPT updated with the new PDP

6. Create a new project choosing the updated EPT, navigate to the newly created PDP and choose one of the value in the department field.

Figure 6 : new PDP in the newly created project

Note that none of the 3 custom fields are displayed since by default no department is select neither.

Figure 7 : department selected (IT)

Figure 8 : IT deparmtent custom field displayed based on the department selected

Figure 9 : selection of a value in the IT department look-up table

Et voilà!!

In addition to this configuration, if resources have been associated to department(s), they'll only be able to select their department(s). This kind of configuration is really easy to deploy and brings a nice and user-friendly added value for the customer.

Try it and hope you'll like it!