Pie Charting a Sheet Summary Report

Rob Lord

I have added a RAG status to all my projects in the Sheet Summary view. I can pull a report showing the projects and their RAG status fine.

When I then try to pie chart the RAG status onto a dashboard this is not possible as the RAG status is not a number.

So I added a number field and populated it with '1' so that I can add the RAG status and a number to a pie chart, but then the pie chart returns a segment for each row in the report - it does not aggregate them. So for each Green status, there is a pie segment and so on.

So I try to aggregate my Sheet Summary using a sheet - but you cannot access Summary items in a sheet. So as far as I can tell I cannot chart out Sheet Summary items.

Unless I'm missing the obivious, Sheet summary is good for reporting, terrible for dashboards.

Paul Newcome

You are unfortunately correct. You would need to pull the value to the sheet so that you can access it in a cross sheet reference (either cell linking or formula driven). You would then pull your counts so that you have a numerical value to be used in your dashboard chart.

Rob Lord

Thanks Paul - but there is no way to pull Sheet Summary data into a sheet right?

Paul Newcome

There is. To reference a Summary Field in a sheet, you would wrap the field name in square brackets and use # in place of the row number.

=[Summary Field Name]#

=[RAG Summary]#

Rob Lord

Thanks Paul - is that documented anywhere - if so I missed that - this is almost perfect - thank you for helping...

One last question though - that works in the sheet that the Summary field is on. Is there a way to have a central sheet that references the Summary field without the Summary field being added directly to the current sheet?

Something like <sheetname>[RAG Summary]#

Paul Newcome

HERE and HERE is some info on Sheet Summaries and how to use them.

A little bit about Sheet summaries and cross sheet references... You can reference other sheets in formulas used in Sheet Summary Fields, but Sheet Summary Fields cannot be referenced by other sheets. That is why I suggested pulling the summary data to the sheet (I use hidden columns for this) then using those cells in the sheet in your cross sheet references.

Being able to access Sheet Summary Fields would be very useful though (perfect example right here). Feel free to Submit a Product Enhancement Request when you are able.

dqsoutherland

Just wanted to add i am trying to do something very similar. the whole reason for the sheet summary was to give my sheet a clean look, especially we you start moving rows from sheet to sheet (since the columns follow). Thanks for the discovery, I'll also add a product enhancement.