How do I create a more complex Bar chart and get it into a dashboard?

Ron Zanetti

I have a multi-metric Bar chart that so far works well in excel. It currently has 3 reported metrics: Prior Period Milestone, Current period Milestone, and Target Milestone. I used the transparency and stacked graph features to make this work in excel, which I could not figure out how to use in Smartsheet. I could use some help determining if Smartsheet can offer a nice Dashboard UX for a Smartsheet built Bar chart, or if I need to stay with excel.

If excel is the way to go, I would like to explore any possibility of using the Web Content widget (or something) to pull in an active excel chart.

I also have an ask to add additional Harvey Balls on overall health.

Any suggestions or tips greatly appreciated!

-RonZ

Paul Newcome

Can you provide a screenshot of your current chart?

Ron Zanetti

Hi Paul. Sure thing: I covered the project names, but they're all 1 string in the label space.

Ezra

I just recently built these with thanks to L@123 reminding me about a workflow

Once you build it in Smartsheet, you no longer have to spend time on:

• exporting data
• manipulating it in another software
• importing that information into Smartsheet
• generating the chart
• making sure that everyone is looking at the smartsheet info and not the spreadsheet that they saved to their desktop three months ago

Building the setup can be daunting at first... but once it is running and you are getting data out the other end, it is very satisfying.

It took a couple sheets to accomplish these things.. One sheet to gather the metrics for each brand and status/health. That feeds a new line of data every day (or week or whatever) to a second helper "history" sheet. Finally a couple reports to gather the appropriate data, but the end result charts out pretty well..

Here's a time-based health chart.. pulling data from the metric history through reports

TLDR: Project Data -> Metric Sheet -> daily workflow -> History Sheet -> Reports -> Dashboard Graphs

A bit more fine-grained chart...

This first graph of number of the tasks' by health, by project (if active)... and another graph of number of projects by brand and project state:

First:

Control Center Summary -> Report -> Dashboard Graph

Second (similar to the time-based graphs above):

Project Data -> Metric Sheet -> Report -> Dashboard Graphs

Paul Newcome

Unfortunately I don't think a chart quite what you are looking for is possible within Smartsheet. In this instance it may be better to stick with Excel.

Feel free to Submit a Product Enhancement Request when you are able and provide as much detail as you can about the way your chart is working. You can even include a link to this thread in your Enhancement Request.

Ron Zanetti

Thanks Paul. Is it possible to 'stack' the milestones so that they are not represented as a bar chart, but each milestone metric starts from 0 (as I can in excel?)

Also, if I keep it in excel, how well does the excel 365 integration work, and will it pull in a chart, or is it only for data level iintegration?

Paul Newcome

I am very out of practice with Excel (as in it has been more than 10 years since I had anything "complex" to build). I am not sure I completely understand your first question, and I have very little experience with integrating excel and Smartsheet.

I just know that having the overlapping bars as you have shown in your previous screenshot is not possible within Smartsheet.

Ron Zanetti

Ok, thanks. I'll look to Tier 2 for excel integration. Thanks for the quick responses!

Paul Newcome

Sure thing. Sorry I wasn't more helpful.

Ron Zanetti

Hey Paul. One more question is you don't mind: Re: the X Axis legend, I want to set the legend to a 100 scale with a mark every 10%, ) - 100, regardless of the data (which maxes out at 100). Are there options to set that in the bar charts in SMartsheet?

Paul Newcome

Unfortunately that is not an option. There MAY be a way to set your table up depending on your exact data, but I'd have to do a bit of testing to see if my theory would pan out.

Ron Zanetti

Understood. Thank sPaul. Really hope the new charting platform due out next year does this stuff and more! ;-)

Tony DiPaolo

Sorry, gents, but I did not see an answer to Ron's question about using the web widget to put an Excel chart (mine has both lines and bars combined), and I cannot do it in Smartsheet, so I'd like to use the widget tool to show my Excel chart (which is hosted in OneDrive) in the Dashboard. But when I try, I get the error: "SharePoint refused to connect "

Thank you for reopening this for me. Tony.