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:

=INDEX($A$1:$D$6,MATCH(1,$A:$A,0),MATCH($J$2,$1:$1,0))

$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