the problem

Users wanted to look at data from multiple databases in a single report. This required computing a complex join of the two databases, which would need to be configured differently depending on how the current report was configured, which fields and groupings the user wanted to see, and what the user wanted to use as the base record. In other words, the nature of the join is different depending on the exact constellation of rows, columns, and row groups.

The product manager knew he needed a way to provide this type of functionality but was concerned that the interface would need to be very complicated.

Two views of the 'select fields' interface.

the process

After discussing with the PM what the users wanted and why this was complicated, my first step was to watch some tutorials on databases and then to read the documentation for PosgreSQL. I knew that I needed to understand SQL in order to understand what the users' options were and what they would mean.

I then began interviewing users and analyzing our existing interface. My analysis showed that the existing interface mixed and matched pieces of certain user tasks. For example, it combined selecting the order of columns with selecting which columns to display. This poses a problem when trying to order the columns: there are way too many present to see what the order is. And it poses a different problem when trying to select columns: the columns are in an arbitrary order, making it difficult to find the ones you want.

After cleaning up the initial interface, I began looking at how to incorporate the new functionality. As I started to organize the various things a user could do, I came up with a logic that could infer the configuration of the join without any additional input from the user than which columns and rows they wanted. I mocked up a design that included minimal changes to the ui but allowed the user to create the complex joins that were needed. After checking with the PM that this met the need and checking with engineering that this was feasible, I began testing the interface with users. After a couple of iterations, I wrote a spec and provided it to the engineering team.

Picture of mockup section which included explanation of join logic.

the solution

The solution was a system that did not require any additional input from the user in order to allow this complex new functionality, thereby completely allaying the concern that this complex functionality would necessitate a horribly more complex interface. I added a couple of controls related to it, in order to expose the different system states that were being enacted by the new join functionality. This was mainly so that power users could see which system state they were currently in. It also gave them the option to excercise explicit control over this complex report creation logic. These controls were hidden in areas that only a power user would see, and they are restricted to certain permissions to ensure that someone who is likely to be confused by them wouldn't run into them.

Picture of the group by selection interface and the reports that result from two selections.