I have created an example app to help get some suggestions regarding best practices for building scalable applications. In my example (attached), I have four departments that I will be displaying productivity units. A managerial employee should have a “my department(s)” sheet with a view of 1-N departments which they are responsible for as well as the ability to view any and all departments in another “all departments” sheet. Rather than build dozens of applications or sheets with complex security rules to accommodate each individuals unique productivity view, I would prefer to build something that is dynamic and can easily adjust to the authenticated user who opened the application. I was considering pulling the UserId from the OSUser() function and adding that criteria to the set analysis but I am open to any ideas that will assist in managing the applications over the long term. For example, suppose my application has a “bug” where I rolled all data up into four quarters where I really intended to show yearly quarters, if I had a separate sheet for every department managed by a security rule, then I would need to make the correction in multiple locations (despite using master items) which creates unnecessary duplication and complexities making maintenance more difficult and error prone. If you created a scalable solution that solves this issue, I would appreciate any and all help! Thanks
My load script contains a list of employees and the departments they have access to. I created an application variable that is set to the current user (OSUser / currently hard coded for the example purposes). This variable is then placed as a filter in set analysis for each of the measures causing it restricts the users view to what they are designed to see. Has anyone implemented security with this method? If so, what are the disadvantages / gotchas to look out for? Additionally a more thorough example would be helpful.
Example set analysis to restrict view to current user:
I think you have a misunderstanding of how to utilize section access. It does not hide or limit what sheets a user can see from an object standpoint. That can be done with security rules if needed but it get's complex. Section access was intended, in part, to attach to your data set. Basically what you're trying to do with set analysis and variables is exactly what section access does.
Also please don't forget this is a user supported forum and no one is getting paid to be here or answer questions. Multiple snarky bumps to your own question it's going to motivate people to help.