Filtering on dashboards?

dlnvtl
dlnvtl
edited 12/09/19 in Formulas and Functions

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.

«134

Answers

  • Andrée Starå
    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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • dlnvtl
    dlnvtl
    edited 08/20/19

    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
    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å
    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. wink

    Let me know if you have questions!

    Good luck!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    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å
    Andrée Starå ✭✭✭✭✭✭

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

    Have a fantastic week!

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • 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
    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
    Rachel Heard ✭✭
    edited 02/25/20

    @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
    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.

  • @Paul Newcome

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

  • Paul Newcome
    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?

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

    Yes, I have access to Sheet Summary.

  • @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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!