I didn't have much time to dig, but we use a similar query with a few extra criteria. I don't see anything in there that is incorrect. Is it returning inactive users in a regular query or is it only in the Report?
I see the issue is with OR condition in combiation with Not Equal to (!=) Geography and Nationality.
If you are looking for all active/inactive users matching a geography/nationality, just change the concatenation operation to OR for both Geography and Nationality.
But if you are looking for all active/inactive users not matching a geography/nationality, change the concatenation operation to AND for both Geography and Nationality. But the results will depend on how the Geography and Nationality are filled for all users in the Organization. e.g., blank or mismatching values would give wrong results
Hope this helps.
Hi the issue is I am getting status of 0 and 1 and I only want 0 and geography != US or nationality != US. The status=0 is being discarded. While the others are being properly evaluated.
Using your input Or Nationality and Or Geography gives us with all users including US.
If I make And Nationality and And Geography I will get only users with both != US.
The status is not being applied at all as for with And And or And OR or OR OR I still get status =0 and status=1. The root of the problem is getting the status=0 to be listened to.
As I mentioned the query is not executing as expected just because of the OR clause.
OR clause bypasses all AND clauses. So the search result constitues all the results matching the AND clauses along with all results matching the OR clause even if they are not satisfying the AND clauses.
As you are generating a report, other workaround could be, just keep the status clause in the query and modify the PLMXML closure report to have a conditional clause to export the users who are matching the criteria (Nationality != US and Geography != US).
Thanks for the explanation on the OR bypassing the Ands. I ended up creating two queries with all ands.
I would like to know of Query Builder is built so that Ands and OR's can be logically stacked?
(and status = 0
and Nationality != US)
and Geography !=US)