Can you lock one filter in a pivot table?

[Ray707]

Hello,

I am working on an excel file that will be sent out to multiple stakeholders. It contains a pivot table with sensitive data. I do not want department heads to be able to go into the pivot table and use the dropdown on one of the filters at the top of the pivot to look at other departments' data. In other words, I want to lock/disable one of the filters at the top of the pivot table so it is not accessible. Is it possible to lock/disable one filter at the top of the pivot table?

Any help appreciated :thumb

[Ray707]

I found this macro (under 'disable selection') that disables all selection, I'm thinking whether this could be tweaked to disable one filter?

https://www.contextures.com/pivottablerestrictdatamodel.html

[Ray707]

I have also come across this piece of code which does exactly what I want, however, there's a flaw as you can see other departments data if you simply write the department name in the disabled cell.

https://www.myonlinetraininghub.com/excel-forum/vba-macros/prevent-pivot-table-filtering

Is there any way around this? :(

[p45cal]

There are several things you can do:
Do not have the source data of the pivot in the file you send.

Do not have the source data of the pivot, even if it's in another file, reachable by the recipients (when the recipent opens the file he may well get a security notice that external data connections have been disabled but offers the choise to enable them, which may mean he can gain access to the data file, especially if it's on a network).
[The user will still be able to see the name of the file it wants to connect to if he tries to change the data source of the pivot table, but he may not see its path. And you can't delete it.]

One thing you can do to retain some functionality of the pivot table is to have a copy of the data for just the relevant department within the workbook you send; this is easy if you put a grand total somewhere in the pivot table and double-click on that grand total while the pivot is filtered for the relevant department, a new sheet will be added for whatever rows of source data are needed to produce that grand total. Now change the source of the pivot table to that new sheet's data (and hide it if you want).

Most important:
Un check the option in Pivot Table Options… on the Data tab, Save source data with file,

and for good measure you can also untick Enable show details and set Number of items to retain per field to None in the dropdown.

The safest way is to eliminate the pivot table altogether; copy and paste its values only.

Oh, and if your pivot is based on the data model, make sure there is no data model in the workbook you send.

Send a pdf file to them!!

[Ray707]

There are several things you can do:
Do not have the source data of the pivot in the file you send.

Do not have the source data of the pivot, even if it's in another file, reachable by the recipients (when the recipent opens the file he may well get a security notice that external data connections have been disabled but offers the choise to enable them, which may mean he can gain access to the data file, especially if it's on a network).
[The user will still be able to see the name of the file it wants to connect to if he tries to change the data source of the pivot table, but he may not see its path. And you can't delete it.]

One thing you can do to retain some functionality of the pivot table is to have a copy of the data for just the relevant department within the workbook you send; this is easy if you put a grand total somewhere in the pivot table and double-click on that grand total while the pivot is filtered for the relevant department, a new sheet will be added for whatever rows of source data are needed to produce that grand total. Now change the source of the pivot table to that new sheet's data (and hide it if you want).

Most important:
Un check the option in Pivot Table Options… on the Data tab, Save source data with file,

and for good measure you can also untick Enable show details and set Number of items to retain per field to None in the dropdown.

The safest way is to eliminate the pivot table altogether; copy and paste its values only.

Oh, and if your pivot is based on the data model, make sure there is no data model in the workbook you send.

Send a pdf file to them!!

That's very interesting and definitely ideas I will take on board in the future. The problem is the source data has to be in the file. With this in mind, is there no way of locking/disabling one filter in a pivot?

[p45cal]

The problem is the source data has to be in the file.
Then your cause is lost.
Anyone with a little nous can do some internet searching and break any protections on the workbook, then they can create their own pivot from the data.
Can't you restrict the data to just the relevant departments? I realise this might mean some measures or calculated items/fields might not show correctly if they use company-wide data.

[Ray707]

Then your cause is lost.
Anyone with a little nous can do some internet searching and break any protections on the workbook, then they can create their own pivot from the data.
Can't you restrict the data to just the relevant departments? I realise this might mean some measures or calculated items/fields might not show correctly if they use company-wide data.

Interesting. So when there is source data in the file there is no way around this? Damnnit :crying:

I'll delve deeper into some of your solutions outlined above. Thanks for your help!

[Paul_Hossler]

Interesting. So when there is source data in the file there is no way around this? Damnnit :crying:

I'll delve deeper into some of your solutions outlined above. Thanks for your help!

What I've done in the past was to use a macro to write each page field's (e.g. Department) filtered pivot table data to a separate workbook as just their data.

They lose PT features, but they only get their data.

You could get fancy and extract their potion of the data from the source data into a worksheet in a new workbook, add a sheet to that WB, and then put a PT using just that data onto their second sheet

[Ray707]

What I've done in the past was to use a macro to write each page field's (e.g. Department) filtered pivot table data to a separate workbook as just their data.


Actually I'd be interested in that- do you have the code for it?

[Paul_Hossler]

I can look for it or recreate it easily

Give me a little time

[Ray707]

I can look for it or recreate it easily

Give me a little time

Sure, no problem!

[Paul_Hossler]

I had done something similar here awhile ago, but couldn't find the previous post. It was easy enough to just recreate it for you

Look at Sales All.xlsm with some made up data

It makes 3 extract WBs based on Dept page field

The extract sheet can have some formatting before saving, and you can change the file naming easily

No real error checking

Edit: I was a little terse with my variable names, so if that's a problem let me know and I'll expand

[Ray707]

I had done something similar here awhile ago, but couldn't find the previous post. It was easy enough to just recreate it for you

Look at Sales All.xlsm with some made up data

It makes 3 extract WBs based on Dept page field

The extract sheet can have some formatting before saving, and you can change the file naming easily

No real error checking

Edit: I was a little terse with my variable names, so if that's a problem let me know and I'll expand

Hi Paul, thanks for this. I have a question as it doesn't appear to be working; when I click on 'split pivot table' in the sales all depts file it activates a pop up which says 'done' but no new wb's are created, should I expect the workbooks to pop up in separate windows or does it save the workbooks somewhere..?

[Paul_Hossler]

They should be in the same folder as the 'Master' or the all date WB with a name like

C:\Users\Ray\My Documents\Sales\Sales2020-2021-04-07-East

so if the 'All Data' WB is "C:\Users\Ray\My Documents\Sales\Sales2020.xlsm"

the WB for the East department generated on April 7, 2021 would be


"C:\Users\Ray\My Documents\Sales\Sales2020-2021-04-07-East"

where

wb.Path = "C:\Users\Ray\My Documents\Sales"

Application.PathSeparator =""

Left(wb.Name, InStr(wb.Name, ".") - 1) = "Sales2020"

"-" & Format(Now, "yyyy-mm-dd") = -2021-04-07

"-" & pi.Value = -East



'make file name
sFilename = wb.Path & Application.PathSeparator & Left(wb.Name, InStr(wb.Name, ".") - 1) & "-" & Format(Now, "yyyy-mm-dd") & "-" & pi.Value




I prefer to keep generated WBs in the same folder as the source, but you don't have to

I prefer to tag generated WBs with a timestamp and maybe a identifying tag

[p45cal]

In the attached is a tweak of Paul's macro which will retain some functionality of the pivot table by including a subset of the Sales data pertaining to only the one department in each of the produced files.
It automates this:
One thing you can do to retain some functionality of the pivot table is to have a copy of the data for just the relevant department within the workbook you send; this is easy if you put a grand total somewhere in the pivot table and double-click on that grand total while the pivot is filtered for the relevant department, a new sheet will be added for whatever rows of source data are needed to produce that grand total. Now change the source of the pivot table to that new sheet's data (and hide it if you want).There are some comments in the code.

[Ray707]

yep I got it working, thanks!:thumb

[Paul_Hossler]

@p45cal



One thing you can do to retain some functionality of the pivot table is to have a copy of the data for just the relevant department within the workbook you send; this is easy if you put a grand total somewhere in the pivot table and double-click on that grand total while the pivot is filtered for the relevant department, a new sheet will be added for whatever rows of source data are needed to produce that grand total. Now change the source of the pivot table to that new sheet's data (and hide it if you want).

Good tip :clap:-- In my suggestion, I was thinking more alone the lines of a static management report, very non-interactive but formatted pretty

[Paul_Hossler]

@p45cal --

I wanted to see how you got the PT's source data onto a new worksheet in the new workbook

This seems to be where the magic happens, but the online help for .ShowDetail doesn't talk to it




With NewPT.DataBodyRange
.Cells(.Rows.Count, .Columns.Count).ShowDetail = True 'create a reduced data source for the pivot
End With





Can you explain a little, please?

[p45cal]

Since as long as I can remember you've been able to double-click on a value in the Values area of a pivot table and it creates a new sheet showing the data that's used to make up that value. It applies to grand- and sub-totals too.
Record a macro of you double-clicking such a value and you should get the Showdetail code.
To get all the records pertaining to a particular filtered pivot you need to have a grand total to click on, one where both the columns and rows are totalled. The cell containing that grand-total is usually in the bottom right corner of the values area of the pivot, given by pivottable.databodyrange.Cells(pivottable.databodyrange.Rows.Count, pivottable.databodyrange.Columns.Count).

As mentioned in msg#4 you can turn this ability off:
and for good measure you can also untick Enable show details and set Number of items to retain per field to None in the dropdown.

[Paul_Hossler]

Ahh -- I know (also for many years) about the double click on a PT cell, but

1) always used it interactively

2) never tried it on Grand Total

3) Didn't realize that it was .ShowDetails = True that actually added the data WS with the sources of the numbers, esp. since the online help is a little terse

https://docs.microsoft.com/en-us/office/vba/api/excel.range.showdetail?f1url=%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(vbaxl10.chm144196);k(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue


BTW, I also just learned that if you double click on a row or column header (e.g. A5) you get a "Which one" dialog, which rearranges the PT and sets the .InnerDetail property, but no new WS