Dashboard - Variable Applied to all reports, graphs etc

Brett Holmes

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

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. 👍️