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 :

No comments :

Post a Comment