Making a Chart from a Report

Options

Hello,

I have two groupings under my Primary column. I would like to make a chart to display the hierarchy as separate Series in the legend. Therefore the chart would be colored based on hierarchy and the values a function of the total, but it will only show the top groupings sum value. Ideally this chart would look like this but be split into two bars per column depending on test site name. In this case test site is all staying one color....



Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Amanda Berta

    When a Chart is using a Grouped and Summarized Report as the source, it can only take the top level grouping, as you've found. Here's the announcement post on this feature with more details: Create a chart from reports with grouping and summary

    One way you could create something similar (showing both the first and second level grouping together) would be to set up a helper column in your sheet. In this helper column you could create a column formula to add together both values, like so:

    = [Test Site]@row + " / " + [Started/Completed?]@row

    Then when both values are in one cell, you can use this helper column as the column to Group by in the Report, creating only one level of grouping/summary.

    Let me know if this will work for you!

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Amanda Berta

    When a Chart is using a Grouped and Summarized Report as the source, it can only take the top level grouping, as you've found. Here's the announcement post on this feature with more details: Create a chart from reports with grouping and summary

    One way you could create something similar (showing both the first and second level grouping together) would be to set up a helper column in your sheet. In this helper column you could create a column formula to add together both values, like so:

    = [Test Site]@row + " / " + [Started/Completed?]@row

    Then when both values are in one cell, you can use this helper column as the column to Group by in the Report, creating only one level of grouping/summary.

    Let me know if this will work for you!

    Cheers,

    Genevieve

  • Amanda Berta
    Options

    @Genevieve P. Thank you for the insight. Yes adding the helper column does get me the info I want but its still not visually appealing. So I guess I'll have to create separate charts for each site to see what I want. Smart sheet should look into allowing hierarchy as a series as it adds pretty valuable views.



  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Amanda Berta

    Using Grouped Reports as a source for Charts is still a fairly new feature so I'm sure that the Product team would be grateful to hear your feedback! If you have a moment, please fill out this form here to in order to provide them with your use-case and request.

    Cheers,

    Genevieve