donut chart from multi-sheet report

i have this report summing up approved, payed or overdue quotes from multible sheets - each showing a different month

how do i use only the sum of all columns to make a chart?


Answers

  • Melitta
    Melitta ✭✭✭✭✭

    Hello @inon rettig,

    I don't think it is possible to create a chart based upon a report. It will be interesting to hear what others have to day.

    I would create a calculations sheet.

    Use a formula to pull the info from the sheets you are using and sum the approved, Awaiting Charge, etc.

    In your dashboard, add a chart widget and pull the data from your calculations sheet. You can then set your widget to display your report.

    Does that make sense?

    Melitta

  • yes that makes sense

    but i dont think i can make a calculation sheet that includes all my present sheet and updates when i add new ones

    maybe i can set up all my future sheets in advance and calculate them

    is that done in cross reference sheet ?

  • Genevieve P.
    Genevieve P. Employee
    edited 02/07/22

    Hi @Melitta and @inon rettig

    Good news! You actually can create Charts by using a Report as the source data. However if you're using a Summarized Report, you'll also need to have some sort of Grouping applied as well (it uses the first level Grouping and Summarized data as the source, and cannot read the top Total row).

    Here's the announcement post on this feature: New! Create a chart from reports with grouping and summary

    How are you defining the rows that you're bringing in to this Report?

    If there are no columns in this current Report that have consistent or identical data to Group By, then I would suggest adding a helper column (or helper Field if this is a Summary Report) to the source sheets so you can use the Group feature - something that will be consistent across all your rows. In my example I've used a Star:

    Even though it's the exact same Data, this allows me to use the Group feature to group the entire Report and gather the Sum at the top. Now I can create a Chart from the one Grouped row, below the Total.

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!