Filtering on dashboards?

Filtering on dashboards?

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.

Previous1

Comments

  • 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

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • 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 NewcomePaul 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!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Paul NewcomePaul 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

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • 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 NewcomePaul 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?

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    edited 03/02/20

    @Lajaun Case

    Try making these adjustments...


    Sheet Summary: Category#

    =INDEX([Project Requests Metrics Sheet Range 3}, Row#)


    Sheet Summary: [Filter Checkbox]#

    Remove


    Main Sheet: [Filter Checkbox] column

    =IF(FIND(Category#, [email protected]) > 0, 1)


    EDIT to tag Lajaun.

  • @Paul Newcome

    Thank you so much for your help. I have everything working except on piece. On the Sheet Summary, I have Row as =COUNT({Filter Requests})

    But, when I put a new entry on the form, it does not look at cell 1 for the status. It continues to pull the previous value which is now in cell 2. (Even if I refresh the page. It is like it is hard coded to the previous value.)

    On the Sheet Summary, if I "Edit Reference" the Row field and click on the Filter Requests sheet, Status column, row 1 cell, then it updates and all of the checkboxes update. How to do force the Row field to look at the first cell only?

    Thanks again!!!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Lajaun Case If you are trying to pull the top row only, then you would not use a COUNT function in it's own cell. You would adjust your INDEX function in the Status field to

    =INDEX({Other Sheet Status Column}, 1)


    This will hard code the number 1 so that it always pulls from the first row.


    Does that help?

  • @Paul Newcome

    I got it working!!! I wouldn't have been able to do it without your guidance. Thank you so much for taking the time to assist!!!!

    Lajaun Case

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Lajaun Case Happy to help! 👍️

  • @Paul Newcome - I read through the thread and was wondering if you can assist me with my similar requirement? Should I start a new thread or add this? What is you preference? My requirement is a bit different as I would like users to be able to choose the project type from my master schedule.

    1. Master Schedule has up to 100 project types they can choose from? lets look at an example like. "CORE Implementation" Project type.
    2. There are standard tasks that I want included in all project plans. These are repeatable tasks like welcome call or kick off etc. No matter what project type the user chooses I want these standard tasks / sections to always display.
    3. I want users to be able to choose the project type i.e. CORE Implementation
    4. I want user to enter a go live date
    5. I want the standard tasks to display for the user
    6. Once steps 3,4 and 5 are done, I only want the information related to thee tasks to display.


  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Harley Esguerra I think your request is different enough to warrant a new thread. Please feel free to @mention me so I get notified of it, as I do think I might have a couple of ideas. If I understand what you are asking correctly, I do something very similar, but I have just under 20 services that could be mixed and matched for an implementation.

  • @Andrée Starå I'm interested in your #3 from the first option up top. Any chance you could provide details on how to do a drop-down filter? Totally fine to reload after every change.

  • Hi @Paul Newcome, I am new to Smartsheet.

    We are trying to build a centralised activity calendar that could be shared between faculties and central departments. I am trying to adapt your solution above to what I am trying to build.

    Basically, I have a data sheet ("Intl Engagement Calendar 2020 with Form") with all the information we would like to capture with regards to an activity (ie dates, month, country, activity type, purpose of activity, faculty required etc). I have set up the Summary Sheet in my data sheet, created a Filter Request sheet as well as a filter request form.

    This is how the Filter Request Form and the sheet that I am filtering is displayed (side by side) on my dashboard.

    My questions are:

    1. Is the checkbox column (Column31) meant to show "#BLOCKED"?
    2. When I fill out the form and submit it and then refresh my browser/the dashboard, nothing happens. The sheet still looks the same.
    3. What am I doing incorrectly? Any advice you could give would be much appreciated. Many thanks!
  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @belz

    The #BLOCKED error means there is an error somewhere else that is preventing the formula from running. Work backwards checking all ranges referenced in the formula in [Column31] looking for an error. Let me know what you find.

  • Hi @Paul Newcome , thank you for all of your help so far. We had to switch gears due to covid, but are starting back up on our dashboard listed above. Is it possible to filter a report or only possible to filter a sheet with the option given above? We have multiple sheets going into a report and would like to filter the report. If it is not possible to filter a report with many sheets are there any other alternatives we can look into? Thank you!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Filtering can only be done at the sheet level. To "filter" a report, you would need to adjust your criteria in the Report Builder.


    What exactly do you want to filter your report on?

  • Please see above responses in this thread from 2/25/20-3/2/20. @Paul Newcome

  • We are wanting to use the dashboard "filter" workaround to filter a report on the dashboard when the page is refreshed.

  • @Andrée Starå - I quickly scanned this thread and did not see the answer the original 3rd options. "A selectable drop-down that could filter what data to shown on the Dashboard...". My use case - I have multiple workstreams in the same plan. I am building a status dashboard which will pull in several types of data but let me just use two for this use case: plan data and risks and issues. I want the drop down list at the top of the Dashboard to be "visible" to other report objects on the dashboard page to pickup the workstream filter and re-render just downing plan data for the a given workstream and the risks/issues data for the workstream. This way I ONLY have to build a SINGLE Dashboard rather than 9 dashboards of the exact same type which results in a bunch of re-work if there are desired changes to the main structure of the dashboard. Is this possible?

Sign In or Register to comment.