Summarized Summary Reports and Charting

Megan MacKenzie Benefiel

I am using sheet summaries to calculate project health and want to roll up the metrics from several projects into a portfolio dashboard. I am pulling all of my summary sheets into a summary report and grouping them by project health type (indicators) and then summarizing to get a count of how many projects are red, yellow and green. I am having several issues.

1. when I go to report this on my dashboard using a chart, it is pulling ALL the summarized data, including total count at the top, which is messing up my chart. There doesn't seem to be a way to de-select the sheet total at top, even though the data are grouped. See images for context.

When I do select the chart and save it, it doesn't show on the dashboard. I get this error:

I see the note at the top of the summary sheet selection that says "Note: Support for reports with grouping and summary coming soon!" is that what's causing these issues and if so, does anyone know when this functionality will be available? We were so excited to be able to get rid of our helper sheets and use summary sheets instead, but not being able to roll our summary sheets up into metrics to report on the dashboard makes the summary sheet function almost useless for us, which is very disappointing. I am hoping this is just some user error and the functionality is actually there.

Aravind GP

Hi Megan,

You are correct about the support for reporting with grouping and that is causing the error. Though I do not have a date for when it will be done, to fix the chart, you can remove the summary and grouping in your report and then do the chart in the dashboard to just get the count of projects in Red and Green as per the data shown.

Megan MacKenzie Benefiel

Thanks for the response. When you say "do the chart in the dashboard" do you mean show the report itself? I want to show the total of red, yellow and green projects, and I don't believe I can do that without some kind of summary line, right? I Understand I can show the report, but that's not what I'm looking for. If I've missed something, please let me know. Thanks!

Aravind GP

Hi Megan,

Once you remove the grouping and summary, you will have only project names with their respective health indication. Save the report and go to your dashboard. Select chart in the add widget and pick the source as your report which contains the projects with health. You can then decide on making it a pie or donut chart to show the count of red and green projects. You can also link the report on click of the widget.

Megan MacKenzie Benefiel

Thanks! When I try the steps as you have here, it says "the selected columns cannot be charted" so I'm not sure you can chart indicators.

Joyce Zahacy

Would love to have a response to this. I'm having the same issue. Megan - did you ever get this figured out?

Megan MacKenzie Benefiel

Nope.

Genevieve P.

Hi @Joyce Zahacy & @Megan MacKenzie Benefiel

Summary fields in Reports are unable to be used in Chart Widgets just yet, but our Product team is working on this!

In the meantime, you would want to create a "Metrics" sheet where you use cross-sheet formulas to calculate your data (such as a SUMIF or COUNTIF formula, depending on what you're looking to do). Then you can use this sheet as the source for your charts.

If you're having trouble creating the cross-sheet formulas, let me know and I'm happy to help further. It would be useful to see a screen capture of your source sheet (but please block out any sensitive information).

Cheers,

Genevieve