Filtering Data before Sharing File

[barjoman]

Hello everyone,

I've built a report (several Pivot Tables with Slicers) which loads data from various sources through Power Query and has various fields/relationships added in Power Pivot. One of these relationships allows me to filter for performance by Manager.

I would like to share this report with each manager, but only allow them to view their own data. Is there any way to do this?

The Manager name is added through a lookup table in Power Pivot.

Thanks
Ben

 

[Matt Allington]

There is no row level security in Power Pivot for Excel. This is available in SSAS Tabular. When you log in to SSAS, it controls which rows you have access to based in your used ID.

You have a few options
if you have SharePoint enterprise, you can control different views of the workbook via SharePoint login. I have done this for a client before.

Another approach is is to use something similar to this that I blogged about last year Creating a Desensitised Copy of a Power Pivot Workbook -

I think power update allows you to pass a parameter to a workbook on refresh. Power Update Software - either way, you create a parameter table in your workbook and possibly use VBA to create multiple workbooks automatically.

My last comment. Perhaps you could rethink the need for secrecy. I have seen is a lot over the years - where people what to hide data from other managers. My personal opinion is the effort is high for little return. Sometimes there is little downside of others seeing the numbers, and maybe even some competitive advantages. People can see how well others are doing and maybe learn from it, and/or feel uncomfortable about their own performance focus ing their resolve. JMO

 

[barjoman]

There is no row level security in Power Pivot for Excel. This is available in SSAS Tabular. When you log in to SSAS, it controls which rows you have access to based in your used ID.

You have a few options
if you have SharePoint enterprise, you can control different views of the workbook via SharePoint login. I have done this for a client before.

Another approach is is to use something similar to this that I blogged about last year Creating a Desensitised Copy of a Power Pivot Workbook -

I think power update allows you to pass a parameter to a workbook on refresh. Power Update Software - either way, you create a parameter table in your workbook and possibly use VBA to create multiple workbooks automatically.

My last comment. Perhaps you could rethink the need for secrecy. I have seen is a lot over the years - where people what to hide data from other managers. My personal opinion is the effort is high for little return. Sometimes there is little downside of others seeing the numbers, and maybe even some competitive advantages. People can see how well others are doing and maybe learn from it, and/or feel uncomfortable about their own performance focus ing their resolve. JMO

Matt,

Thanks for your response. To start, I understand and completely agree with your comment about secrecy. In fact, our managers have had access to each others' sales volumes and performance for some time now precisely for the motivation factor, however, this project involves providing our Managers with detailed financial information which was previously not visible to them, and should not be visible to their colleagues.

1) SSAS Tabular is currently over my head. I'm still learning my SQL and may be able to use this down the line.
2) We do not have access to Sharepoint Enterprise.
3) I thought of this method already, that is, filtering the data in Power Query before loading to the Data Model. However, as I mentioned, the Manager name is added in the Data Model and is not part of the original data set, so this is not possible.
4) A paid addon is not an option.

To expand on item 3, I did think of running the data through Power Query again after the Manager name has been added, but I don't know if this is possible.

Thanks,
Ben

 

[Matt Allington]

Is your data in SQL? How many managers?

 

[barjoman]

Is your data in SQL? How many managers?

Part of the data is in SQL, part of it isn't. All of the data is exported as csv and then imported through PQ.

Currently about 20 managers, but expecting to grow to 30 or so in the near future.

 

[Matt Allington]

So 30 managers means it is too much work to split manually. It may be fine once, but make a change and you have to split it again or do the maintenance 30 times.

You could do a join in PQ to load the correct sales, but I fear it will be slow x30 copies. If this were me, I would do this
1 use SQL to join the manager key to the data table.
2 connect the above table to PQ
3 create a table in excel with a list of all the manager keys.
4 create a parameter table in Excel and load it to PQ. This table would have a single manager key
5 join the table from 2 to the table from 4 to filter for a single manager.
6 write some VBA to cycle through the table in 3 to update the table in 4, 1 manager at a time followed by a data refresh and a "save as copy [manager name]

 

[barjoman]

So 30 managers means it is too much work to split manually. It may be fine once, but make a change and you have to split it again or do the maintenance 30 times.

You could do a join in PQ to load the correct sales, but I fear it will be slow x30 copies. If this were me, I would do this
1 use SQL to join the manager key to the data table.
2 connect the above table to PQ
3 create a table in excel with a list of all the manager keys.
4 create a parameter table in Excel and load it to PQ. This table would have a single manager key
5 join the table from 2 to the table from 4 to filter for a single manager.
6 write some VBA to cycle through the table in 3 to update the table in 4, 1 manager at a time followed by a data refresh and a "save as copy [manager name]

Matt,

I've solved the problem using a series of Joins in PQ to get the Manager names in there. Thanks for your suggestions and help.

I do have a follow-up question which I've posted here:
http://www.mrexcel.com/forum/power-bi/920741-powerquery-merge-effective-date.html#post4426162

Any ideas would be appreciated.

Cheers,
Ben

 

[SimonNU]

I've implemented workflows in the past whereby the data model contains windows logins. When combined with about 6 lines of VBA, this will automatically filter all pivot tables to the Windows login of the person opening the workbook.

This has the added benefit of users not requiring PQ. It's also very fast.

 

[barjoman]

I've implemented workflows in the past whereby the data model contains windows logins. When combined with about 6 lines of VBA, this will automatically filter all pivot tables to the Windows login of the person opening the workbook.

This has the added benefit of users not requiring PQ. It's also very fast.

Simon, I can't use windows credentials for this particular application because the users are not all using company issued equipment, but I'd be very interested in seeing your VBA code.

From your post however, it seems like this is only using the VBA to apply a filter, meaning that they could then manually change the filter and view the rest of the data. My main objective was to entirely hide subsets of data from each user.

 

[SimonNU]

You are correct, that is the approach. You can simply put the filter on a separate sheet and apply the appropriate protection to prevent users from accessing it.

The below VBA demonstrates the workflow - it's pretty straight forward. Obviously you'll need to add more security to the workbook using this method:

Function sUserLogin() As String


sUserLogin = Environ("USERNAME")

End Function

... and in a Workbook_Open event:

Sheet1.Range("usernamePivotTableFilter").Value = sUsername