I'm trying to build a module that displays only the top ten cost centers based on a variance line item that I've got calculating. I'd gotten it to work with a RANK formula and then a boolean line item, but then when I started trying it with selective access set up on the cost center, it broke and I only see a few of what should be the top ten items based on my more limited access. It appears that the rank still calculates on all the expense centers (even the ones I don't have access to).
A couple of assumptions first, then a couple of thoughts. I would be glad to spin up a quick example as well, once we can confirm I'm up-to-speed on the requirements here.
Can you please let me know if I missed or mis-stated anything here?
1. You want the ability to rank all Cost Centers, based on the variance line item value, including Cost Centers that you do not have access to.
2. You want the end user to be able to view all of those ranked Cost Centers
3. You *do not* want users to be able to see the variance line item values (the values on which the ranking is being down), and would want selective access to only enable them to see values for Cost Centers to which they have access
Here's a visual representation of the assumptions above:
Assuming I'm on the right track above, you could:
Create a Cost Center list (let's call it CC2) that mirrors your main Cost Center list (CC1). While CC1 has selective access applied, CC2 does not. You can pull the data used for ranking purposes into a module that applies to CC2, from a module that applies to CC1. Then, use Dynamic Cell Access to control access to senstive information presented in the CC2 module.
You can remove selective access from CC1 entirely, and use Dynamic Cell Access to manage access to data associated with CC1.
Option #1 is less impactful and should be a straightforward workaround. Option #2 would only make sense if you have multiple areas of the model where access needs to be managed at a more-granular level than at a list level.
Let me know whether or not the above makes sense. If it does and you're interested in an example, just DM me a copy of your module blueprint and I can spin something up for you.
Not quite on the assumptions. I want the ranking to be done based only on the cost centers to which the user has access. So, in your example, let's say user 1 has access to A, B, C, G, and H and user 2 has access to I, F, D, C, & B and I want to display the top three values on a dashboard. I would want user 1 to see C, B, and A while user 2 sees I, F, and D. They should only see the cost centers and associated data to which they have access.
I was able to get to the point where I, F & D would always display. But for user 2, since they don't have access to any of them, they see nothing on the grid. We ended up just displaying a grid sorted so the top variances are on top, but all cost centers (to which the user has access) are displayed. This works, but the original request was that each user could go in and see their top 10 variances.
I think some user level filtering, possibly with some Dynamic Cell Access would work.
For example, if you created a module with just the Cost Center and User Lists, as well as a single boolean line item that indicated what cost centers each user had access to (this could take different forms, but ultimately would have to end up at the Cost Center Level)... you can then let the user select their own current period, bring in the data for that period (if they have access), and see the descending top 10 from there (by letting the user set the period they want to see, the sort order module/setting will have a better change of being smaller).
I don't know the related list sizes (# of users, and # of Cost Centers), so its difficult to know how big this would be. Also, it could be that the current time period is set centrally for all users... which would make things easier.
Paul's right. If you maintain the users access to cost centers in a module (which you should), you can use that to have your ranking module with the user dimension and basically set to 0 CC they don't have access to.
DCA doesn't help here though. Even with DCA, calculations do happen, even if you don't see them.