Can USER interact with a Dashboard to, for example, specify dates?

I want to create ONE dashboard to let user view project status for a given time period... but ideally we can have the user select/enter a start and end date to view data by... how? can this be done?

Or do i have to force-feed the user what I want them to see only?


Best Answer


  • Thank you, @Kelly Drake - yes... these tasks are trivial in Excel but seem to be impossible in SmartSheet. I just wanted to be able to build ONE Dashboard that would show a monthly snapshot of project status(es). But I didn't want to have to build one dashboard for Oct 2020, one for Nov 2020, one for Dec 2020 etc.

    I know I can build the dboard to show previous 31 days or 'current month' as variables from the referring reports... but then I'm still stuck with displaying only statuses for "now"... not enabling a view into any month in time.

    Ideally I'd be able to let the user select, say "October 1, 2020" and "October 8, 2020" and then the report would work as a WEEKLY summary, too! See how much more powerful this could be with just one very basic, very simple user-cap? Then I could build one dash... one report... and spend more time working Kakuros... ;)

  • @Kelly Drake = maybe I have to do an Advanced Option to link it to a source sheet... and then maybe I can have just the source be the date fields (?) and share that out universally... and then use those universally shared date fields to build my reports?

    But then I still have a problem, I think... because what if two users want to look at the Dashboard at the same time and refer to two different time periods? they would both be in the same referring source data cells, right?

    yeah... Week 1 of using SmartSheet and already wishing for a different platform.. ;) LOL

  • Kelly Drake
    Kelly Drake Overachievers Alumni
    edited 09/15/20

    @Catherine Hall Yeah the solution you're describing sounds more advanced that smartsheet's current features almost to the point where you need a relational database. Not sure what your environment is and how much data you're looking to use show but you could look at something like this....

    1. Create a form that will serve as a "lookup" that is just one field that has the dropdown options of your time frames. You could play around with making the field a multi-select to enable just weeks or if it only works for single select options
    2. In the sheet that your form responses drop add a system generated column for Created Date and then a formula column that contains the formula =IF(Created@row = MAX(Created:Created), "YES", "")
    3. In your sheet you would need a sheet summary field and a helper checkbox column. For the sheet summary field use the formula: =INDEX({form entry field}, MATCH("YES", {Form Latest Entry})). Then in your helper column you have the following formula: =IF([Week/month field]@row = [Sheet Summary Field]#, 1, 0)
    4. Build a report that has the helper column checked.
    5. Build out a dashboard portal that includes a URL widget for the form and then put your report in as a widget. (If you have more than 500 lines, Dynamic View removes the pagination of reports)
    6. The way people would interact with this would be to "fill out" the form. Click Submit. wait a few seconds and refresh the dashboard. The report should refresh with the items within that

    Things to keep in mind - this is a massive edge case that pushes the limits. If multiple are viewing the dashboard at the same time and editing the form, the set up will struggle because this only works really well with one request at a time. You'll also want to make sure to stress that people should refresh the dashboard and not just resubmit the form.

    Also - this solution could be overengineered if your data set is small/medium and you may be able to simply create a dynamic view and add time bound filters to end up at a very similar UX.

    Kelly Drake (she/her/hers)

    STARBUCKS COFFEE COMPANY| business optimization product manager

  • ... whew! Thank you for the really detailed explanation... since I'm only about a week into SmartSheet I feel like this solution is a bit over my head... so for now I'm just creating one dashboard and will manually update a time period each month (i've not found a way to create a report for "THIS MONTH" like I could for "TODAY"... so I'm just going to manually update every first of the month... for now...

    but yes... I think ultimately I'm wishing for more power... I will bookmark your solution and come back in a couple months when I have time under my belt in the system and more time to process (right now the PIs are expecting other results to be delivered yesterday!) :)

    again, THANK YOU, @Kelly Drake ! :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!