How to summarize and visualize sheet summary data of multiple sheets?

Bartosz Sobczak

Hi!

I've been using SmartSheet to track progress of different business units on a particular project. Each unit has its own sheet containing grid data that they fill out, as well as sheet summary data where I calculate different KPIs (e.g. number of records with specific status). For each unit, the sheet summary data is then summarized in a report which I use as a source for a unit-specific dashboard.

What I would like to do now is to aggregate the same information on a global level (for all units). I created a report that pulls in the sheet summary data from all the unit sheets. In the report, I added summaries to give me a global sum for each KPI. However, I'm not able to use it as a dashboard source - the Total row will not stay collapsed and the dashboard will always show the data on unit level instead of a consolidated one (please see below).

Is there any way to summarize the sheet summary data of multiple sheets and display only the Total in the report?

If not, is there a better way you could suggest to achieve a consolidate view?

Thanks in advance for your response.

Best,

Bartek

Sarah Donnelly

Following. Have a similar situation.

Bartosz Sobczak

Any chance somebody was able to review this case?

Genevieve P.

HI @Bartosz Sobczak and @sdonnelly

Yes! You should be able to keep the rows collapsed, only showing the Summary rows, by toggling on the option to "Collapse Grouped Rows" in the Report Widget:

Is this what you were looking to do?

Cheers,

Genevieve

Bartosz Sobczak

Hi Genevieve,

Thanks for your input!

Unfortunately, this is not the problem. What I have is a report that is based on sheet summary data of different sheets. The report includes totals of all the relevant KPIs (e.g. sum of revenue from all individual sheets) in the top row. I would like to use this report as input for charts on a dashboard. Unfortunately, the report stays expanded and any charts I create show the individual data from the sheets instead of a consolidated total.

Feel free to let me know in case you need any additional insights from my side.

Best Regards,

Bartek

Genevieve P.

Hi @Bartosz Sobczak

Thank you for clarifying that you are looking to source a Chart type of widget from that top summary row. Chart Widgets cannot currently use the Grouping & Summary feature in Reports as the source data (as you've found).

Our Product team is currently working on this, but it has not been released yet. You can subscribe to our Release Notes page (here) to be notified when this feature is available.

Until then, you could either use a Stacked Bar chart to show all the data together, or display it as a collapsed header row through a Report Widget as in my image above.

Cheers,

Genevieve

Genevieve P.

Hi @Bartosz Sobczak

This feature has now been released! You can now create Chart Widgets from the top Summary rows from a Grouped Report. See: Charting using Grouping and Summary

Cheers,

Genevieve

Bartosz Sobczak

Hi @Genevieve P.

Thanks for the update, appreciate it. Will look into the new functionality and post back here in case I have any questions or comments.

Best Regards,

Bartek