Wednesday, April 30, 2014

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!

Share this article :

Sunday, April 27, 2014

Create dynamic multi-lingual Excel BI reports

The challenge

In a recent development of BI report for project status, I've been challenged to create an easy bilingual functionality in excel services.
As you may know, Excel services is giving a challenge to not use macros.

The preparation

First, you will have to define the translation terms and identify them with an ID:
Then, you will have to prepare a easy way to select your language.
To do so, you will have to create a "dummy" pivot table to be able to create a slicer on it.
the source data is really simple :
 Selecting this data source, you will have to create a pivot table (Insert/Pivot table) and add the data source as a filter

The Magic

You're now all set to create multi-lingual fields to be included wherever in your Excel Services reports.
The magic is coming from a compilation of pivot tables filters linked to slicer and index/match functions

So, you can now in your report sheet create a slicer pointing to your early created pivot table:
The effect of this is that your filter field seen below will change each time you will select a new language in your slicer.
You can now create a "not so complex" Excl formula pointing to the required data, so, if you want to gather the "Project Status" in an other language, you will use the following formula:

$A$1:$D$6 = the source table
1                 = the index of the term you want to get (1 for the project status translation) = The only variable of the formula
$A:$A         = where the IDs are
$J$2            = the current selected language = the pivot table filter
$1:$1          = where the titles (languages) are

Then, while selecting a language from the slicer, the text will automatically change based on the ID you selected:
Hope you like this trick
Share this article :

Friday, April 25, 2014


Hello to you all the community of project server administrators or users of this wonderful tool.
With my co-author, wewill try here to gather tips and trick and also best practices around the Project server day to day life

Jérome & Guillaume
Share this article :