Showing "stacked" data / Statuses of items by swimlane

I have multiple items that each have their own statuses and that roll up to "workstreams" or swimlanes.

On the dashboard, I would like to show the progress of the items by swimlane without breaking it out into separate widgets for each. This would ideally be a stacked bar where each bar represented the Items and was broken into "stacks" by Status.

I have created a report that summarizes the amount of items in each swimlane with a secondary grouping and summarizing by status.

Example of what I would like it to look like (ignore the color scheme)

Screenshot 2025-09-04 152025.png

Is it possible to get this in Smartsheets?

Find more posts tagged with

Reports

Dashboards

Accepted answers

This may not be the most straightforward solution, but I solved this problem on a single sheet by using a metrics sheet and cross-sheet formulas. You can make this mostly dynamic by extending your IDX/Level columns beyond the total number of swimlanes you have. You'll need:

Main Sheet

Helper Column identifying the level of your swimlane rows

=COUNT(ANCESTORS())

Helper Column identifying the parent of each row

=PARENT([Primary Column]@row )

Metrics Sheet

Level Column - simple number column to identify your swimlanes by their indent level on your main sheet, e.g. 0/1/2 based on how many ancestors they have

IDX Column - simple number column to identify which indexed item you want (1, 2, 3, 4) from the INDEX/COLLECT formula

Primary Column - Listing your swimlanes using the INDEX/COLLECT formula

=IFERROR(INDEX(DISTINCT(COLLECT({Main Sheet - Primary Column}, {Main Sheet - Level column}, Level@row )), IDX@row ), "")

To Do Column/In Progress Column/Validation Column/Done Column - Column formula counting the number of rows that have the status you have in the the column header per swimlane

=IF([Primary Column]@row = "", "", COUNTIFS({Main Sheet - Parent}, [Primary Column]@row , {Main Sheet - Status column}, "Whichever status you want to count"))

Using the metrics sheet as the source for your stacked bar chart you'll get the output you're looking for.

If your swimlanes are actually sheets the formulas get much more complex, or you'd have to have multiple metrics sheets pulled together using a report to get the output you're looking for.

All comments

This may not be the most straightforward solution, but I solved this problem on a single sheet by using a metrics sheet and cross-sheet formulas. You can make this mostly dynamic by extending your IDX/Level columns beyond the total number of swimlanes you have. You'll need:

Main Sheet

Helper Column identifying the level of your swimlane rows

=COUNT(ANCESTORS())

Helper Column identifying the parent of each row

=PARENT([Primary Column]@row )

Metrics Sheet

Level Column - simple number column to identify your swimlanes by their indent level on your main sheet, e.g. 0/1/2 based on how many ancestors they have

IDX Column - simple number column to identify which indexed item you want (1, 2, 3, 4) from the INDEX/COLLECT formula

Primary Column - Listing your swimlanes using the INDEX/COLLECT formula

=IFERROR(INDEX(DISTINCT(COLLECT({Main Sheet - Primary Column}, {Main Sheet - Level column}, Level@row )), IDX@row ), "")

To Do Column/In Progress Column/Validation Column/Done Column - Column formula counting the number of rows that have the status you have in the the column header per swimlane

=IF([Primary Column]@row = "", "", COUNTIFS({Main Sheet - Parent}, [Primary Column]@row , {Main Sheet - Status column}, "Whichever status you want to count"))

Using the metrics sheet as the source for your stacked bar chart you'll get the output you're looking for.

If your swimlanes are actually sheets the formulas get much more complex, or you'd have to have multiple metrics sheets pulled together using a report to get the output you're looking for.

Hmm, I may be able to modify this idea to work for me… the only problem is that the items come in via a form where I ask users to identify which swimlane the item belongs to. So they do roll up to the swimlane, but not through levels. But I could probably figure out a COUNTIFS that says to count based on which swimlane is identified?

I'll play with this a bit and see what I can figure out. Thank you for the cross-sheet formula idea - I suspect that will be integral to this solution!

That will work, the DISTINCT in the INDEX/COLLECT formula will give you a distinct list, so instead of PARENT you’d just have a condition matching the swimlane column. Let me know if you get stuck, post here and I’m sure I can help modify the formulas appropriately 🙂

Thank you! I do seem to be getting stuck.

I don't need the level part, correct?

so removing the parts that are struck out should work?

=IFERROR(INDEX(DISTINCT(COLLECT({Main Sheet - Primary Column}, ~~{Main Sheet - Level column}, Level@row ))~~, IDX@row ), "")

If yes, then I am missing something else, because unfortunately that is not working for me.

For the counting the statuses - I want to make sure I am understanding correctly that it will require a column for each status, correct? Then if the Swimlane matches, what is in the primary column, I want to count it. This makes sense - just need to get that primary column one to work!

ok! I got it to work! Thank you!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.