Pivot App - Dynamic Updating

Michael Foster

Hi,

I'm trying to create a Pivot Chart using the pivot app based on a number of sheets in a workspace. As it doesn't allow me to pivot on data from a workspace i have created a report, this works and i'm able to pivot on that data.

The problem i have is when i pivot on the pivot sheet it has created a row called grand total. I need to allow the Chart to update automatically if any additional lines of data are added and can only do that if i select all rows.

This brings through the Grand Total Column but i don't want to reflect that in my charts.

How do I remove that row from the Pivot Sheet?

Also is there a link to the Engage 19 Pivot App session?

Many thanks,

Mike

Genevieve P.

Hi @Michael Foster

As far as I'm aware, that Grand Total row can't be removed from the Pivot Sheet as it summarizes the data and will appear again each time the configuration runs... however I have an idea on how to make sure it's not included in your Chart Widget.

If you create a Report from this Pivot sheet you can set up the Report criteria so that it excludes that one Grand Total row, but includes any other rows from the Pivot sheet. Then have your Chart Widget be based off the Report (which will automatically always pull all data in the Report). This way as new rows get input into your Pivot sheet it will update the Report, which will update your Dashboard.

In regards to a link to the Engage 2019 session, here's a link to our Engage Content: https://www.smartsheet.com/content-center/best-practices/engage-content

It sounds like you may have been looking for the session titled "Build End-To-End Business Solutions with Smartsheet Pivot App, Calendar App, and DataMesh", is that correct? If so, click here for the direct link.

Engage 2020 is virtual this year, and you can learn more or register here!

Let me know if I can help with anything else.

Cheers!

Genevieve

Michael Foster

Hi Genevie,

Doing this restricts new columns that maybe created on the basis that columns are added because of the input into the pivot.

My understanding is that the data points are restricted based on the data points that you set in the subsequent report.

I just want a dynamic pivot which doesn't include the grand totals (MS allows you to remove the grand totals) - I just want the pivot of data.

Thanks,

Mike

Genevieve P.

Hi @Michael Foster

Hmm, ok. So there's no way to set this up from the Pivot App itself (please submit an Enhancement Request when you have a minute!)

However, you could get rid of this row by creating a helper "archive" sheet to collect these Grand (Total) rows. To do so, set up an automated workflow on the destination Pivot sheet that Moves the row (see here) when it's added or changed, with the criteria that the title in the Primary column is "Grand (Total)".

There will be a few minutes where the row will appear in the sheet, but then it will auto-filter out by moving over to your Archive sheet. Do you think that will work for you?

Cheers,

Genevieve