I'm working on a project which will let a user create reports by building up numerous rules, so they can see the data they're interested in. The UI needs to be powerful yet simple. The Balsamiq's below will hopefully explain it a bit better than I can using words!
It starts off pretty simple. You can select the fields you're interested in, and start adding rules...
It gets a little bit ugly when it comes to having a nested query. The example below: "Show me all users who have received emails in the first week in May, whose surname contains 'Smith'".
The real problems start when we have nested queries in nested queries. This particular report (fictional, pointless... but you get the idea!) is "any users where their surname contains 'smith', who have never received an email from email@example.com, but have received an email in the first week in May".
Is there a neater way of building this type of UI? Realistically, most reports will be fairly simple, and I'd expect very few to have 3 or more nested subquery's - but I know there will definitely be some that do have 3.
Context: A database which contains members of a national group, ~100000 members. Users are split into regional levels. Communications/emails sent via the system.
Technical note: The 'in/not in' linked tables will be where a foreign key exists between the selected field and another table. I'm yet to work out how to handle non-unique FK's on one table (e.g. sender/recipient), and haven't yet worried about 'AND' vs 'OR'...