Dashboard - Variable Applied to all reports, graphs etc

Hi I have a requirement to report on the current data for a period and using a Dashboard makes a lot of sense except that since I have to analyse the data every week I need to redo the report each time I want to change

It would be great if I could create a variable (1 or more) that can be applied to the incoming smartsheet date for reports, graphs etc) so that when I change the variable (perhaps user changeable via a drop down box) the Dashboard would automatically have all the correct data as per that variable -

e.g.

MyVar1 = User1,User2,User3

MyVar2 = Qtr1,Qtr2,Qtr3

When the user select User2 and Qtr3 you would only see data from the reports that match that criteria and they you could select User1 and the data would change in the dashboard

thanks

Find more posts tagged with

Reports

Dashboards

Enhancement Request

Comments

Chris McKay

Hi Brett,

This is possible, but I'd need to know more about how your sheets are structured.

I'd look to create a checkbox control column in your sheet(s) that will flag true or false if the row meets a certain condition:

=IF(AND(Date1 >= [StartQtr]1, Date1 <= [FinishQtr]1),1,0)

You could then have a dropdown that has Qtr1 -> Qtr4

Then 2 cells that populate with a [StartQtr]1 and [FinishQtr]1 based on the value in the dropdown.

Brett Holmes

Hi Chris

Thanks while I could do this it still creates a static report from my perspective

I was thinking of having all opportunities for the last year in a smartsheet and then be able to pick the quarter and the sales person I wanted review in the dashboard and then when I wanted to see another salesperson I would pick that from a drop down on the dashboard because this would give me a high level report that I can then pick what is in the dashboard

Chris McKay

Hi Brett,

Not a problem. I don't think dynamic reports or dashboard dropdowns/filters are on the radar for Smartsheet (but who can tell right?) so for the time being this is probably the best you can hope for.

Yana Rolnik

I add my vote for this enhancement as well. I would like to have ability to modify parameters for reports/dashboard and it would dynamically regenerated.

Right now I need to modify reports to indicate which sheets to include every time new project sheet is generated. We generate 3 new project sheets each month, so would be nice to be able to have an ability to just dynamically increment project # and include corresponding sheet in status report.

Allison Jones

I vote for this issue. It would be great to create one dashboard for a manager that they can toggle between their sales reps or regions

Hawk

I vote for dashboard can be dynamical/interactive.

Andrée Starå

Hi everyone,

Please submit an Enhancement Request when you have a moment to have your vote added

A New Way to Submit Your Feature Requests

To make your Enhancement / Feature Request count, send in the form above because there isn’t a guarantee, it will be registered otherwise.

Original Post: https://community.smartsheet.com/announcement/new-way-submit-your-feature-requests

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

D@n

I agree with the above comments. This is a massive shortcoming for SmartSheet reports. Will suggest they add it using the link, but unfortunately I need a reporting solution now and it doesn't look like SmartSheet will be the answer.

Paul Grim

I submitted the enhancement request as this feature is noticeably lacking the very first time you build a report. I can't understand why since filters are already available in grids so why not in reports as well? Reports are just another view of the data, so this one looks like a swing and a miss. 🤔

Paul Newcome

I see a handful of specific scenarios above, some of which I can actually think of a few different solutions for. If anyone on this thread would like some ideas on possible solutions, feel free to let me know, and I will be happy to see what we can come up with to get a working solution in place for you.

Sandra Guzman

Hi there @PaulNewcome!

I have started my search for answers to see if there are any dynamic capabilities/features available for dashboards. What I would like to be able to do is allow for my internal stakeholders to select dashboard views based on month, quarter or YTD. Is there a way to accomplish this all within one dashboard?

The data we want to be able to see on a monthly, quarterly, or annually basis includes: Business Unit, Function, Number of Assets Complete, Resource Hours by Business Unit, etc.

Is there some Smartsheet magic that can be done to make this happen?

Thanks in advance for sharing your knowledge!

Sandra

Paul Newcome

@Sandra Guzman It is possible, but it may require a fair amount of modification. You are going to want to establish start and end dates for each of the different options and have those listed somewhere in the metrics sheet.

Next you would set up a second sheet and create a form that simply uses a single dropdown for the time frame selection and use a formula in a second column on this sheet (not included on the form) to replicate the row number.

Then go back to your metrics sheet and set up a basic INDEX function to pull the most recent form submission into a cell.

=INDEX({Form Sheet Selection Column}, MAX({Form Sheet Row Number Column}))

Next you build a nested IF statement that essentially populates the appropriate dates based off of the result of the previous step.

Then you can update all of your metrics formulas to only pull metrics for the specified time frame.

Finally you use a Web Content Widget to put the form on the dashboard.

User selects an option and submits the form. When they refresh their browser (or if there is a timer set to automatically refresh the dashboard) the metrics displayed will reflect the most recent time frame submission.

Or...

You could put your metrics sheet(s) and dashboard into a folder, save the folder as new, update metrics for each time frame and have 3 separate dashboards (one for each). Then include a Shortcut Widget on each of the dashboards that contains links to the other two. This to me seems like it would be an easier setup and easier for people to use.

Sandra Guzman

Thanks @Paul Newcome!

The first option definitely seems like alot of work to get done. I have been getting my practice in with INDEX/MATCH and nesting. If time permits and I start to feel like taking on a new challenge, I may go ahead and give that a try.

Your second option sounds like the winner here. Thank you so much for your creative solution suggestion. I really appreciate your help!

Best regards,

Sandra

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Trending discussions

Submitting forms for approval
Any help would be greatly appreciated! We have a 3-step process for travel approvals. I am able to get the forms to the correct people. I now have a request for the ability to add comments during the approval process. Staff submit request via Smartsheet form Automation forwards the form to the manager for approval Once…
Why would an automation for movement not carry over a cell value?
Hi everyone, I’m running into an issue with one of my sheets and was hoping to see if anyone else has experienced this. Here’s the setup: I have a sheet where, when I check a box, the row moves to a “Completed” sheet. In my Total column, I use a formula that sums a group of cells tied together via a parent/child…
Unable to edit smartsheet forms without constant reboot
Is anyone having trouble with smarthsheet over the past several days? Every time I go into a form to edit it, the cursor jumps fields after each character is typed. It is so frustrating. In some cases the cursor gets frozen in a field and jumps back and forth between two fields and freezes the whole system. I have yet to…