Filter function in Dashboard

MSGFreebird

I am building a dashboard using a report for the data source. I do not want the dashboard viewers to have to click on the widget and open the report to manipulate the filters. What I need is a way for the viewer to filter the data in the dashboard. Is that possible?

Matt Johnson

Hi @MSGFreebird,

Smartsheet does not currently have a slicer feature on Dashboards, but I think its on the roadmap. I've seen some work arounds but nothing super flexible like PowerBi.

However, you CAN filter a dashboard and/or reports by Current User to show only tasks that are assigned to them.

Another thing to consider is that sometimes not being able to filter dashboards can be a good thing. I've been on calls when people are trying work out discrepancies only to find out that the filters weren't set the same. Sometimes creating a lot of reports, accessible from the dashboard, allows everyone to pick from the same bucket of pre-filtered reports. Maybe one report embedded on the Dashboard and below it, shortcut links to various filtered views of the same data. At-Risk Tasks report and below it, At-Risk Tasks by Department, At-Risk Tasks by GC, At-Risk Tasks by Due Dates in the next 5 days grouped by PM, and so on.

Definitely not the answer you were looking for but I hope it helps.

Matt

Don12345

@Matt Johnson Hi Matt! I am having trouble finding how to filter a dashboard based on current user viewing it. The usual filter button is not showing up at the top like it does for sheets. Can you explain where to find this?

Thank you in advance,

Don

Matt Johnson

Hi @Don12345

Great question because, on a reread, my statement is slightly confusing becuase filtering the dashboard isn't exactly what you'd expect. If you have a report that is filtered by the current user, when you go to embed that report in the dashboard you set the "Viewer Mode" to be "their own perspective" so that the report is pre-filtered by user. So basically it just makes the report work for what's supposed to do but the whole dashboard isn't exactly "filtered" by the user. I hope that all makes sense.

Have a great day. -Matt

Don12345

@Matt Johnson Yes, makes sense! I was thinking there was a way to filter the graph widget based on their own perspective. I was hoping that if the sheet that the dashboard pulls from includes a current user filter, this would transfer over to what is seen on the visualizations shown on a dashboard.

Thanks!!

Matt Johnson

@Don12345

Here's something I do sometimes to make graphs and charts specific to the Dashboard. I usually use this for Department or Team Level but I think it would work for Assigned to values as well.

On the metric sheet, I put a column on the far left called Filtered By or something like that and highlight the first cell as a reminder. Usually its a dropdown column but only that first cell matters. All the formulas on that metric sheet have that value incorporated in them. Then, after each project is created I have an update request or alert remind me to go to the metric sheet and change that 1 cell so all the graphs change with it. I even incorporate that cell into the dashboard title by (instead of using a title widget) aiming a metric widget at that cell or another cell that uses that cell like =thatcell+" - "+ Dashboard. That way I always know for sure I switched that cell to the new value.

I hope that helps.

Matt

Monster Man

@Matt Johnson

Matt I am intrigued by the approach you describe. Can you give an example of the values you use in "thatcell" and how you use that in metric formulas?

Thank you,

-Mons

Matt Johnson

Hi @Monster Man

Sure thing. So say your main tracker has a column for region (North Pole and South Pole) ⛄️ and you want a Dashboard for each that are the exact same. On your metric sheet, you have a cell where you choose between the two. Then all of your formulas have the value in that cell incorporated into the formula.

Example: =COUNTIFS(Region from Main Tracker, $Region$1, Status from Main Tracker, "Behind")

So if I choose North Pole, then that counts all the Behind Tasks in the North Pole. Then I save as new on the whole folder (main sheet, metric sheet, and dashboard), rename a few things, flip the Region cell to South Pole on the metric sheet, and now I have a dashboard for each Region.

I hope that helps.

Matt

Jillian Cadorette

Holy cow, @Matt Johnson . I would love to learn more about this solution.

I have one giant intake form. From that I slice the data various ways. There are many set reports that I then filter for different dashboards. That means that if I need to update something universally (like unhide the date column) or change the order of the columns, I need to go into 70ish individual reports to make that change throughout. I spelled a heading wrong in one of the dashboards and had to go through and change it in 10ish spots. I know more efficient methods exist!

Matt Johnson

Hi @Jillian Cadorette

It sounds like you got a great thing built, but starting to reach the end of it being a manageable thing. I'd be super excited to help on this one. Hit me up on LinkedIn and let's talk more. Or we can bounce ideas around on here but you'll reach a broader audience if you start a new thread/question. But let's connect, a quick 30 minute call can save so much time. That goes for anyone reading this thread too.

Have a great day!

Matt

Kadie Butterfield

@Matt Johnson you are a ROCKSTAR. This has been a HUGE help for me with an employee dashboard for HR to review for each employee.

Thank you for sharing this!!

22Wordsmith

@Matt Johnson re your filter approach, how would you manage it with the following variables, please:
12 managers
12 agencies
33 departments
5 themes
4+ reporting cycles

I started with all the managers, agencies, departments and themes on one metric sheet for one reporting cycle but now I've been asked to make it possible to see any reporting cycle from a single dashboard.

TIA.

Matt Johnson

@22Wordsmith

Once it gets that dynamic I would use PowerBI for the dashboard. It's overwhelming at first but put a day of learning into it with YouTube videos and you'll be fine. Smartsheet will still be the data source. And you can embed the PowerBI dashboard back into a Smartsheet dashboard and everything can "stay in Smartsheet".

22Wordsmith

Thanks for that advice, @Matt Johnson