Pie/Donut Charts
I have a project summary report that gives me counts of project tasks by status in >50 project sheets. Ex: each project has summary fields for its row tasks like "Complete Count", "Not-Started Count".... My goal is to have a pie/donut chart on my dashboard to show an aggregation of counts from all of my projects.
When I select the summary report and the count columns, the option for pie/donut gray out. As an experiment I selected a stacked column but it then creates a chart with each project individually while I want an aggregated view.
What am I doing wrong?
Best Answers
-
Pie/Donut charts can be very picky about how their data is formatted and what they can be to begin with, and as a result, I'm pretty sure you just can't do it. When working with a report in making a graph, Smartsheet always selects the entire report, meaning that you can't select individual rows, only individual columns. That would kill what you're trying to do in and of itself, but I'm reasonably sure that Dashboard Charts don't even recognize the Report Summaries as data that exists. I ran an experiment on a report with 12 rows and a report summary for each column and the resulting graph [a bar graph] only had 12 series, not the 13 it would have if it could see the Report Summary. Unfortunately, I think what you're trying to do is just entirely impossible the way you're doing it.
Unpalatable though it is, the easiest answer I see here is to recreate the function of the report in a grid instead. Without knowing the exact nature of you're sheets it's hard to say how best to go about this, but if you have a summary rollup of some kind [as created through Control Centre] you could set up a system that sifts through it and pulls out the corresponding values for each project using a vlookup() or index(match()) or something like that. If you don't have Control Centre this gets considerably harder, but with some more information I'm pretty sure we could come up with something to approximate the report summary.
-
The grouping function does work when creating charts/graphs from a report, but it does not work with the Summary row. In this particular instance though (because of how the data is coming into the report) grouping doesn't work because it is not being grouped by the metrics you want to display.
If you were to pull every single row from every single sheet into the report and then group based on the column you are using to drive those counts in your formulas in your Sheet Summary fields, then it would have worked.
Answers
-
Did you try grouping the report?
-
I will try that now, Thanks!
-
Grouping did not seem to get me anywhere. But, summarizing does show the data I want to chart (see top row). Still won't let be build a pie/donut chart.
-
Pie/Donut charts can be very picky about how their data is formatted and what they can be to begin with, and as a result, I'm pretty sure you just can't do it. When working with a report in making a graph, Smartsheet always selects the entire report, meaning that you can't select individual rows, only individual columns. That would kill what you're trying to do in and of itself, but I'm reasonably sure that Dashboard Charts don't even recognize the Report Summaries as data that exists. I ran an experiment on a report with 12 rows and a report summary for each column and the resulting graph [a bar graph] only had 12 series, not the 13 it would have if it could see the Report Summary. Unfortunately, I think what you're trying to do is just entirely impossible the way you're doing it.
Unpalatable though it is, the easiest answer I see here is to recreate the function of the report in a grid instead. Without knowing the exact nature of you're sheets it's hard to say how best to go about this, but if you have a summary rollup of some kind [as created through Control Centre] you could set up a system that sifts through it and pulls out the corresponding values for each project using a vlookup() or index(match()) or something like that. If you don't have Control Centre this gets considerably harder, but with some more information I'm pretty sure we could come up with something to approximate the report summary.
-
The grouping function does work when creating charts/graphs from a report, but it does not work with the Summary row. In this particular instance though (because of how the data is coming into the report) grouping doesn't work because it is not being grouped by the metrics you want to display.
If you were to pull every single row from every single sheet into the report and then group based on the column you are using to drive those counts in your formulas in your Sheet Summary fields, then it would have worked.
-
Thank you, I will try that.
