How to use summarized data from report in chart widget

ghammar

I'm using a program report to summarize data across several hundred projects. The report is working well and shows the sum of the data across all of the projects. I want to show only the summarized row from the report in a smoothed line chart widget. The users have access to the report, so I don't need to show the detail in the chart. How do I include only the summary line as the data source for the chart widget? Here's a snippet from the program report; I want to graph only the part in the red box.

jmyzk_cloudsmart_jp

Hi @ghammar

You're right — not being able to chart the summary row directly from a report is a limitation, especially when directors expect current data and the process needs to scale.

To clarify:

1. Using the Smartsheet API, you can access the cell values from a report, meaning you can pull the detailed row data that feeds the report.
   Since you have hundreds of project sheets, creating cross-sheet formulas to reference all of them would be impractical and difficult to maintain.
   However, with the API, you can access all the report data in one place, without needing to create or maintain hundreds of cross-sheet references.
2. That said, the summary row itself is not exposed via the API, so you cannot directly retrieve or chart it.
3. As a workaround, you can use the API to calculate the summary values — for example, sum each column across all rows in the report's data — and then write those calculated values into a single row in a dedicated metrics sheet.
4. This metrics sheet can then be used as the source for a smoothed line chart widget, showing only the summarized values.
5. The whole process can be automated with a scheduled script (e.g., hourly or daily) or triggered by a Smartsheet webhook, depending on how real-time your data needs to be.

This approach requires some development or scripting skills (such as Python). Still, once built, it offers a scalable and near real-time solution, eliminating the need for manual data preparation or maintenance-intensive formulas.

Let me know if you'd like help getting started or a sample script!

jmyzk_cloudsmart_jp

Hi @ghammar

To display only the summarized row from the report in a smoothed line chart widget, as shown on the right side of the image below, I exported the report as an Excel file, imported it as a sheet, added a parent row, and summed the values of the children. If you have hundreds of sheets, this should be a practical way. (Bottom right sheet, below)

In the dashboard, I selected only the parent row as the source data for the smoothed line chart widget.

It is not automatic, but given your reporting period of a year or so, manually updating the chart once a month may be an acceptable approach. (All the data before the current month is past data and does not change.)

For automatic, real-time reporting, you can use the stacked bar chart widget for the same purpose, as shown on the left side.

https://app.smartsheet.com/b/publish?EQBCT=63df835e102849c2be80d444fc8fd634

ghammar

@jmyzk_cloudsmart_jp Thanks for the ideas. Our directors are expecting current data in their dashboards and waiting a month to find abnormalities won't work for them. Manual data manipulations don't work well in our environment and don't allow us to scale well. I may be able to build a bot to do the work, but it would be really helpful to have the ability to take the data that are summarized and chart the summary line.

jmyzk_cloudsmart_jp

Hi @ghammar

You're right — not being able to chart the summary row directly from a report is a limitation, especially when directors expect current data and the process needs to scale.

To clarify:

1. Using the Smartsheet API, you can access the cell values from a report, meaning you can pull the detailed row data that feeds the report.
   Since you have hundreds of project sheets, creating cross-sheet formulas to reference all of them would be impractical and difficult to maintain.
   However, with the API, you can access all the report data in one place, without needing to create or maintain hundreds of cross-sheet references.
2. That said, the summary row itself is not exposed via the API, so you cannot directly retrieve or chart it.
3. As a workaround, you can use the API to calculate the summary values — for example, sum each column across all rows in the report's data — and then write those calculated values into a single row in a dedicated metrics sheet.
4. This metrics sheet can then be used as the source for a smoothed line chart widget, showing only the summarized values.
5. The whole process can be automated with a scheduled script (e.g., hourly or daily) or triggered by a Smartsheet webhook, depending on how real-time your data needs to be.

This approach requires some development or scripting skills (such as Python). Still, once built, it offers a scalable and near real-time solution, eliminating the need for manual data preparation or maintenance-intensive formulas.

Let me know if you'd like help getting started or a sample script!

ghammar

@jmyzk_cloudsmart_jp Thanks for clarifying. We use the Smartsheet API's extensively, so it's a matter of working the solution into one of our sprints.