Dynamic Dashboard Template Customization

I'm new to Smartsheets and learning by doing.

Currently I'm working with the Dynamic Dashboard template to create my own Dashboard that filters for a given faculty last name, then pulls key details about them from a larger Faculty Information Sheet.

I've worked out how to set a form that has a dropdown menu of all of their last names, and how to use a report to select the current filter and report that back out to the dashboard.

I see that there's a sheet in the Dynamic Region Metrics of the Dynamic Form Template that seems to be able to auto refence the current filter to pull data, but that's geared towards pulling statistics, whereas I want to hunt down checkboxes and dates, as well as text. Does anyone have any tips? I've attached a quick mockup of the dashboard I want to create and a scrubbed version of the faculty data sheet info I'm trying to get to.

Tags:

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    Hi @Rose Howard

    In the demo dashboard below, I customized the template to add a filter to select a project name and show the selected projected row data. (In your case, the Project Name would be the Last Name.)

    To customize the template, I did the following.

    First, I added one sheet and two reports.

    Those three are equivalents of one sheet and two reports for the original template's region filter.

    The Active Project- Project Filter uses the Project sheet's Max Row Helper column (As shown below) to extract one row to show as a "Filtered Project", as shown in the above dashboard. (The report directly uses the Portfolio Summary sheet's "Project Helper" column, but the column is updated with the Max Row Helper.)

    The "Current Filter - Project" report shows the current filter for the project choice, as shown in the top right corner of the demo dashboard.





    This "Project" sheet has an identical structure to the Region sheet.

    Its form is attached with a web widget in the top middle of the above dashboard.



    In the "Portfolio Summary" sheet, I added the "Project Helper" column with a similar formula as the Region Helper column.

    =IF([Project Name]@row = "Summary", "", IF([Project Name]@row = INDEX({Project Range Project}, MATCH(true, {Project Range Max Row Helper}, 0)), 1, 0))

    So, the whole process is as follows.

    1. The form in the dashboard adds a project name to the Project sheet.
    2. The new row will be flagged in the Max Row Helper column.
    3. The corresponding row with the same project name's Project Helper column got flagged.
    4. Two reports for a project use the flag to filter a project.
    5. The dashboard displays those filtered reports.


    Current User Approach

    Since you know how to use a report to select the current user filter, you just have to create a report with relevant data with the current user filter. (Make sure to set the Viewer Mode to "Their own perspective"

    When I log in as "App".

    When I log in as "Demo".