Options
Claire Owen-Schubnell ✭✭✭
09/22/20 edited 09/22/20 in Smartsheet Basics
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?
0 · Share on FacebookShare on Twitter
MCorbin Overachievers Alumni
09/23/20 Answer ✓
Options
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.
6 · Share on FacebookShare on Twitter
Answers
MCorbin Overachievers Alumni
09/23/20 Answer ✓
Options
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.
6 · Share on FacebookShare on Twitter
Claire Owen-Schubnell ✭✭✭
09/24/20
Options
This worked! Thank you so much!
0 · Share on FacebookShare on Twitter
MCorbin Overachievers Alumni
09/24/20
Options
Yay! Glad to hear it!
0 · Share on FacebookShare on Twitter
Claire Owen-Schubnell ✭✭✭
09/30/20 edited 09/30/20
Options
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.
0 · Share on FacebookShare on Twitter
'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("searchbox"+index); Coveo.initSearchbox( searchBoxRoot, "https://search.smartsheet.com/community/" ); }); $('#titleBar .CoveoSearchbox').hide(); $('.coveoSearchButton').on("click",function(){ $('#titleBar .CoveoSearchbox').toggle(); }); // ====== Front Page Search Box ====== $('#Form_search').remove(); var boxes = ["#search-0", "#search-1", "#search-2", "#search-3", "#search-4", "#search-5"]; console.log("PT: Before search box replacement"); $(boxes).each(function(i, val) { // ====== Front Page Search Box ====== $(val).each(function( index ) { console.log(this); $('#searchbox' + i).remove(); $(this).find('form').remove(); $(this).addClass('fpsearchbox'); $(this).attr('id', 'fpsearchbox' + index); $(this).append('
'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("fpsearchbox"+index); var action = "https://search.smartsheet.com/community/"; if ( $('body').hasClass('Categories')) { var category=$('h1.heading-1').text(); if (category === "Smartsheet Product Feedback & Ideas") { category = encodeURIComponent(category); Coveo.$(searchBoxRoot).on('buildingQuery', function(e, args) { args.queryBuilder.advancedExpression.addFieldExpression('@communitycategory', '==', [category]); }); action = action + '#&f:@communitycategory=['+category+']'; } } Coveo.initSearchbox( searchBoxRoot, action ); }); }); });
Categories
- All Categories
- 14 Welcome to the Community
- 59.3K Get Help
- 88 Global Discussions
- 93 Industry Talk
- 399 Announcements
- 12 Community Corner Newsletter
- 71 Brandfolder
- 121 Just for fun
- 43 Community Job Board
- 22 Member Spotlight
- 1 SmartStories
- 253 Events
- 13 Webinars
- 7.3K Forum Archives
Watch video
