Create Chart Widget Using a Report (not a Sheet)
With the Dashboards, I am able to create charts from a sheet but not from a report (this requires using the report widget).
I collect weekly "submission date" data from 40+ people. I have created a report that shows when the submission is due, when it is submitted, and the number of days (plus or minus) between the two. I would like to create a line graph for each individual that charts the above and below zero data; however, I can't graph the data from my report.
If I add the tracking information to my sheet, will I a) be able to still pull a report from that sheet, and b) create a line graph showing each person's time differences? OR, is there a way to use the report I have already created from my sheet, and put that into a line graph?
Thank you!
Pam
Answers
-
Sasan EmployeePam,
At this time the source data for a Chart needs to come from a sheet. A few alternative suggestions:
- Create a new sheet and use the new vlookup or cell link feature to reference your staff and their # of days respectively in two columns right next to each other (contiguous). Then select your range.
- If you have nonnumerical data between your staff names and the number of days, you can still select a contiguous range with the non-numerical data and the chart will be created only with your staff members and # of days. See example below.
Thanks
Sasan
-
Pam Tadsen ✭✭✭✭✭
Hi Sasan,
Thanks for the suggestion. I'll give that a try!
Pam
-
Sasan Employee
Pam,
我们刚刚发布的新功能选择数据from your Report. You can now select a Report you have access to and then in the chart preview panel you will find a new button called "Select Columns". You can then select specific columns in your Report you want to chart against. Hope this help.
Sasan
-
Pam Tadsen ✭✭✭✭✭
Wonderful news, Sasan - thank you! I can't wait to take it for a test drive!
-
@HELP@ ✭Did you guys take out this option? I'm looking for the way to display just selected columns from my report.
-
I don't see any options on how to select specific columns froma report either
-
Genevieve P. Employee AdminWhen you're selecting the source data, the whole report will be selected.
The option to then only select specific columns is within the Chart Widget window, under the headingChart & Data.(See here for more information on Chart Widgets).
-
Jo-Ann Turning ✭✭✭
When I select a REPORT to use for a Chart Widget- it is selecting the entire report (it is not letting me select the columns of that report that I would like to chart)... I have probably 20 columns on the report and it looks like it will only allow me to chart these 3 shown below?? any ideas how I can actually see the entire list of columns. If I click edit - it takes me back to report that I selected for the chart but it is completely selected and "frozen" it will not move in any way to allow me to select or unselect columns. Thanks for your quick help and advice!
-
Andrée Starå ✭✭✭✭✭✭I hope you're well and safe!
When you're using a Report as the source, unfortunately, you can't make any change to the range. The whole Report will always be selected.
Regarding that, you only see three columns.
Strange!
Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or somemore screenshots of the Report?(Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too,[email protected])
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community bymarking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Genevieve P. Employee Admin
Are you using the Grouping and Summary functions on this chart, but only for those three columns? If you want to map the row data and not the Grouped/Summarized data, I would suggest creating a Duplicate chart that does not have Summary turned on. Then you should be able to select any of the columns visible in the Report.
Let me know if this makes sense or describes your issue!
Cheers,
Genevieve
-
markh10 ✭
So each different Chart, I would need a separate Report?
-
Genevieve P. Employee Admin
Hi@markh10
Can you clarify what type of charts you're looking to create, and how your source data if formatted?
You can create multiple charts off of one single Report, using different columns from that same source data in each chart if that helps.
Cheers,
Genevieve
-
markh10 ✭
I don't know how to select columns to create a chart.
When I open up the report file (subset of the master sheet), all the columns/fields are selected to be charted.
It seems that if I want other columns to be charted then I would have to create another report file with just those columns.
Maybe a separate problem but with the report I created and tried to use the Chart Widget, Add Data, I get "Selected data cannot be charted. Please select a new range." This is from a report I created with only 3 columns, 184 rows.
-
Genevieve P. Employee Admin
Hi@markh10
You're correct that the Report will be selected in its entirety when you identify your source item, however after you've selected this, in theChart Widget configurationyou can then identifywhichcolumns from that Report you want to display. This means that new rows added to the Report will automatically be included in your Chart for those columns!
Once you've set up your first widget, you can simply copy/paste the widget (without needing to re-select your same source Report) and adjust the columns that each Chart has included.
In regards to the error message you're seeing, it sounds like perhaps all the columns you selected may only have Text associated with them (and no numbers to show in a chart). Could that be the case? It would be helpful to see screen captures of your source data, but please hide sensitive information.
Cheers,
Genevieve
-
markh10 ✭
这是错误的屏幕截图。
Categories
<\/p>
=VALUE(IFERROR(JOIN(DISTINCT(COLLECT([Customer PO Amount (USD)]@row:[Customer PO Amount (Local Currency)]@row, [Customer PO Amount (USD)]@row:[Customer PO Amount (Local Currency)]@row, <>\"//www.santa-greenland.com/community/discussion/comment/\"))), \" \"))<\/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":109460,"type":"question","name":"IF \/ OR formula then Check a Box","excerpt":"I need a formula that IF a specific column has either 1 of 2 specific choices, then a BOX in another column is checked: =IF(([Study Status]@row = \"Active\/ Open to Accrual\", 1) OR([Study Status]@row = \"In Start-Up\", 1))","snippet":"I need a formula that IF a specific column has either 1 of 2 specific choices, then a BOX in another column is checked: =IF(([Study Status]@row = \"Active\/ Open to Accrual\", 1)…","categoryID":321,"dateInserted":"2023-08-25T13:30:39+00:00","dateUpdated":null,"dateLastComment":"2023-08-25T16:21:27+00:00","insertUserID":9250,"insertUser":{"userID":9250,"name":"Susan Swisher","url":"https:\/\/community.smartsheet.com\/profile\/Susan%20Swisher","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-25T16:23:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":46,"score":null,"hot":3385956126,"url":"https:\/\/community.smartsheet.com\/discussion\/109460\/if-or-formula-then-check-a-box","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109460\/if-or-formula-then-check-a-box","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109460,"commentID":392616,"name":"Re: IF \/ OR formula then Check a Box","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392616#Comment_392616","dateInserted":"2023-08-25T16:21:27+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+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-08-25T15:13:03+00:00","dateAnswered":"2023-08-25T14:14:39+00:00","acceptedAnswers":[{"commentID":392575,"body":"
Give this a try:<\/p>
=IF(OR([Study Status]@row = \"Active\/Open to Accrual\", [Study Status]@row = \"In Start-Up\"), 1)<\/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":109457,"type":"question","name":"Conditional Formatting (modified date not in the last 3 weeks)","excerpt":"I would like to use Conditional Formatting (highlight the entire row yellow) if the modified date is not within the last 3 weeks. So any row that has not been recently updated (last 3 weeks) should be highlighted in yellow. Is this spmething that can be done directly within conditional formatting or should I first create a…","snippet":"I would like to use Conditional Formatting (highlight the entire row yellow) if the modified date is not within the last 3 weeks. So any row that has not been recently updated…","categoryID":321,"dateInserted":"2023-08-25T12:33:14+00:00","dateUpdated":null,"dateLastComment":"2023-08-25T12:40:57+00:00","insertUserID":161267,"insertUser":{"userID":161267,"name":"Jef Snyders","title":"Jef Snyders","url":"https:\/\/community.smartsheet.com\/profile\/Jef%20Snyders","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/4HJAEW33KBD0\/nXEKEZE5EQEV4.jpg","dateLastActive":"2023-08-25T13:32:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":161267,"lastUser":{"userID":161267,"name":"Jef Snyders","title":"Jef Snyders","url":"https:\/\/community.smartsheet.com\/profile\/Jef%20Snyders","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/4HJAEW33KBD0\/nXEKEZE5EQEV4.jpg","dateLastActive":"2023-08-25T13:32:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":26,"score":null,"hot":3385935251,"url":"https:\/\/community.smartsheet.com\/discussion\/109457\/conditional-formatting-modified-date-not-in-the-last-3-weeks","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109457\/conditional-formatting-modified-date-not-in-the-last-3-weeks","format":"Rich","tagIDs":[437],"lastPost":{"discussionID":109457,"commentID":392553,"name":"Re: Conditional Formatting (modified date not in the last 3 weeks)","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392553#Comment_392553","dateInserted":"2023-08-25T12:40:57+00:00","insertUserID":161267,"insertUser":{"userID":161267,"name":"Jef Snyders","title":"Jef Snyders","url":"https:\/\/community.smartsheet.com\/profile\/Jef%20Snyders","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/4HJAEW33KBD0\/nXEKEZE5EQEV4.jpg","dateLastActive":"2023-08-25T13:32:31+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-08-25T12:40:09+00:00","dateAnswered":"2023-08-25T12:37:47+00:00","acceptedAnswers":[{"commentID":392551,"body":"
There is no direct way to do this. Adding a checkbox helper column with something similar to this and then formatting based on the checkbox is the simplest way. You can even hide the extra column, as there is no reason for it to be visible.<\/p>
=IF([Modified Date]@row < TODAY(-21), 1, 0)<\/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":437,"urlcode":"conditional-formatting","name":"Conditional Formatting"}]}],"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":4991,"limit":3},"title":"Trending in Smartsheet Basics","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
![https:\/\/us.v-cdn.net\/6031209\/uploads\/IQIOTIQDOJDE\/columns-jpg.jpg<\/a><\/p>","bodyRaw":"[{\"insert\":\"When I select a REPORT to use for a Chart Widget- it is selecting the entire report (it is not letting me select the columns of that report that I would like to chart)... I have probably 20 columns on the report and it looks like it will only allow me to chart these 3 shown below?? any ideas how I can actually see the entire list of columns. If I click edit - it takes me back to report that I selected for the chart but it is completely selected and \\\"frozen\\\" it will not move in any way to allow me to select or unselect columns. Thanks for your quick help and advice! \\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/IQIOTIQDOJDE\\\/columns-jpg.jpg\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/IQIOTIQDOJDE\\\/columns-jpg.jpg\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2021-09-08T20:55:01+00:00","insertUser":{"userID":121314,"name":"Jo-Ann Turning","url":"https:\/\/community.smartsheet.com\/profile\/Jo-Ann%20Turning","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-10-18T16:34:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/304331#Comment_304331","embedType":"quote"}">](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/IQIOTIQDOJDE\/columns-jpg.jpg\"){kind=link}
