How do I get a Dashboard Chart Widget to display 'No Data' when the source report returns no data?

Swampcat14
Swampcat14 ✭
inSmartsheet Basics

I have a dashboard chart widget whose source data is a report on a sheet. There are times when the report accurately returns no data from the sheet that meets the reporting filter criteria. In that case, the dashboard chart widget displays an 'Unable to display chart' error (screenshot below).

Smartsheet Dash 'Unable to Display' Error.png

This error message is inaccurate and leads the users to believe there is a configuration error or something broken in the chart or the underlying report. This effect is worsened during troubleshooting when attempting to edit the widget, and seeing the prompt to 'Add Data'.

Smartsheet Dash 'Unable to Display' Chart editor.png

The chart widget and the report are correctly configured, it's just that the report has returned no data. Based on the report configuration, 'no data' is an occasional accurate and intended output. With that output, the chart widget should display something more informative and accurate, like the same chart that was there before, just with '0' displayed for each measured parameter.


Does anyone have a solution for this? I had temporary success with a 'Summarize' configuration on the report, but that was apparently only due to ghost data coming through from the source sheet. Once I cleaned up the ghost data, the 'Summarize' configuration failed to resolve the issue again.

Tags:
· Share on Facebook Share on Twitter

Answers

  • Kelly P.
    Kelly P. ✭✭✭✭✭

    @Swampcat14

    We get around this annoyance by having a very small value populate if everything is a zero. For example, if the chart is a count of something by month, then Jan will populate as .01 if Jan-Dec are all zeros. This keeps the chart from displaying a message indicating a "broken" chart.

    Hope this helps!

    · Share on Facebook Share on Twitter

Categories

Hi @Jenn Moffett<\/a>,<\/p>

One approach is as follows.<\/p>

  1. Create a helper column. Name it \"Modified\", and select \"Modified Date\" as the column type. You can optionally hide this column.<\/li>
  2. Create a Sheet Summary field. Name is \"Last Updated\", and select \"Date\" as the column type.<\/li>
  3. Type the following formula into the \"Last Updated\" Sheet Summary field: =MAX(Modified:Modified)<\/li><\/ol>

    Hope that helps! I just tested it before recommending, and it worked like a charm! :)<\/p>

    BRgds,<\/p>

    -Ray<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":292,"urlcode":"dashboards","name":"Dashboards"},{"tagID":335,"urlcode":"sheet-summary","name":"Sheet Summary"}]},{"discussionID":110982,"type":"question","name":"Convert to Column Formula Error","excerpt":"I'm trying to make the below formula a column formula. If I copy and paste the formula into cells, it works as expected. =IF(OR([Status]1 = \"Complete\", [Status]1 = \"//www.santa-greenland.com/community/discussion/109467/\"), \"//www.santa-greenland.com/community/discussion/109467/\", \"Yes\") Two issues, though: 1) The [Status]1 brackets are being automatically removed and reformatted as Status1 2) When I try to convert to a column…","snippet":"I'm trying to make the below formula a column formula. If I copy and paste the formula into cells, it works as expected. =IF(OR([Status]1 = \"Complete\", [Status]1 = \"//www.santa-greenland.com/community/discussion/109467/\"), \"//www.santa-greenland.com/community/discussion/109467/\", \"Yes\")…","categoryID":321,"dateInserted":"2023-09-29T12:37:39+00:00","dateUpdated":null,"dateLastComment":"2023-09-29T13:37:17+00:00","insertUserID":167683,"insertUser":{"userID":167683,"name":"aurea","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/aurea","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-29T20:31:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-09-29T16:56:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":15,"score":null,"hot":3391986296,"url":"https:\/\/community.smartsheet.com\/discussion\/110982\/convert-to-column-formula-error","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110982\/convert-to-column-formula-error","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":110982,"commentID":397635,"name":"Re: Convert to Column Formula Error","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/397635#Comment_397635","dateInserted":"2023-09-29T13:37:17+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-09-29T16:56:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-09-29T13:39:02+00:00","dateAnswered":"2023-09-29T13:37:17+00:00","acceptedAnswers":[{"commentID":397635,"body":"

    You only need the brackets when the column name contains a space, number, and\/or special character. Your column name doesn't contain any of those things which means the brackets aren't needed which is why Smartsheet is removing them.<\/p>


    <\/p>

    The reason it cannot be converted into a column formula is because you are using specific row numbers. Column formulas must use \"@row\" references.<\/p>


    <\/p>

    You have<\/p>

    Status1<\/p>


    <\/p>

    You need<\/p>

    Status@row<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":110944,"type":"question","name":"Alerts","excerpt":"Hi there, I want to use the trigger and alert to notify someone in the next row when the task above is complete. E.g., if the Status column is changed to Complete in row 3, I want an alert to be sent to the contact in the Assigned To column in row 4. Is that possible? Megan","snippet":"Hi there, I want to use the trigger and alert to notify someone in the next row when the task above is complete. E.g., if the Status column is changed to Complete in row 3, I want…","categoryID":321,"dateInserted":"2023-09-28T18:30:24+00:00","dateUpdated":null,"dateLastComment":"2023-09-29T17:38:02+00:00","insertUserID":167668,"insertUser":{"userID":167668,"name":"mmburns","url":"https:\/\/community.smartsheet.com\/profile\/mmburns","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-29T17:38:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":167567,"lastUser":{"userID":167567,"name":"dschrimpsher","title":"Enterprise Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/dschrimpsher","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!CSDrwr4e8_o!pFFr6eYKo4I!_leR1jGucXU","dateLastActive":"2023-09-29T18:19:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3391936706,"url":"https:\/\/community.smartsheet.com\/discussion\/110944\/alerts","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110944\/alerts","format":"Rich","lastPost":{"discussionID":110944,"commentID":397720,"name":"Re: Alerts","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/397720#Comment_397720","dateInserted":"2023-09-29T17:38:02+00:00","insertUserID":167567,"insertUser":{"userID":167567,"name":"dschrimpsher","title":"Enterprise Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/dschrimpsher","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!CSDrwr4e8_o!pFFr6eYKo4I!_leR1jGucXU","dateLastActive":"2023-09-29T18:19:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-09-29T17:20:41+00:00","dateAnswered":"2023-09-28T22:57:55+00:00","acceptedAnswers":[{"commentID":397558,"body":"

    Hi,<\/p>

    You can use checkbox column to trigger the person assigned to in row 4. In row 4 checkbox, use the below formula:<\/p>

    =IF(Status3 = \"Complete\", true, false)<\/p>

    Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=321&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":5117,"limit":3},"title":"Trending in Smartsheet Basics","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

    Trending in Smartsheet Basics

    • 仪表板最后更新日期

      I would like to display the last time the dashboard was updated. My approach was adding a date field in the summary data that automatically pulls the last saved date of the sheet. I don't think I can do a formula in the date field type. Any other suggestions?

    • Convert to Column Formula Error

      I'm trying to make the below formula a column formula. If I copy and paste the formula into cells, it works as expected. =IF(OR([Status]1 = "Complete", [Status]1 = ""), "", "Yes") Two issues, though: 1) The [Status]1 brackets are being automatically removed and reformatted as Status1 2) When I try to convert to a column…

    • Alerts

      Hi there, I want to use the trigger and alert to notify someone in the next row when the task above is complete. E.g., if the Status column is changed to Complete in row 3, I want an alert to be sent to the contact in the Assigned To column in row 4. Is that possible? Megan

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video