Create Stacked Bar Chart from multiple columns of a summary report

Baptiste.J

Hi there,

I've been runing around in circles for a while now and was hoping you could help.

Here is my issue:

I am using smartsheet to do some UATs on a project. I have several test sheets and would like to do some reporting on testing advancement by using stacked bar charts.

Agregating all my test sheets into a summary report I want to have the amount of test cases / Status / Tool.

How one of my data sheets looks like:

My report summary:

Now I want to build a nice stacked bar chart on my dashboard:

What I have:

But I want something like:

What am I doing wrong here?

Thanks for your help,

Baptiste

Baptiste.J

Hi I ended up by addind a helper columns in every test sheets (12!)

Then i summarized everything on a report by suming every status.

And there we have it!

Genevieve P.

Hi @Baptiste.J

Currently Chart Widgets can only take the top-level data from a Grouped Report as their data source (see the announcement about using Reports for Chart Widgets here). Please provide your feedback to our Product Team through this form!

As an alternative, you could create a Metric sheet using cross-sheet formulas to gather your data. Since you have a number of sheets you'll need to add together multiple, separate cross-sheet formulas, but it's possible.

In your Metric Sheet, one column would be for the Tool, one column for OK, and one column for KO. Then you would use a SUMIF formula to get the data...ex:

=SUMIF({Status Column Sheet 1}, "OK", {Tool Column Sheet 1})

Then add together each sheet's SUMIF formula with new references:

=SUMIF({Status Column Sheet 1}, "OK", {Tool Column Sheet 1}) + SUMIF({Status Column Sheet 2}, "OK", {Tool Column Sheet 2}) + =SUMIF({Status Column Sheet 3}, "OK", {Tool Column Sheet 3})

Let me know if this makes sense or if it would help to see screen captures.

Cheers,

Genevieve

Baptiste.J

Hi I ended up by addind a helper columns in every test sheets (12!)

Then i summarized everything on a report by suming every status.

And there we have it!

Genevieve P.

Hi @Baptiste.J

That works as well! I'm glad you were able to find a solution.

CycleBagEd

I have to say, I find this less than acceptable for a product of this type. We shouldn't have to pollute our raw data in order to report properly.

DianeQ

Yes, this is an expensive tool and users shouldn't have to be inventing their own workarounds for charting basic summary information.

Where is making this functionality more user-friendly on your product roadmap? To be included in 2022 features? 2023? Not on the roadmap at all?

Kamil Tomaszuk

DianeQ

Apr 1, 2022

Yes, this is an expensive tool and users shouldn't have to be inventing their own workarounds for charting basic summary information.

Where is making this functionality more user-friendly on your product roadmap? To be included in 2022 features? 2023? Not on the roadmap at all?

The same question here. I have more tha 250 sheets to summarize. Should I change every one of them?

Ami Veltrie

DianeQ

Apr 1, 2022

Yes, this is an expensive tool and users shouldn't have to be inventing their own workarounds for charting basic summary information.

Where is making this functionality more user-friendly on your product roadmap? To be included in 2022 features? 2023? Not on the roadmap at all?

I have the same question. Where is this feature at on the roadmap?

Genevieve P.

Hi @Ami Veltrie

Please submit your feature request to the Product team by creating an Idea Post in the Smartsheet Product Feedback and Ideas topic here in the Community. This will allow other users to vote on your enhancement idea! The more votes a feature has, the more likely it will be addressed by the Product team during their monthly reviews.

Cheers,

Genevieve

Ahtasham Rizvi

Requesting similar functionality as requested by Baptiste.J.

Thank you for taking the time to submit the idea. Voting up!

(Tried to vote up, unable to!)

(The workaround shared here is a workaround and is not always practical)

Genevieve P.

Hi @Ahtasham Rizvi

This is a discussion post - it doesn't look like an Idea has been submitted yet to the Product team for this specific feature. Please feel free to either create a New Idea or to add your voice to this similar Idea:

Dashboard Charts - Secondary Axis, additional data points, target line

Michele Riemann

Dec 13, 2022

Hello - It would be nice if we could add a secondary axis to charts to show additional data points in one chart. Attached is an example of this ability in excel.

https://us.v-cdn.net/6031209/uploads/474U9366MTWK/secondary-axis-screen-shot-1-png.png

https://us.v-cdn.net/6031209/uploads/Y3MB4H0DZRUK/secondary-axis-screen-shot-2-png.png

---

View Post

Cheers,

Genevieve