Dynamic Dashboards

Max Weinstein
Max Weinstein ✭✭
edited 11/06/24 in Formulas and Functions

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?

Tags:

Answers

  • ChristianFinke
    ChristianFinke ✭✭✭✭✭

    Hi @Max Weinstein

    You could try adding filters in your reports to separate data by each leader. This would let users who support multiple leaders see counts by category split out for each one.

    Another option is grouping data by leader in a single report, so users can view each leader’s counts on the same dashboard.

    Hope that helps! Let me know if you'd like help setting this or anything else up.

    CHRISTIAN FINKE

    cfinke@digitalradius.com

    Schedule a Meeting

  • thanks @ChristianFinke . If I group by leader and then category it only shows the full counts by leader. I can't see the category breakout. Is there a way to see multiple levels of groupings in a chart?

  • ChristianFinke
    ChristianFinke ✭✭✭✭✭

    Of course @Max Weinstein

    Currently, Smartsheet charts don’t support multiple levels of groupings directly within the chart view. To achieve a breakdown by both leader and category, you could create separate reports filtered by each leader and category, then add these as individual charts on the dashboard. This would give you a visual split for each category under each leader.

    Let me know if you’d like more help setting it up!

    CHRISTIAN FINKE

    cfinke@digitalradius.com

    Schedule a Meeting

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!