Report with both Null and Not-Null values

Sbott

Hi all.

I have been bumping my head against a wall on this one for over a week. I am pretty sure there is a way to get this to work but do not have the Smartsheet skill set to figure it out.

Problem: The dashboard is not displaying the statuses with the correct bar chart coloring. How to do a report so it displays all statuses regardless of the value.

Objective: to have the report to include 5 of the statuses for each SME so that the dashboard bar color remains consistent. It is changing colors based on the order of the statuses returned in the report.

I have a grid sheet being used to track articles being written by various SME on staff. There are 6 statuses being used in a dropdown to track the progression of each article. (Note: I had to put numbers in front to keep them in the progression order)

I have created 16 reports for each of the 16 SME's to show the number of articles by status on a dashboard. The report filter is pulling the SME by name (example below Beau) and is pulling the Status to not include "Not Needed"

As you can see from the print screen below, Beau has articles in 3 of the statuses and the other 2 are not displaying as they have null values. I am wondering if one of the possible solutions to my problem is to have all of the statuses show up (filter) in the report even if they are null value. This way each status would show on each SME and the bar color could be set. With my limited skill set, I can't figure out how to do that, so it displays all statuses regardless of the value. Right now I am going in and manually updating the colors of the status for all 16 SME's

Any help would be greatly appreciated

Shauna

Paul Newcome

You would need to have this calculated on a separate sheet with formulas. It will not be possible using reports.

Basically you would have all names listed down the first column. Then you would have additional columns going off to the right for each of the different statuses. These columns would use a COUNTIFS to count how many times each name is listed for that particular status.

From there you would add 0.00001 to the end of the COUNTIFS so that the back-end is still greater than zero (registers on a chart) but hide the decimals so the from end only shows zero.

Charting from this sheet will show a zero count for the statuses as applicable but still maintain your bar colors.

Paul Newcome

Happy to help. 👍️

Paul Newcome

You would need to have this calculated on a separate sheet with formulas. It will not be possible using reports.

Basically you would have all names listed down the first column. Then you would have additional columns going off to the right for each of the different statuses. These columns would use a COUNTIFS to count how many times each name is listed for that particular status.

From there you would add 0.00001 to the end of the COUNTIFS so that the back-end is still greater than zero (registers on a chart) but hide the decimals so the from end only shows zero.

Charting from this sheet will show a zero count for the statuses as applicable but still maintain your bar colors.

Sbott

Paul, I can't thank you enough. That is exactly what I needed.

Truly appreciate your help.

Paul Newcome

Happy to help. 👍️