Hi all,
I have a report that is filtered by business unit within the company. The business unit hierarchy is a ragged hierarchy. There is also row level security (RLS) on the business units so users can only see the business units they have access to.
The requirement is to have the report open up for a user and their default view is showing 1 level below their default business unit in a stacked column chart, where the business units are on the x-axis. Then from this, the user can drill down on a specific business unit to see more details in the chart.
For example, the business unit table is structured as so:
| Level 1 | Level 2 | Level 3 |
| A | B | C |
| A | B | D |
| A | E | F |
| A | G | |
(this is much more simplified than our actual hierarchy, where there are many more levels and values)
With the RLS, for example if they only have access to C and D, then the user could still see the parent B and A, but it will only sum C and D at those upper levels. We don't want the user opening the report with it at level A, which would only sum C and D with RLS, but they think it's the true value for A, when it truly is not since A has other children but the user just can't see them.
I have 2 possible ideas:
1) I was thinking of having a table I would manually maintain for all the users and their default business unit:
| | Default |
| User 1 | A |
| User 2 | B |
| User 3 | G |
So that when User 1 opens the report, they should see in the x-axis: B, E, and G. When User 2 opens the report they should see C and D. When User 3 opens the report they should see G since there is nothing lower than G.
Then I need to somehow make the fields I'm using in the x-axis of the column chart to dynamically change based on who is viewing the report. What I mean by this that User 2 sees C and D, so this is Level 2 in the first table. So I can't have Level 1 as a field in the x-axis as it won't show that one level below. And User 3 sees G which is Level 3, so I can't have Level 2 as a field in the x-axis for this user. I need the fields to dynamically update based on who is vieiwng the report.
I was thinking of using the function USERPRINCIPALNAME() to get who is viewing the report, then use LOOKUPVALUE() to find their default business unit from the manually maintained table, then show only those business units and children business units in calculated columns, but this function USERPRINCIPALNAME() can't be used in a calculated column. So I don't know how to dynamically change the calculated columns based on who is viewing the report.
Any thoughts on how to make the x-axis dynamically change? Could I somehow use field parameters? I'm not sure how I could apply those here though.
2) Another possible simplified solution, is that I just have all the business unit levels in the x-axis, then when a user opens the report for the first time, they see the top parent A (even though they should see their one level lower than default). But then I train them to drill down to their correct level using the drill down function, and then teach them to save that view as a default personal bookmark, so that it will always open up to that one level down no matter how they modify the report afterwards. Not an ideal user experience to have to set it up initially though.
OR
I manually modify the report for each default view, then get a "Share" link for each one and send those links out to the correct people. It's a bit of manual work unless I can automate it with Power Automate or some program. Keep in mind, this example was showing 3 users, but realistically there will be many users and many unique views.
Any thoughts on this, or if you've done this before? Any new ideas or inputs are greatly appreciated.
Thank you in advance!
