Pivot Table - send Partial Data only

[kanwer pradeep singh]

I have data of company expenses with Cost Center Responsible persons. I have created Pivot table and now I want to send Pivot table to respective CC responsible person WITH ONLY his/her data i.e. he should only get data related to his CC and not able to see other CC responsible person data.

I can simply copy whole data to new sheet, delete others data and re-create Pivot table with his data only but what happens if I have 20 different CC responsible persons. I cannot waste so much of time doing this 20 times.

Any fast and easy solution please.

 

[GraH - Guido]

Hmm, perhaps something like this:

1. Create the pivot template
2. Put the CC responsible in the filter section of the pivot
3. From the pivot table tools - Analyze select Options - Show Report Filter Pages: for each CC responsible, a new sheet is created with the correct pivot
   
4. From Pivot table options - Data uncheck the Save source data with file.

Then manually, or with a macro, save each sheet as a new workbook and send it out.

 

[kanwer pradeep singh]

Hi G,

Thanks for your reply, up to step 3 is ok but step 4 defeat the purpose.

I want to show data of the particular CC responsible Person (just do not want him to see all other CC responsible person data). If I follow step 4, all the source data will be lost and even this CC responsible person also cannot see his own source data.

This is like copy and paste Value.

I am sorry if I confused you earlier.

Thanks,
Pradeep

 

[GraH - Guido]

If I follow step 4, all the source data will be lost and even this CC responsible person also cannot see his own source data.

I don't agree. It does not delete the data, it only disconnects the pivot from the source. But the data resides within the pivot cache.
Only if some-one would refresh the pivot, then the data is lost. Or if you use the auto refresh feature. If you would also disable the drill down, the CC cannot view the data behind the pivot as well.
You'll have to play around with the pivot settings a bit.
I don't think you confused me, I believe you are not fully aware of all these pivots options I explained. It would help however if you upload a sample file, as per forum rules...

EDIT: for further automation you would need a macro.

 

[Chihiro]

As long as data resides in Excel. There's no real secure way to limit access. Be it on sheet, in Pivot Cache or in data model. You can easily read entire content of Pivot Cache with VBA and retrieve all underlying data contained within (filtered or not).

If data security is important, only have data that specific user should have access to in Excel. Depending on how the data is brought into Excel, there are some options.

1. Data stored in DB - Leverage Row-Level Security feature to limit access to only data allowed in a query/table. One of most secure way to distribute data, without additional layer in reporting side.

2. Use advanced filter & small vba to export out only the pertinent data to new workbook, build pivot table from it.

If using Office 365 there are more options available.
3. Use code or some other method to upload file on individual folder for each file. Share it with specific user only. Distribute report template that queries appropriate folder based on either Environ("UserName") or apiGetUserName.

4. Leverage SharePoint list to store data, and create live connection file, but control row level security in SharePoint (i.e. user can only see data tied to their account).

etc, etc.

Best method will depend on your environment. If you need further help, upload sample file, that's desensitized, but with same headers, data types and enough data to demonstrate your need. And indicate version of Excel along with tools that you have access to (PowerQuery, PowerPivot, database etc).

 

[GraH - Guido]

That would be fast and easy, Chihiro? For you that's probably very true. You do assume the responsible manager would try to read the data they are not supposed too. I'm just too naive...
Given that, I'm impressed with all the options you offer to secure data. Not my cup of tea at all. Then again, I'm a coffee drinker...

 

[Chihiro]

That would be fast and easy, Chihiro?

@GraH - Guido
Depends.

Options 1, 3, & 4 takes bit of time to set up. Actual time it takes will depend on your technical knowledge. But once properly set up. Requires minimal maintenance.

Option 2, will also take bit of time to set up. Then few minutes to upload files via code on every run. As long as user don't lose their distributed template, that side will be hustle free. But this method is more prone to being broken than other 3.

As for data security. When dealing with corporate finance data with various underlying info, to which users shouldn't have access. You'll learn the importance of data security very quickly.

 

[GraH - Guido]

As for data security. When dealing with corporate finance data with various underlying info, to which users shouldn't have access. You'll learn the importance of data security very quickly.

Very true. I'm fortunate enough not dealing which such things. As finance, not my passion at all. But I have but respect for those who know how to secure data.