Report > Advanced filtering for exclusions

  • 2
  • Question
  • Updated 2 months ago
  • Answered
  • (Edited)
Within Report Builder, has anyone solved for a filtering scenario that involves excluding rows based on a combination of fields?

For example, if I have a dataset in which all of my users are assigned a letter (A, B, C, D, etc.) and also a number (1, 2, 3, etc.)

I would like to see all of the users who have an A, and all of the users who have a 1, but I want to exclude from view any of the users who have the combination of A and 1.
Photo of Matthew Lind

Matthew Lind, Champion

  • 3,604 Points 3k badge 2x thumb

Posted 2 months ago

  • 2
Photo of Marcelo

Marcelo, Champion

  • 3,412 Points 3k badge 2x thumb
What if in the filter you set it to Letter != A Number !=1 and then on the filter setting you set it to . (A and B)?
Photo of Matthew Lind

Matthew Lind, Champion

  • 3,604 Points 3k badge 2x thumb
Thanks Marcelo. I've found when I attempt that, I end up excluding all of the A's and then also excluding all of the 1's.
Photo of Rakesh

Rakesh, Employee

  • 360 Points 250 badge 2x thumb
Hi Matthew,
Can you try Marcelo's suggestion with (A or B)? 
Theoretically, (A ∩ B)' = A' U B'.

For your exact question, filters needed would be
Filter A: Letter A
Filter B: Number 1
Filter C: Not Letter A (!= A)
Filter D: Not Number 1 (!= 1)
In advanced logic: (A or B) and (C or D)
(Edited)
Photo of Matthew Lind

Matthew Lind, Champion

  • 3,604 Points 3k badge 2x thumb
Rakesh, I attempted your suggestion on the dataset and did not get the results I was hoping for. Records I wanted to exclude because they met BOTH of my criteria still appear in the results.

Given that in my use case, I have 4 pairs of values to exclude (I need to exclude A1, A2, A3 and B1), even if this did work, I would end up with at least 16 filters and an advanced logic string that makes me shudder.

The ability to generate a Calculated Field within a Report (in this case, to CONCAT) would give me a more straightforward solution and put Reports at more parity with Bionic Rules.
(Edited)
Photo of Rakesh

Rakesh, Employee

  • 360 Points 250 badge 2x thumb
Hi Matthew,
Concat should be one of the functions in the formula fields capability we have on our roadmap! 
Photo of Andy Roy

Andy Roy

  • 878 Points 500 badge 2x thumb
In your example, it sounds like there's a "Letter" field that might = A, and a "Number" field that might = 1.  If so, then you'd theoretically just use something like

Filter A:  Letter=A
Filter B: Number=1
and the expression:
 (A OR B) NOT (A AND B) 

Too bad we can't use NOT as an operator.  I just voted up this related Idea.  Finding another way still sounds like an interesting challenge, but being able to use "NOT" would really tie the room together.  ;)
https://community.gainsight.com/gainsight/topics/filtering-in-reports-for-does-not-start-with
Photo of Matthew Lind

Matthew Lind, Champion

  • 3,604 Points 3k badge 2x thumb
I was guarding against floating a solution to my own question, because I wanted to get some good opinions or creative solutions. You're right, Andy, in that I would use a NOT operator tomorrow if it was available.
Photo of Andy Roy

Andy Roy

  • 878 Points 500 badge 2x thumb
Keep hope alive! :)