2010-04-20

Using a filter with a Excel Services and a Pivot parameter

I was going to create a BI dashboard containing a Pivot table from Analysis Services and some KPIs. The Pivot table sholud be rendered by Excel Services and the content should be filtered on the current user's country (region he/she was responsible for). The conuntry information was stored in the user profile. Straightforward - I thought, but oh no that was wrong ...

Everything was simple and easy until I was going to connect the Current User Filter web part to the Excel Services Web part and filter the pivot on the curent user's country. What I discovered after quite a lot of googling and reading different blog posts, was that a Analysis Services Pivot table parameter expect a value that is not a "simple" value as e.g. "Canada", but rather "[Geography].[Country].&[Canada]".

In my case then the Current User Filter Web part that comes OOB with SharePoint was not suitable, and I had to write my own. This filter wraps the user profile value into the correct Analysis Service Pivot parameter syntax.

No comments: