What are the requirements for a report builder?

RDG manages an online training website for one of our clients that is fairly complex. The website provides various companies the ability to assign and manage users within departments, create courses with training aids and a list of exam questions, and schedule, those courses as required training to their users either directly, to an individual department, or for the company as a whole. The site allows users to login in, view their required training, and complete the courses' corresponding exams with instant feedback on the exam's score. Admins then have the ability to run reporting on who took what when and how well.

Our client came to us with a request to create a report builder so company admins could access, arrange, and filter various stored data values to create the exact report results they wanted. We had already been supporting several predefined reports that admins could run to get this information but the results were fairly rigid in the report structure. That is, of course, the main downside to predefined reports. Once set up they will consistently return the requested information but will only allow for customizations that the developer specifically adds. A well-built report builder on the other hand gives the admin significantly more flexibility in how the information is displayed, filtered, and sorted.

What are the requirements for a report builder?

For the purpose of this article, a report builder is a report creation tool that allows admins to create simple to complex reports based on a variety of related data sets. In this article, we will delve into the report builder infrastructure that is required for an admin to be able to create a report about how many exams each user took in the last month.

Base Content

The first thing to think about is what data sets are needed for the information we want to display and how are they related. In our example, we want information about users, their department, and their exams. Since department and exams are tied directly to the user, the user should be our base content with the others being attached data sets. A simple report using a single base content and its directly adjacent tables will often satisfy our admin's reporting needs. 

Columns

The second thing to think about is what do we want to display. In our example, we would like a column for the user's full name (User Name), a column for the user's department name (Department), and the count of how many exams they completed in the last month (Exam Count). As you can probably imagine there is some complexity to building out our columns. Looking at our example we can see three different kinds of columns, raw value, combined fields, and aggregated results.

For the Department Name column we simply want to display the raw value for the department's name from its data set. For the User Name column we want to combine two fields together since the first and last names are stored separately in the user data set. For the Exam Count field we want to display the results of an aggregation (count) of the joined exams data set.

At this stage, we also may want to specify how we want results to be displayed based on their field type. We may want to change the way a date is displayed from how it is stored in our data set. If booleans values are stored as 1/0, we may want to use specific language for each condition, such as active/inactive or true/false. If we are using a combined field column with a division operator we may want to show the results as a percentage instead of a decimal value.

Filters

The third thing to think about is what filters do we want. In our example we need to be able to filter the exams we will be counting to only those that were completed within the last month. We can manage filters that are not going to be displayed in the end report results in one of two ways. We can allow a filter based on the completed date field regardless of its inclusion in our columns or we can add a hidden raw value column and filter by that.

I find that adding a hidden option to columns is the simplest way to manage the filters. This also allows us to filter by the results of a combined fields column or aggregated results. In our example, we may want to also exclude all users that did not take any exams and that could be done by adding a greater than 0 filter to the Exam Count results.

Sorts

The fourth thing to think about is how do we want the information sorted. In our example, we want to simply sort it by the users' names. We use the same column selection logic as our filters so that we can also sort by combined or aggregated results such as the number of exams completed or by the user's full name. We of course also need to include the option for which direction to sort the results, ascending or descending.

Ability to Save for Repeat Use

The last thing to think about is whether or not we want our admins to have to go through this entire process every time they want to run this report or if we want to add a way for them to save the report for quick access later.