Charts built off of reports

I have some charts that are built off of reports pulling from multiple metric sheets counting data entered in the last 30 days, 5 days and 24 hours. The reports are set to look at and add only the items that have a number greater than 0 in the 30 days column as there is a huge list and we wanted to simplify that list to just the items that have numbers to report. The issue we are running into is that when there is no data to report, the chart breaks instead of showing 0 values. Is there a way that I am not thinking of to keep the chart from breaking, but also keep that list simplified to just the items that have a number to report? Screenshot attached of the message showing on our dashboard.

Best Answer


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You could add a "helper row" to each of the sheets that will always be included in each of your reports. This way the reports are never technically empty.

  • I did think about that, but I would have to have a number in the greater than 30 days column in order for it to flow to the report, which I think would throw the teams off. Unless there is another way to set the report..... Thinking as I type here...... Would a checkbox that checks if there is a number in any of my 3 reference columns and check the box if there is a value, leave it unchecked if not ( or vice versa). I still don't think that would work as it would still be the same issue, it would either return all rows or none. I kinda feel like I'm overthinking the issue.

  • YESSS!!!! So simple too!!! I had thought about something similar, but it just did not click!! Thank you, I believe that will work. Sometimes you just need a fresh perspective. 😉

  • @Paul Newcome I expanded on your formula and used this:

    =IF(COUNTIFS([Last 30 Days]$2:[Last 30 Days]$22, >0) > 0, 0, 1)

    This way, if there is a number to report, the "No data to report" row will not show!! Very helpful, thank you!! ☺️ Sorry it did not click right away!!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Good thinking on how to remove the "No data to report" row when there is data. Nicely done! 👍️