Dashboard Chart to Match Current User Report

Claire Owen-Schubnell · 09/22/20

I've set up a current user report that accurately lists all project submissions for each user when viewing the dashboard. I'd like to include a corresponding pie chart to visually indicate the number of projects each user has per status (in progress, pending feedback, on hold, etc.); however, the current user filter either does not work (pie chart shows the number of all projects per status, not the number of projects listed in the current user report as shown in screenshot) or the widget displays "widget results are empty for you" when I test it with a colleague.

I've tried every possible report and configuration I can think of and made sure other users have the appropriate permissions and access.

What type of report do I need to set up to create this? Do I need to reconfigure my master project intake sheet or the portfolio metrics sheet? Is this only possible through Smartsheet Dynamic View as a premium add on?

MCorbin · 09/23/20

You need to create a metrics sheet.

Have your users in the rows and each column is your status (Reviewing Request, etc)

The formula in each cell would refer back to your raw data sheet:

=COUNTIFS({Requestor}, $[User Name]@row, {Status},Review$1)

(note: If you use the first row for your status values, you can create the formula in 1 cell, then copy it across all the columns and down all the rows)

(The user name should be a contact column)

Then...

Create a report that looks at this Metrics Sheet. Set the report to filter on Current User for the User Name.

Then... Create your chart in your dashboard that looks at that report. Set the Advanced Options in the Chart to show for "Dashboard Viewer".

That will get you a dynamic graph that updates for each viewer who is looking at your dashboard.

MCorbin · 09/23/20

You need to create a metrics sheet.

Have your users in the rows and each column is your status (Reviewing Request, etc)

The formula in each cell would refer back to your raw data sheet:

=COUNTIFS({Requestor}, $[User Name]@row, {Status},Review$1)

(note: If you use the first row for your status values, you can create the formula in 1 cell, then copy it across all the columns and down all the rows)

(The user name should be a contact column)

Then...

Create a report that looks at this Metrics Sheet. Set the report to filter on Current User for the User Name.

Then... Create your chart in your dashboard that looks at that report. Set the Advanced Options in the Chart to show for "Dashboard Viewer".

That will get you a dynamic graph that updates for each viewer who is looking at your dashboard.

Claire Owen-Schubnell · 09/24/20

This worked! Thank you so much!

MCorbin · 09/24/20

Yay! Glad to hear it!

Claire Owen-Schubnell · 09/30/20

Hi MCorbin,

Thanks again for your help! Can you advise on how I can adjust the formula in my metrics sheet to account for projects with 2 contacts in the requestor column?

Currently, I have 1 project in progress and the requestors are John Doe and Jane Doe. When I check the individual rows in the metrics sheet, the In Progress column shows a value of 0.

Dashboard Chart to Match Current User Report

Best Answer

Answers

Categories