Report Builder: Include and exclude on same column

Mark Lappegaard

In Report Builder for WHAT, I want to include one term but not another for a specific column.   (See attached.)

 

But the "Exclude selected items" check box is all or nothing.  

 

It can't be this hard. There has to be a "contains" and "does not contain" filter ability. 

 

To be clear, I want a result that will show all the primary tasks that contain the word "Assembly" but do not contain the word "Resource". 

 

What am I missing, please?  

Ezra

A "Helper column" that simply duplicates your Primary column's (fill down: =Primary@row) would allow you to filter all that has criteria:

- Primary Contains "XXXX"

- Helper Doesn't Contain "ZZZZ"

Andrée Starå

Hi Mark,

Great idea! That would be a great addition to Smartsheet features.

Please submit an Enhancement Request when you have a moment.

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Mark Lappegaard

Thanks, Ezra. 

 

To be clear- I would have to create this helper column in all my source sheets, correct?  This isn't some sort of "calculated column" I can create like within an Excel Pivot Table, no?

 

I ask because I am actually creating two similar reports, each with other a hundred source sheets... 

Mark Lappegaard

Honestly, if that is the only solution, I am just going to export to Excel and do all my work in a pivot table. 

Andrée Starå

Another option could be the Smartsheet Premium Add-on, Pivot App.

https://www.smartsheet.com/marketplace/premium-apps/pivot-app

Would that work?

Ezra

If it's only a couple hundred source sheets, I'd just run through proceduraly  them in batches of 20...

• open all of them in tabs in a separate window
• add the column and give it whatever helper column name you choose (I usually like to keep the names logical, but it could be "Sparkle" or "DearDiary" if you feel like smiling whenever you run across it)
• copy the column name and run through all the tabs to add the column as a first pass.
• the next pass would be pasting the formula at the top of that column
• final pass would be to copy-down (ctrl-D) the formula for that column

Wouldn't take more than 15 or 20 minutes.. and it would be more useful than exporting it, and stepping through another few processes to get what you want.

Mark Lappegaard

That's a golden workflow, Ezra.  Thanks for sharing your technique. 

Mark Lappegaard

Thanks, Andree.  Looks like it might work too. 

I'm disappointed it's an add-on.  In my limited perspective, it should not be that hard to build a two part filter in Report Builder.  Simply allowing the ability to click "exclude" for one criteria and "include" for another in the same column shouldn't require paying more money.  But, again, I may not appreciate the complexity.   Still, thanks for your suggestion. 

Andrée Starå

Nicely done!

Thanks for sharing Ezra!

To add. Another option could be to have a Master sheet where you add the column and a row with the formula with a unique character in front of it.

Something like this. *=IF(COUNTIF(A@row:F@row; 1) = 6; 1)

1. Add column
2. Add row
3. Add formula with the unique character in the beginning
4. Copy that row to all the other sheets, and that would create the new column automatically on those sheets
5. Open each sheet and do a Find and Replace with and replace the * with nothing and that would activate the formula
6. Fill the rest of the column with the formula

Would that work/help?

Andrée Starå

Happy to help!

I agree that it should be possible to Include/Exclude and I think Smartsheet will add that feature soon. The Premium Add-on, Pivot would help, but it's much more powerful, so I don't think it's for this use case. It would work, but it's not why it's a premium add-on.

Please submit an Enhancement Request when you have a moment.

 

Mark Lappegaard

AHA!  I figured out a solution that worked in my limited case.  

 

I used the search operator " " to restrict my results to only the term I wanted (in this case "Assembly") which excluded everything I didn't want ("Resources").   

 

So simple. I feel foolish I didn't think of it before asking for help or going to the rabbit hole of workarounds but hopefully this can help others. 

 

Andrée Starå

So obvious it seems, but we all missed it ​​​​​​​

Thanks for sharing!

 

Michael Dillenberger

+1 for this feature. The " " solution won't work for me. I want to do a filter that has a start date "in the next _ days" but is NOT today. Doesn't seem possible without a 'this but not that' in the same column feature.