Building a Dashboard from a Report

MikeCorvin

Is this possible?  I went to try and create a metrics sheet from the report but it doesn't show up.  Building the report was supposed to group all of my similar items together, why can i not then build a metric sheet to create my Dash based upon that report?

Andrée Starå

Hi Mike,

Not sure I follow!

To add to the Metric Widget you'll have to use a sheet. 

What do you want to do?

Have a fantastic week!

Best,

Andrée Starå - Workflow Consultant @ Get Done Consulting

Bruce Case

I agree with Andree.  To use the Metric Widget, the source comes from a sheet, however, if you do have a Report created, you can use the Report Widget and show the entire Report in the Dashboard.

Hope that helps.

Andrée Starå

Here's more info about the different Dashboard Widget Types: https://help.smartsheet.com/articles/518558-widget-types-for-smartsheet-dashboards

Hope that helps!

Best,

Andrée

Paul Newcome

When using cross sheet references, you cannot select a report to reference. It has to be a sheet. Try referencing the sheet that the report is pulling from and using the appropriate criteria needed.

MikeCorvin

I am not trying to use the metrics widget, but to build a metrics sheet which i can then use to build a graph or chart.  My original sheet that I am pulling from houses many types of information from various 'customers' the report then makes it so each 'customer' can only view their own information.  I would then in turn like to build dashboards for each individual 'customer' as well.  If i cannot use the report to pull data from, in what way can i build a sheet to correctly pull the information i want for only a specific customer?  If column x = customer z then attain completion status of column y for same row?

MikeCorvin

I am not trying to use the metrics widget, but to build a metrics sheet which i can then use to build a graph or chart.  My original sheet that I am pulling from houses many types of information from various 'customers' the report then makes it so each 'customer' can only view their own information.  I would then in turn like to build dashboards for each individual 'customer' as well.  If i cannot use the report to pull data from, in what way can i build a sheet to correctly pull the information i want for only a specific customer?  If column x = customer z then attain completion status of column y for same row?

Andrée Starå

Ok.

Now I follow!

My recommendation would be to create a master metric sheet where you collect everything witch cell-links or/and cross-sheet formulas from the other sheets. You can then use that information in reports and dashboards.

Would that work?

Best,

Andrée

Paul Newcome

If you are building out separate dashboards from each customer, you could compile the info you want displayed for each customer all in one sheet. Are you able to provide some screenshots (use dummy data if there is any sensitive/confidential information that can't be displayed)?

 

What I am thinking is using a table with COUNTIFS, SUMIFS, and INDEX/MATCH formulas along with cross sheet references to pull your pertinent data, but a lot of it is going to depend on your current setup and how/what exactly you are trying to display the data in the end.

MikeCorvin

Here is a quick shareable version of what I have created with some dummy data in it.  The Shareable Test is the main sheet while the report is labeled as such.  There a re a few metrics i would be interested in for creating charts/graphs, the variations in the dates, the amount of times any given material is reported, and down the road possibly how many times we have issues of foreign materials under the same PO#s.

Paul Newcome

And how are you wanting the data displayed? Chart/graphs? Tables?

MikeCorvin

Charts and Graphs I'm thinking, however if I can pull the information into a metrics sheet then I can build anything from it.

MikeCorvin

Charts and Graphs I'm thinking, however if I can pull the information into a metrics sheet then I can build anything from it.

Paul Newcome

Ok. Depending on the number of variables you are reporting on, it could require a handful of sheets for organizational purposes.

 

Basically what you would do would be to build out your table in the metrics sheet(s). Then in your formulas, you would use cross sheet references to mimic the criteria you were wanting to pull for the report.

 

I am having trouble figuring out the details from your above posted screenshots. Are you able to post other ones with the sheet names visible? It makes it easier to tell which one is which. Also a mockup of how you'd like the metrics sheet to work along with more detail on exactly where the information is coming from would help.

MikeCorvin

Paul,

I have retaken shots and will add them to this reply.  The one that reads as Test with 'Suppliers' listed as 1, 2, & 3, would be the main sheet (from which i would want to pull the metrics from), whereas the Report shows only 'Supplier 1'.  Creating a metrics sheet would be wonderful, as it would allow me any number of possibilities for future building in a Dashboard or etc,  building sheets as such hasn't normally been a huge issue, but by adding the variable of specifying a supplier to pull information on I am unsure how to go about creating that formula.  Obviously I cannot just draw information from the report, which would be easier as it is already sorted, would be nice if it offered If Then loops but maybe we'll get there one day.

Mike

Paul Newcome

Ok. It's making more sense now.

 

There are a ton of different ways to build out the sheet(s) depending on what you want to display and how. To specify a Supplier in a formula, you would use simple cross sheet referencing.

 

If you wanted to count how many times the word "Organic" showed up in the [Foreign Material] column, you would use

 

=COUNTIFS({Foreign Material Column Range}, FIND("organic", LOWER(@cell)) > 0)

 

Using the proper cross sheet referencing steps you would select the [Foreign Material] column from your sheet, and this will give you the number of times that word showed up in that range.

To add in the condition of the supplier:

 

=COUNTIFS({Foreign Material Column Range}, FIND("organic", LOWER(@cell)) > 0, {Supplier Column Range}, @cell = "Supplier Name")

 

Use the same cross sheet referencing steps to select the Supplier column and the specify the supplier. 

 

Of course you can build out a table and use cell references in place of the criteria, so on and so forth, but that's the general idea. Just keep adding range/criteria sets and you can pull as much and as detailed information as you want.

 

Depending on what you are pulling, you could use one metrics sheet for each supplier and have the foreign material types tracked or one for each foreign material type and get a total for each supplier on the same sheet, or you could have one sheet with parent rows as the supplier and a breakdown of material type as children or the other way around. The actual setup is entirely dependent upon what works for you based on the data you want to pull and how you want to display it.