Showing "stacked" data / Statuses of items by swimlane

HalfSun

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)

Is it possible to get this in Smartsheets?

AdamSYNH

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.