How to summarize a report?

Pamela Wagner

I am creating a productivity tracker for my department of 9 people. Each person has a sheet where on a daily basis we note the amount of time we spend on a task and the department we do the task for (we are in IT so we have 15 departments we can work with). I've created metrics on each sheet for the total number of hours spent working for each dept, the % time and then the time bucketed into 4 major categories. From that sheet, I've created a sheet report for each person that will have both of those criteria (total time and % time) against each department then created an actual report for each of our IT departments. Lastly, I then created a master report that pulls in all of the data from each person so I have one report for the full IT dept.

Now I need to graph the total amount of time we spend as an IT department working with each of the departments we support. The first silliness is how do I get my master report to be a sum of all of the data? Right now, I have a row for each of us and columns for each dept:

Of course when I go to my dashboard and try to graph it, it grabs each cell but doesn't sum them. How do I sum the report or how do I create a report that sums each column so I have one total for each department?

Andrée Starå

Hi @Pamela Wagner

I hope you're well and safe!

Have you explored using the Grouping/Summary feature?

https://community.smartsheet.com/discussion/81983/new-create-a-chart-from-reports-with-grouping-and-summary

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Genevieve P.

Hi @Pamela Wagner

Thank you for this updated screen capture! I can see that you only have a Summary applied, so there are no Grouped rows within this Report.

The top "Total" row isn't currently supported with this new release, whether or not there are Groupings below it: Charts can only grab from the first level Groupings underneath that top Total.

What is the Filter for this Report, or how are you defining the rows that you're bringing in? If there are no columns in this current Report that have consistent or identical data, then I would suggest adding a helper column to the source sheet(s) so you can use the Group feature... something that will be consistent across all your rows. In my example I've used a Star:

Even though it's the exact same Data, this allows me to use the Group feature to group the entire Report and gather the Sum at the top. Now I can create a Chart from the one Grouped row, below the Total.

Let me know if this makes more sense! Please also provide your feedback on the new feature release by filling in this form, here.

Cheers,

Genevieve

Andrée Starå

Hi @Pamela Wagner

I hope you're well and safe!

Have you explored using the Grouping/Summary feature?

https://community.smartsheet.com/discussion/81983/new-create-a-chart-from-reports-with-grouping-and-summary

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Pamela Wagner

I didn't know this was available yet since when I go to create the chart in my dashboard the note says that this is still coming. I will definitely check it out!

Genevieve P.

Hi @Pamela Wagner

Just a quick note! Based on your image above, it looks like you just need the top Total row in your Summary is that correct? If so, you'll also need to Group your Report by something for the Chart to recognize the Summary data.

(Please see Brendan's note above: "This update does not support charting from the second level or third level groups in your report and does not support charting from the “Total” group.")

If you only need the top row, try Grouping your Report by a field that is consistent within every row such as a checkbox or another symbol. This will create a sub-heading under the "Total" row with that Group's summary, which you can use in the Chart.

Let me know if this works for you!

Cheers,

Genevieve

Pamela Wagner

Genevieve, thanks for the quick reply but I'm not understanding what you are suggesting to do. My report has a row for each person on the team and then each column is the number of hours we are working for that department. I've summed each column and that is what I need to chart. What is it that I'm using to group against then?? Here is what my report looks like:

Wouldn't being able to chart the total row make sense then?

Pam

Genevieve P.

Hi @Pamela Wagner

Thank you for this updated screen capture! I can see that you only have a Summary applied, so there are no Grouped rows within this Report.

The top "Total" row isn't currently supported with this new release, whether or not there are Groupings below it: Charts can only grab from the first level Groupings underneath that top Total.

What is the Filter for this Report, or how are you defining the rows that you're bringing in? If there are no columns in this current Report that have consistent or identical data, then I would suggest adding a helper column to the source sheet(s) so you can use the Group feature... something that will be consistent across all your rows. In my example I've used a Star:

Even though it's the exact same Data, this allows me to use the Group feature to group the entire Report and gather the Sum at the top. Now I can create a Chart from the one Grouped row, below the Total.

Let me know if this makes more sense! Please also provide your feedback on the new feature release by filling in this form, here.

Cheers,

Genevieve

Pamela Wagner

It worked! Thanks much for the hand holding!

Andrée Starå

@Pamela Wagner

Excellent!

Happy to help!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Genevieve P.

https://community.smartsheet.com/discussion/comment/301644#Comment_301644

No problem at all! I'm glad you were able to get it to work. 🙂

Gladys Liley

@Genevieve P. This is great! Quick question, when a summary report is emailed as an Excel the summarization row is gone. Is this the expected results or is another step needed?

Genevieve P.

Hi @Gladys Liley

Yes, this is currently expected. Summary rows are not currently able to be exported - please let the Product team know of your feature request through this form, here!

An alternative would be to embed the Report on a Dashboard, then Export the Dashboard to a PDF to show the Summary lines.

Cheers,

Genevieve

DianaP

Hi,

I have Grouping and summaries as well, but can't use it for creating a chart. What can be the problem?

Thanks, DianaP

Genevieve P.

Hi @DianaP

I can't see anything wrong with your current set-up, it should be selectable as a Chart. Can you try again today with a new widget? Are you still getting the same error?

DianaP

Hi Genevieve,

It is working now. Thank you!