Drill into a dashboard

HI all.

I'm still new to Smartsheet. I have a stupid simple question.

How do I get my charts in my Smartsheet dashboard to filter the other widgets? I've poked around the help and youtube and it's never covered. I know it's elementary but I can't figure it out.

Help!

Thanks, SB

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    It isn't necessarily a hyperlink to FILTER the dashboard. It is a duplicated dashboard that has the underlying data updated for that particular "filter". The hyperlink is to the other dashboard.


    Example:

    You want to have a "Master" dashboard that shows the overall metrics for John and Jane. You also want to have a "filtered" dashboard that shows those same metrics but only for John and another but only for Jane.


    Create the "Master" dashboard. Put it and all underlying reports/sheets into a single folder.

    Save the folder as new.

    Update formulas in metrics sheets and reports to only show data for John.

    Save the folder as new.

    Update formulas in metrics sheets and reports to only show data for Jane.


    Now you can insert a shortcut widget on the "Master" that includes links to John's Dashboard and Jane's Dashboard. A widget on John's that contains links to the Master and Jane's, and then a widget on Jane's that includes links to the Master and John's.


    Depending on how your formulas and reports are set up, it shouldn't take too much tweaking in between folders.


    For example...

    If I know I am going to have a metrics sheet for Master, John, and Jane, then when I build out my Master Metrics sheet, I leave a cell open.

    =COUNTIFS({Source Sheet Status}, "Open")

    When I Save As New, I would update my formula to reference the Name column on the source sheet and the empty cell on the new sheet. Then I plug in "John" into the empty cell (lets just call that cell "[Column Name]1 for now) and my formulas will now work based on "John".

    =COUNTIFS({Source Sheet Status}, "Open", {Source Sheet Name}, [Column Name]1)

    The next Save As New for Jane would only need to have that one cell in the metrics sheet updated to say "Jane", and everything will switch over.


    It may seem like a lot of work for two filtered views, but if all you have to do is "Save As New", update a cell, then update a report, it can make duplicating the dashboard for 15 people a breeze.


    The trick is making sure that your metrics sheets are set up to adjust based on minimal input.

    I use "helper cells" in my metrics sheet to reference variables whenever I possibly can. For YTD reporting I would have a cell for the name and the year. That way I can just edit two cells and all of the formulas referencing the name and/or year for my metrics will be up to date. If I am pulling separate counts for Open and Closed, I'll have a column that contains Open in row 1 and Closed in row 2. That way I can write the formula in row 1 and then just dragfill.

    Don't be afraid to get creative with cell references when building out "templates" like this. It can make updating metrics sheets (and even reports) a lot easier if you plan on having to repeat everything multiple times.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide more detail and possibly some examples? Screenshots would also be helpful if possible.

  • Hi.

    Yes. Can I click on one of my green bars and have the other charts filter to reflect the status I click on? A simple thing really. But when clicking on "Advanced" for chart actions, none stick out to me to make this action happen.

    Thanks, SB

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Lashawnta Barker

    I hope you're well and safe!

    Unfortunately, it's not possible now to "link" one widget to another, but it's an excellent idea!

    Please submit an Enhancement Request when you have a moment.

    As a possible workaround, you could use a form to select what you'd want to view and refresh the Dashboard, and then everything would be updated to show the selected data. (quite advanced to set up). Another option could be to use a 3rd-party integration like Power BI or similar.

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Thanks for the reply.

    So you mean to tell me that Smartsheet allows you to create wonderful dashboards but you can't drill down into the data? WOW just wow. I have Tableau. I'll use that instead. This is definitely a request worthy enhancement.


    SB

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I usually build everything out for one and then drop the dashboard, reports, metrics sheets, etc. into a folder.

    I then save the folder as new, update a couple of links, and have now replicated the dashboard for the new set of metrics.

    I then make sure each dashboard has a widget on it that contains hyperlinks to the other dashboards.

  • Thanks Paul.

    So I can use a hyperlink to filter my dashboard? I'd like to have detailed instructions on how you set that up. Do you have any document you can send me or refer me to? I'm willing to try.

    SB

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    It isn't necessarily a hyperlink to FILTER the dashboard. It is a duplicated dashboard that has the underlying data updated for that particular "filter". The hyperlink is to the other dashboard.


    Example:

    You want to have a "Master" dashboard that shows the overall metrics for John and Jane. You also want to have a "filtered" dashboard that shows those same metrics but only for John and another but only for Jane.


    Create the "Master" dashboard. Put it and all underlying reports/sheets into a single folder.

    Save the folder as new.

    Update formulas in metrics sheets and reports to only show data for John.

    Save the folder as new.

    Update formulas in metrics sheets and reports to only show data for Jane.


    Now you can insert a shortcut widget on the "Master" that includes links to John's Dashboard and Jane's Dashboard. A widget on John's that contains links to the Master and Jane's, and then a widget on Jane's that includes links to the Master and John's.


    Depending on how your formulas and reports are set up, it shouldn't take too much tweaking in between folders.


    For example...

    If I know I am going to have a metrics sheet for Master, John, and Jane, then when I build out my Master Metrics sheet, I leave a cell open.

    =COUNTIFS({Source Sheet Status}, "Open")

    When I Save As New, I would update my formula to reference the Name column on the source sheet and the empty cell on the new sheet. Then I plug in "John" into the empty cell (lets just call that cell "[Column Name]1 for now) and my formulas will now work based on "John".

    =COUNTIFS({Source Sheet Status}, "Open", {Source Sheet Name}, [Column Name]1)

    The next Save As New for Jane would only need to have that one cell in the metrics sheet updated to say "Jane", and everything will switch over.


    It may seem like a lot of work for two filtered views, but if all you have to do is "Save As New", update a cell, then update a report, it can make duplicating the dashboard for 15 people a breeze.


    The trick is making sure that your metrics sheets are set up to adjust based on minimal input.

    I use "helper cells" in my metrics sheet to reference variables whenever I possibly can. For YTD reporting I would have a cell for the name and the year. That way I can just edit two cells and all of the formulas referencing the name and/or year for my metrics will be up to date. If I am pulling separate counts for Open and Closed, I'll have a column that contains Open in row 1 and Closed in row 2. That way I can write the formula in row 1 and then just dragfill.

    Don't be afraid to get creative with cell references when building out "templates" like this. It can make updating metrics sheets (and even reports) a lot easier if you plan on having to repeat everything multiple times.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Here is an example of a YTD metrics template I use. My formulas reference the year in $Lead$1. To filter based on "New" vs "Existing", my formulas reference PARENT(Lead@row). Then to filter down on each person my formula references $Lead@row.


    This lets me write the formula one time in [Total Go Lives]3, dragfill down to cover the rest of the people, then copy/paste that entire block down below. Now I am pulling 12 different data points, and I only had to write one formula.


    Lead1 is actually an incoming link from another metrics sheet within the same folder that contains that particular year.

    So when I go to use the template to build out my 2022 YTD tracking, All I now have to do is go to that one sheet, update the year, and then the cell links and whatnot trickle down through the rest of my metrics sheets and automatically updates everything. This saves me from having to manually update anywhere from 30 - 100 formulas on 25 different sheets and filters down through 23 dashboards and about 115 reports as well.

  • Thanks all for your comments. @Paul Newcome I understand your solution. I will have to give that a try.

    Thanks

  • Christos
    Christos ✭✭✭

    Is Chart Drill-Down functionality available yet?

    Are there any plans to add it as a feature?

  • Mary Clark
    Mary Clark ✭✭
    edited 08/09/22

    @Paul Newcome, This is awesome! I identify each row in my sheet by using "01/01/22", "02/02/22", "03/01/22" according to the month the request came in. Is there an easy way to figure totals for quarterly numbers as well? Thanks! Mary

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Mary Clark You would leverage the AND statement in your COUNTIFS or SUMIFS (or whatever other function you are using).


    =COUNTIFS([Date Column]:[Date Column], AND(IFERROR(MONTH(@cell), 0)>= 1, IFERROR(MONTH(@cell), 4)<= 3, IFERROR(YEAR(@cell), 0) = 2022))


    The above would count for Q1 2022 by looking for the month number being between 1 and 3 and the year being equal to 2022.

    You could also input specific dates if needed using the AND function alongside the DATE function.

    =COUNTIFS([Date Column]:[Date Column], AND(@cell>= DATE(2022, 01, 01), @cell<= DATE(2022, 03, 31)))