locked
Dynamic Slicer for permissioned SP2013 PowerView report RRS feed

  • Question

  • I'm currently working on a project to transfer management BI reporting onto Powerview on SharePoint2013. I've used USERNAME() to create dynamic permissioning so each user logging into SP2013 can only see data for their own business and region (this has been achieved by using the following Calculate function for each expression e.g.

    MTD Rev:=CALCULATE(TOTALMTD(SUM([MTD AvgActivity]),'Date'[Date]), FILTER(CROSSJOIN(VALUES('Hierarchy'[Permission Level]),Permissions,VALUES(Region[Region])),'Hierarchy'[Permission Level]=Permissions[Business]&&Permissions[User]=USERNAME()&&Region[Region]=Permissions[Region]))

    However, an issue I'm trying to resolve is to remove members from a PowerView slicer that a permissioned user does not have access to see. For example, if the data in my model relates to a global line of business (with regions EMEA, AMRS, LATAM and APAC) but the permissioned user only has access to EMEA, then I'd like only EMEA to be shown in the slicer i.e. the only entry available on the slicer is EMEA hence there is no option to select AMRS, APAC or LATAM (which would be blank anyway). I want to do this as I'd like to use the Slicer to indicate the scope of the report to the user.

    Any guidance on how this could be achieved would be much appreciated

    Many thanks

    Friday, August 19, 2016 8:56 PM

Answers

All replies

  • Hi JW,

    Based on your description, you need a dynamic slicer items for different users, right?

    In your scenario, you need to have relationship between username and regions. In order to implement dynamic security for this scenario, you can add two additional tables to your model. Please refer to the link below to see how to achieve it.
    https://msdn.microsoft.com/en-us/library/hh479759%28v=sql.120%29.aspx?f=255&MSPPError=-2147217396

    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Charlie Liao Sunday, September 4, 2016 9:23 AM
    • Marked as answer by Charlie Liao Thursday, September 8, 2016 9:38 AM
    Monday, August 29, 2016 7:45 AM
  • Thanks for the reply Charlie - looking at the link you posted about creating the two additional tables, this seems relevant if my data model is in an SQL database however my it's in powerpivot in excel, so I don't think the documented solution would work. The issue I'm struggling to get round is not being able to use DAX function Username() in a calculated column. Any other work around suggestions greatly appreciated

    Many thanks

    JW

    Friday, September 9, 2016 4:06 PM