Report/SQL Builder - Nested conditions

Question

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 protected], 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'...

Answer 1

I ended up changing the database to accomodate a simpler UI. One example of a nested query may have been, "Show me all regions where member data hasn't been uploaded in the last 30 days". The query would look like (pesudo):

SELECT region_name 
  FROM region
 WHERE region_id NOT IN (
    SELECT region_id
      FROM upload_history
     WHERE upload_date < DATE()-30
 )

Instead, I've just added a last_upload_date to the region table, and will allow the user to select that in the 'where' clause instead.

Here's the UI in its (mostly finished) form. Just needs a bit of polish. (Colours, logo's, etc. all TBD)

Thanks for your feedback + assistance!

Answer 2

This would take a more work, but I think it would be pretty slick if you could mimic the behavior in Mac Mail when searching through emails.

You could just have one big search box and allow users to just type in keywords. For example, in Mac Mail you can type "from:John" and it will search for emails from John.

In your example:

"Show me all users who have received emails in the first week in May, whose surname contains 'Smith'"

You could allow the user to type in something like the following:

^{download bmml source – Wireframes created with Balsamiq Mockups}

Check out the behavior in Mac Mail to get a better idea of how this works.