I recently found a great PowerPivot feature to make the published PowerPivot sheet “view only” for a end user.
When you have write permissions for a PowerPivot sheet you can open the sheet in Excel and use all the measures and columns and use everything available in the sheet by using the “Open in Excel” button:
Now the great news, you can contain these permission to view only, documents can only be viewed sever side:
When you open the PowerPivot sheet in the browser you see the Open in Excel button is gone:
When we open the PowerPivot gallery as a default document library you will not be able to download the Excel sheet, it will render as html when you download it. I think this will be a very usefull permission !
Unfortunatelly in this case i can’t use slicers if i have external data connections.
Hu @cysiek , I am not sure what you mean.
I have real life example.
I have site containing many worker related data such as team, working hours, salary, mark etc.
Every worker is in a team, which has a team leader.
What i wanted to achieve is one PowerPivot file, for every team leader, but showing ONLY his own team data.
I created PowerPivot file, which gets data only from one SharePoint site. There are two slicers, one with team lader, one with worker.
When i click on team leader, only his workers are shown and 4 different charts are updated which is pretty cool.
I wanted to limit other team leaders to be able to change team leader slicer so i moved it to another sheet, which is not visible (Publish Options).
I created page with Excel Web Access Web Part and Current User Filter. After some magic ([].[]) in sending parameter to Pivot file, i have this approach working.
So far everything looks good. But then comes part, when i have to give it to team leaders.
I didn’t want them to be able to download a file and change team leader slicer, showing very confidental data.
I’ve added View Only permission to Pivot file and page for test leader.
When i opened through page slicer filtered only test team, but when i wanted to change to another worker by clicking worker slicer i got error:
“An error occurred during an attempt to establish a connection to the external data source used in the workbook”
Same error occurs when i open Pivot file directly.
I had this king of error before, and changing in Pivot file -> Connections -> PowerPivot Data -> Properties -> Authentication Settings to none was solving the issue. Not this time.
Changed Authentication to SSS ID, where test leader is in Members section. Still nothing.
When i open Pivot file as full control admin, i can refresh data with this SSS ID, so connection works.
I started to dig and found a page, that at least Read permission is needed. I changed to that, still the same.
It only works, after i added Contribute permission to Pivot file, which is unacceptable since team leaders must not download a file.
Do you have any idea how make this work?
@Kasper: Any update on this?
Ok, i will answear my own question.
I checked how Pivot library has been configured and found out that it was versioning enabled.
When versioning was set to “Create major and minor…” pivot file behave wrong. There was error when user has View only permission. I changed that to “Create major versions” setting and everything works just fine.
Conclusion: As always SharePoint magic beat us, this product is great as an idea, but pretty poor written.
hello Kasper,
I know the article is pretty old, but I would to ask you for the any solution to share to the end users PowerPivot table results (connected to AS) without possibility of changing the structure (adding new columns, measures and so on)
is there any easy way to do that?
Regards!