Making a Dashboard from a Form

Ty Werven

Hello,

I am trying to connect a form all the way to a dashboard that will give real time updates and require no manual intervention once made.

The form is sent to a group of individuals that will eventually have to be separated out into separate units for which they work for. I am thinking of doing an automation that will take all data from the form and then send to separate sheets based on what they put as their "unit" in the form.

In the form, their are 4 questions that are yes (all), no (all), n/a(for 2), and other (for 1). I want to be able to track the monthly yes's and no's over a time span that will automatically update once the form is filled out. I have done a project that goes in this sequence (sort of) before. form>master data set>separate unit set sheet>metric sheet for each unit> dashboard for each unit. The separate units need have their own copy of the responses that their people fill out which is why I do not go straight from the master response sheet into a metrics sheet.

Is this possible to do while tracking the amount of yes's and no's for each month for each unit separately?

Jason Albrecht

Hi @Ty Werven

Thanks for posing this question to the community.

I'm going to anticipate that there will be quite a few questions to come, to help answer your question.

However, the answer is: it depends, but in theory, yes.

Now, here are some preliminary questions:

1. You've not mentioned using any reports in your explanation. Is there a reason for that?
2. You mention 'the separate units need [to] have their own copy of the responses...' Can you elaborate? Will they need to edit or add data, or are they just needing to manipulate the responses for their own reporting requirements? What else will they need to do with the data? What type of access are you expecting they'll have (admin, edit, view, etc.) to the unit's 'metric sheet'?

I'm sure there will be other questions from others in the community as well.

Ty Werven

Hi @Jason Albrecht

I would be open to using reports. Just do not have as much experience with them.

Yes, I work in a healthcare setting where nurses/ managers will be the one using the form. It is basically a huge audit for information in the hospital that each "unit" will do a couple times a month. The audit information will get put into the master sheet (ideally) where my team will get to see all the data. Each unit needs to be able to see their own data without doing lots of sorting or looking for their unit specific data (the hospital setting for nurses/managers is extremely busy and this is to help save them time/make their lives easier). This is why I would like each unit to have some sort of sheet that is only their unit information. If a specific unit sees a trend in their responses we want them to be able to go back to their own personal unit sheet/form and see who or what is causing the increase.

Basically, when I have made things like this in the past, I have only made the people I am sharing with it viewers. Lots of them will not have experience with Smartsheet's and I do not want them editing any of the data in anything besides filling out a form. This is why it is ideal to have some sort dashboard with graph trends that they can share at meetings for their entire unit, and some sort of personalized sheet that the managers can look deeper into to see any information that might be more useful (the response includes medical record number, nurse names, room names, and the 4 questions which is what I would like to display graphically).

I really appreciate the help.

Ty Werven

@Jason Albrecht Something like this as the end product (but 4 different graphs would) would be great

Jason Albrecht

Hello @Ty Werven

Thank for the extra information. That is really helpful.

May I encourage you to look into understanding Reports (Intermediate User Learning Track here).

There are multiple videos on the topic as well.

I say this because it's how I created multiple dashboards for individual external users to see data related to them, from a master spreadsheet (Source of Truth or SOT for short).

In other words, I would

• create a master template report with all the columns in the required sequence, then,
• save a copy of the template report and rename it to the appropriate unit
• change the filters in the report to reflect that unit's data
• create a master dashboard, creating the graph above (that you pasted in your message) and displaying the master template report data (which obviously feeds the graph).
• save a copy of the master dashboard and rename it to the appropriate unit
• change the graph and report sources to the appropriate unit report
• publish / share that specific dashboard to the appropriate unit representatives.

As a side note, we used the word 'portal' when naming the dashboard, (e.g., Unit 1 Portal) to help quickly search and find the templates and dashboards separate to all the other templates and dashboards for internal reporting.

For the sake of clarity, points 1 and 4 would not need to be done for each unit. Once you've created a portal for one unit you can go back to the templates you created, save a copy and then work on that copy for the next unit. The beauty of doing it this way is that some units will likely want certain data presented differently. The individual reports and dashboards will give you some flexibility, albeit I would recommend this be done with some reluctance, since we found that when the external parties talked with each other about their data, they didn't realise they were not seeing the same consecutive columns, causing all sorts of confusion.