I have an external data source that implements row-level security.
I have an XLSX file which I want to distribute which will have a pivot table based on the external source. All of my users have an identically configured ODBC connection, except it uses each of their personal credentials and thus they have access to different data.
I've explored all of the connection & pivot table settings that supposedly give you such controls but they are not working for me. When I save my workbook, it seems that it is not possible to prevent the contents of the pivot table (as they currently look) from being saved. When a new user opens it, they will be able to see the current pivot table contents (which they perhaps shouldn't have access to) until they click "enable content", accept the various popups and/or wait for refresh.
Previously, I created a table based on external source and configured the connection to not save data - this worked. I then created a pivot table on top of the table range and configured it to not save source data. This sort of works except the table refreshes first and so the pivot table loses its settings and you have to start again with a blank pivot.
If you create a pivot table directly on the external source (rather than indirectly via a table), which I expect is the best practice, the tickbox in pivot table options for "Save source data with file" is greyed - presumably because excel knows the source data is actually external and so the question isn't relevant - except is is relevant because the pivot table output still contains data when saved
The only thing I can think of is is save the workbook as a user with 0 permission so the pivot table is structured correctly but with 0 contents and then send that round. Users will then see no/harmless data before it auto-refreshes at which point, they'll see what they should see.
Kind of feels like a glaring omission from excel. Am I missing something?
