Department specific Dashboards and manual updates to Reports


I have created department-specific Dashboards to make tracking department specific tasks easier for the end users. However due to limited knowledge, it's a very manual process each time a new site is added to the project timeline. I have to go into each report I've created for each department. How can I go about utilizing 1 report and sharing a filtered department view to their dashboards?

I don't mind adding the new sheet to each report but for a better automation experience for anyone who takes over adding new projects, I'd like to know if it's possible.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I would suggest a different approach assuming you are able to meet a few criteria here…

    Are all of the sheets that you are pulling into the report using the same naming convention? I understand each sheet may be named differently, but if it is something like "Project 1 Metadata Sheet" and "Project 2 Metadata Sheet"?


    Are all of these sheets within the same workspace?

    If you can answer yes to both (similar naming conventions and in the same workspace), you can reference the entire workspace (so that it automatically picks up new sheets as they are added to the workspace) and create a filter within the report based on the sheet name containing (for example) "Metadata".

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    Hi @Brandon R

    Below is a demo dashboard that uses a report's current user filter to show the tasks of the department to which the current user belongs.

    Key Features:

    Department Member List: This sheet maintains a list of all members of your department.
    Department Task Sheet: This sheet details all department tasks, including information about the specific department member assigned to each task.
    Department Task Report: This report displays tasks specifically assigned to your department. It leverages a current user filter, so you only see tasks relevant to your department.

    Behind the Scenes:

    The Department Task Sheet automatically retrieves department member information from the Department Member List using a formula that matches the department in the task sheet with the corresponding department in the member list. This formula utilizes the INDEX and MATCH functions.

    For details, please look at the published dashboard. (Since you can not log in to this dashboard, you see a blank report as it is set the Viewer Mode to the "their own perspective" view. The image below shows a report on the upper left corner of the current user = jmiyazaki)