Hello - I have a source data sheet with the below columns:
- leader
- category
- level
- access list
Access list is used for row level security and ties directly to leader. From this sheet I have 3 aggregated reports: 1 for leader, 1 for category, and 1 for level. The reports are also filtered by access list has any of Current User, so the reports only show what the user has access to.
I created a dashboard with charts from the aggregate reports. As an example, a chart for count by category, with viewer mode set as 'their own perspective'. So as an example, if the category aggregate report has data for leaders A, B and C, but current user only has access to leader A, their report and chart will only show data for leader A. This has eliminated the need for us to create separate dashboards by leader, as we can use 1 dynamic dashboard linked to user access.
This works great currently if the user only supports 1 leader, but it causes issues if they support multiple leaders.
Example: user supports leader A and B.
- Category 1 has a count of 10 under leader A and 6 under leader B.
- Category 2 has a count of 5 under leader A and 5 under leader B.
Right now the chart for this user would show a series for category 1 for a total of 16 and category 2 for a total of 10 without the ability to split by leaders A and B. Does anyone have any ideas on how I can split these charts by leader to show counts by each category under each leader separately?