Hi all!

I need to write a report where I can get all users who are part of grp A but not part of grp B and not part of grp C

Scenario:
Grp A = reply required
Grp B = answered Yes
Grp C = answered No

Option 1: My original thought was to do a join where my columns are:
user_id
grp A
grp B
grp C

Where the grp columns would either display the grp name or be empty. Then I can run all my reports off of this DB view.

Option 2: I can easily write a script to get the data, but prefer to not jump through the hoops of creating a UI page and fiddling with jelly to be able to add a pretty report on a dashboard.

Option 3: Build out a table for the data (boss tossed this one out there). Def doable, but for ~200 users for the current ask, well, it feels like overkill.

Are there other solutions? Has anyone done a similar self join? I have not been able to get the DB view to even simply display all users in one group. Admittedly, I did not add the user table to the view first, I was just trying to limit what came from the grpmember table.

At any rate, any insights or suggestions would be much appreciated!

Edit: For anyone who doesn't want to read the comments, I ended up writing a script include to compare two glide lists from sys_user_gpmember. This generated a comma separated list of sys_ids from the sys_user table. I dropped a call to the script include in the condition builder on sys_user for "sys_id is one of <javascript: new GroupTracking().noReply()>

They wanted additional breakdowns, so I added other functions in the same script include to generate additional reports. Thank you to those who replied and gave me the insight I needed to make this work.