Filtering on dashboards?

dlnvtl

I'm new to smartsheet and trialing it at the moment. I have a question, I hope you can help me with:

I have a portfolio of companies in a main sheet and their debt issues, and related stuff in several sheets. Rows are companies/debt issues, columns are details of these. I want to collate all this into the dashboard used the different widgets - a dashboard on a _per company_ basis. So, a dashboard showing all the company info, financials, debt etc in one dashboard.

As far as I can see, normally you will use the dashboard to present aggregated information. I need to show filtered information, filtered on a per-company basis.

I am wondering if there is a way to change the contents presented in the dashboard, based on dropdowns, filters, settings, etc. - and this has to be something that is user-friendly, so not altering values in some remote area of a sheet. Ideally, I want a dropdown on the dashboard, which basically allows the users to select the company they want "dashboarded" information on. 

I don't think I can do this in a report function since I need to show graphs. Only dashboard does graphs. 

Any help is greatly appreciated, I have been over the documentation, community, googling etc. and can't seem to find a way around this.

Andrée Starå

Hi,

Welcome to the Community and the wonderful world of Smartsheet!

There are many ways to structure a Dashboard/Portal. Here are a few examples.

1. A Portal with links to all the companies individual Dashboards
2. A Dashboard with an overview of all companies that you then can drill down to the different companies Dashboards
3. A selectable drop-down that could filter what data to show in the Dashboard but it would have to be reloaded after each change.

Would any of those examples work/help? Which one would you prefer?

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

dlnvtl

Hi again,

This sounds very promising, thank you for responding.

I have been thinking about your options and, to be completely honest, I don't know. I am not really able to evaluate the options. On the other hand, I don't want you to spend hours writing detailed answers for all three options. I think the best way forward, is perhaps me giving a bit more detail on what the situation is, perhaps you can recommend an approach:

- I won't update the data that often, probably on a weekly basis, and only small tweaks. I think that means #3 is still in the game?

- I need to show aggregated data on one dashboard, and i need to show the company slide we discuss here (pulling from three sheets) on another dashboard. That appeals to #2.

- The users are not that tech-savvy, so the solution must accommodate that

- The list of companies changes over time as we invest and divest, the solution should be able to handle that automatically. I think that rules out #1?

I think #2 is the best solution for me, but it's more a hunch.

Many thanks for your time!

Paul Newcome

In regards to #3...

 

I have built out a similar solution but used a form for the selection. The details were a little different, but the concept is the same.

.

I started by building a very bare bones sheet with a form that has dropdowns for each of the different variables that could be selected by everyone. On this sheet, I set row 1 to pull the data from the most recently entered form submission.

.

I then built out my metrics sheet.The table was built out to reflect the things that would not change. In my case it was dates within a range. The variables that COULD change were cell linked from the top row of the Form Sheet. I then referenced these linked cells in my calculations.

.

From there I built out my dashboard starting with the metrics displays.

 

I then imbedded the form on the dashboard using a Web URL widget.

 

Then go back into the form and set it to take you to a specific url after a form is submitted and use the dashboard url.

.

People could simply select their variables from the for and click submit. The form would essentially force a reload of the dashboard which would in turn update all of the underlying sheets which in turn updated the data displayed on the dashboard.

.

Reading through it, it seems like a complex way of setting things up, but really the most complex part was building out the actual metrics formulas.

 

If you give the build a quick run through, I bet it ends up being a lot easier to setup than it sounds.

Andrée Starå

Happy to help!

It sounds like all options could work, but I agree that #2 seems to make the most sense because of the flexibility and if needed, it's easy to combine it with #1.

That said. Paul's suggestion below is also an excellent solution, and maybe the best solution would be a combination of all of them. 

Let me know if you have questions!

Good luck!

Paul Newcome

The biggest drawback I have found (so far) to my solution is that because you are using form entries to drive what data is shown, heavy traffic will run up your row count and could cause viewing issues if multiple people are submitting requests at the same time. That second part being a common issue for ANY collaboration really.

Andrée Starå

Did you get it working or do you still need help?

Have a fantastic week!

Best,

Andrée

Rachel Heard

Hi, @Paul Newcome. Do you have an example video or screenshots for how you built option 3? My organization is interested in using this workaround but I'm having a difficult time wrapping my head around these ideas.

Paul Newcome

@Rachel Heard Are you able to provide some details regarding your setup and processes? How many variables do you want users to be able to search/filter on? Are you able to provide a screenshot of the sheet that you will be searching/filtering with sensitive/confidential data removed, blocked, or replaced with "dummy data" as needed?

Rachel Heard

@Paul Newcome We are in the beginning phases of building our Lessons Learned Repository. There will be hundreds of Lessons Learned within the past 12 months but as of right now we have not inputted any of this information. When the build is complete, we would like to filter the report on the dashboard (pictured) by project name, primary category, and other category. The project name would need to be a text box input, primary category can be a drop down, and other category would need to be a text box input. I understand how to embed the form into the dashboard once it is created but I do not understand the logic of how it filters the report on the dashboard.

Paul Newcome

@Rachel Heard Here's the basic rundown of how it works...

First create a sheet ("Filter Request") that contains the columns you want to filter by as well as a system generated [Created Date] column.

Set up the columns you want to filter by how you want them to be used for the filter, so free text fields would be text/number type columns, and the dropdown selection you want to filter on would be a dropdown type column with the selections prefilled in the column settings.

Then you would create a form that simply has the fields set as the columns you want to filter by.

From there you would go back to the original sheet and add in a checkbox type column. This checkbox column would contain a formula that would first pull the data from the most recent entry on the "Filter Request" sheet and compare it to the data across those fields in the row.

It would then check the box if the row meets the criteria input through the most recent form.

Then your report/sheet could have additional criteria/filter built into it that accounts for this new checkbox column being checked (meaning the row meets the filter criteria).

The user would submit the form then refresh the browser (instruction that can be put on the dashboard or in the "custom message" after submitting the form) which would force-refresh everything else connected and the report/sheet will be updated and the form will be reset for a new filter request to be entered.

This does have the drawback in that it will only show THE most recent request and not necessarily YOUR most recent request. This also will need some maintenance to delete rows once you start approaching the 5,000 row limit on the request sheet.

Rachel Heard

@Paul Newcome

Thank you for your explanation! Would you mind providing a sample formula that feeds the checkbox column?

Paul Newcome

How many different columns will you be filtering by and what are the column names? Do you have access to the Sheet Summary fields on the sheet you want to apply the filter to?

Rachel Heard

We are filtering by three columns (if possible): Project Name, Primary Category, Other Categories.

Yes, I have access to Sheet Summary.

Paul Newcome

Ok. On the filter request sheet, you would set it up to have those three columns plus a system generated Created (date) column. Set the form up to include the three searchable columns and have the form populate new entries at the bottom of the sheet.

On the data sheet, you would create 4 new fields in the Sheet Summary: Row, Project Name, Primary Category, and Other Categories.

In the Sheet Summary Row field, you would enter

=COUNT({Filter Request Sheet Created Column})

This will give you the count of the last row populated (which is the most recent request) and will be used to pull the data from that row to your sheet for the filter.

In the Sheet Summary Project Name field:

=INDEX({Filter Request Sheet Project Name Column}, Row#)

Sheet Summary Primary Category field:

=INDEX({Filter Request Sheet Primary Category Column}, Row#)

Sheet Summary Other Categories field:

=INDEX({Filter Request Sheet Other Categories Column}, Row#)

Then you would create a checkbox type column with the following formula:

=IF(AND(FIND([Project Name]#, [Project Name]@row) > 0, FIND([Primary Category]#, [Primary Category]@row) > 0, FIND([Other Categories]#, [Other Categories]@row) > 0), 1)

Finally you would create and activate a filter that only shows rows that are checked.

Then you can display the Filter Request Form and the sheet you are filtering side by side on a dashboard. The user would then fill out the form and submit it then refresh their browser/the dashboard.

If you were fine with people access the Sheet Summary fields and updating those themselves, you could skip the step of creating the form and just instruct people to open the sheet summary, enter their search criteria, save the sheet, then refresh the browser. Saves a fair amount of setup and removes the Sheet Summary Row field.

That is going to depend though on whether or not you want people to have direct access to the sheet or not.

Lajaun Case

@Paul Newcome

I am extremely new to Smartsheet.

Mine is not as complex. I only have one field that I want to be able to filter with on a dashboard.

I was trying to adjust what you had provided above to fit mine. But, I am still struggling with it.

Please review what I did in my screenshots. Hopefully, I am providing enough for you to assist me.

Thank you in advance for your help.