Create Chart Widget Using a Report (not a Sheet)

Pam Tadsen
Pam Tadsen ✭✭✭✭✭
edited 12/09/19 in Smartsheet Basics

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

«1

Answers

  • Sasan
    Sasan Employee
    edited 02/26/18

    Pam,

    At this time the source data for a Chart needs to come from a sheet.  A few alternative suggestions:

    1. 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.
    2. 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

    Screen Shot 2018-02-26 at 1.20.23 PM.png

  • Pam Tadsen
    Pam Tadsen ✭✭✭✭✭

    Hi Sasan,

    Thanks for the suggestion. I'll give that a try!

    Pam

  • Sasan
    Sasan Employee

    Pam,

    We just released the new capability to select data 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
    Pam Tadsen ✭✭✭✭✭

    Wonderful news, Sasan - thank you! I can't wait to take it for a test drive!

  • 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.
    Genevieve P. Employee Admin

    Hi @Mike Doros

    When 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 heading Chart & Data. (See here for more information on Chart Widgets).

  • Jo-Ann Turning
    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å
    Andrée Starå ✭✭✭✭✭✭

    Hi @Jo-Ann Turning

    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 some more screenshots of the Report? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    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 by marking 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Jo-Ann Turning

    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
    markh10 ✭✭

    So each different Chart, I would need a separate Report?

  • Genevieve P.
    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
    markh10 ✭✭
    edited 09/21/22

    @Genevieve P.

    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.
    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 the Chart Widget configuration you can then identify which columns 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