Drill into a dashboard

Lashawnta Barker

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

Paul Newcome

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

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

Lashawnta Barker

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å

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!

Lashawnta Barker

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

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.

Lashawnta Barker

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

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

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.

Lashawnta Barker

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

Thanks

Paul Newcome

@Lashawnta Barker Happy to help. 👍️

Christos

Is Chart Drill-Down functionality available yet?

Are there any plans to add it as a feature?

Mary Clark

@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

@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)))