The challengeIn 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 preparationFirst, you will have to define the translation terms and identify them with an ID:
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 :
The MagicYou'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:
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