1

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...

Step 1 - pretty simple!

Step 2 - adding some 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'".

a little icky...

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".

oh dear.

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'...

2
  • Did you by any chance release this as an open-source tool?
    – player87
    Mar 29, 2017 at 20:45
  • I've changed jobs since, I'm now reworking it in React and will release the frontend code. Backend was using Dynamic Linq-to-SQL, the new one will just be orchestrating API calls using GraphQL :) Dec 4, 2017 at 18:08

2 Answers 2

2

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)

The UI

Thanks for your feedback + assistance!

2
  • 1
    Since each filter can be OR or AND arbitrarily, how do you deal with, on the server, the lack of grouping? (region = X AND region = Y OR region = Z) needs some kind of nesting
    – blockhead
    Jun 27, 2017 at 7:26
  • This came down to altering the UI slightly to allow only "AND" or "OR" in each sub-clause, and using the other option (AND/OR) in either the sub-clause or additional clauses. e.g. " 'Region is X AND Job Role is Electrician' OR 'Region is Y AND Job Role is Electrician' OR 'Region is Z AND Job Role is Electrician' " vs " 'Region is X OR Region is Y OR Region is Z' AND 'Job Role is Electrician' " Dec 4, 2017 at 18:05
0

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:

mockup

download bmml source – Wireframes created with Balsamiq Mockups

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

1
  • Thanks! I'm not sure this proposed design would be suitable when the queries start getting a bit more advanced. Even for something like: "Show me [deails] where user's town in (towns where region is North-East)", the mockup could have trouble expressing that. I'll have a look at Mac Mail in case I'm misinterpreting the mockup. May 5, 2015 at 14:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.