How to create a stacked column chart from a report

KIML_CAN

Hi everyone, I'm trying to create a staked columns chart in my dashboard to show per Assigned to the number of cases per status. However, the chart shows up just as 1 big column. Would someone knows why?

Please see the photo for visual.

Thank you in advance for your help

Brian Wilson DC

@KIML_CAN I believe it has to do with the fact that their is a summation "TOTAL" level in your report. Your best bet is to create a simple data helper sheet to tabulate this data and use that as the reference for your dashboard. I have shown what that might look like below. You can make it its own file and have it refer back to your source sheet, but I've included it on the source sheet so you could see how it is calculating the data (click to zoom in):

So the left half I have replicated what your data might look like and then on the right where you see BOB, JEFF and KIM I have created the calculations to tabulate all of the data your report is showing you.

Then you would add your widget and select the data ranges below. It is important that you have your "assigned to" names first, because when you use the widget for the data you will want to make sure you use the "First Column as Series Labels" toggled on. That will take the names Bob, Jeff and Kim and apply them to the legend. So your data below:

Turns into this:

Using these settings:

If you want your chart to show the values, as well, click on the editor and go down to "SERIES" and choose "Always Show Value Label" and it will do this:

I hope this helps!

Regards,

Brian

Brian Wilson DC

@KIML_CAN I can't get the report to work into a chart with the number of variables you are trying to make. That's not saying it isn't possible and someone else in the community may know a work around, but I am unable to figure out how to get Smartsheet to apply it.

Your other option would be to simply make your widget your actual report. That way it would display the report on your dashboard. Additionally, you could publish the report document so people can click on the widget and look at the report itself. While not as clean looking as a stacked data column it would still provide them with opportunity to access the data in a read-only format. If you decide to go that route please let me know if you need help formatting it.

Brian Wilson DC

@KIML_CAN I believe it has to do with the fact that their is a summation "TOTAL" level in your report. Your best bet is to create a simple data helper sheet to tabulate this data and use that as the reference for your dashboard. I have shown what that might look like below. You can make it its own file and have it refer back to your source sheet, but I've included it on the source sheet so you could see how it is calculating the data (click to zoom in):

So the left half I have replicated what your data might look like and then on the right where you see BOB, JEFF and KIM I have created the calculations to tabulate all of the data your report is showing you.

Then you would add your widget and select the data ranges below. It is important that you have your "assigned to" names first, because when you use the widget for the data you will want to make sure you use the "First Column as Series Labels" toggled on. That will take the names Bob, Jeff and Kim and apply them to the legend. So your data below:

Turns into this:

Using these settings:

If you want your chart to show the values, as well, click on the editor and go down to "SERIES" and choose "Always Show Value Label" and it will do this:

I hope this helps!

Regards,

Brian

KIML_CAN

Yes Thank you so much

Brian Wilson DC

@KIML_CAN I can't get the report to work into a chart with the number of variables you are trying to make. That's not saying it isn't possible and someone else in the community may know a work around, but I am unable to figure out how to get Smartsheet to apply it.

Your other option would be to simply make your widget your actual report. That way it would display the report on your dashboard. Additionally, you could publish the report document so people can click on the widget and look at the report itself. While not as clean looking as a stacked data column it would still provide them with opportunity to access the data in a read-only format. If you decide to go that route please let me know if you need help formatting it.