SSAS Dimension Security Result in Excel Slicer

Question

I have created a role in my Analysis Services cube (SQL Server 2012) and applied a dimension security so that only one of the dimension attribute's member is selected. When I use the cube in Excel 2013 with a slicer for that particular dimension attribute against a pivot table, i get to see only one item, which is exactly what i require. However, when I click on the slicer item, the values of the pivot table do not change, it shows the total values instead of values filtered for that specific dimension attribute member.

The weird thing is that when I view the numbers by browsing the cube, i get the correct number whereas in excel slicer when I click on the attribute member, it does not change pivot table values.

If I select 2 dimension attribute members to be allowed for the role, the excel slicer works perfectly fine and the pivot table values change when I click on either of the 2 dimension attribute members. I am not sure whether this is a bug in excel or there is something else I need to make sure before achieving the desired functionality?

Thanks.

Answer 1

Have you enabled visual totals? You click that check-box in under Advanced tab in Dimension Data page of role setup (see screenshots in this page: visual totals).

Visual totals make sure the results are recalculated to what ever the security is set to. If VT is not checked in, then AS shows you total value for the dimension, without security applied to it. It would not show any cousins etc, only the descendants of the member which security is applied on. It basically 'secures' the details, but not totals...