Skip to main content
SmartRecruiters

Report Builder Deep Dive

Plans:
  • Personal
  • Team
  • Corporate
Editions:
  • Corporate
  • Global

Report Builder gives customers the ability to: define, combine and extract mission critical data from SmartRecruiters. To help our users get the most value out of this product, this documents defines core concepts underlying the feature architecture.

Before reading this, its best to review our Getting Started guide.

Key definitions

Entity

  • An entity is a core component of SmartRecruiters. Examples of entities include: jobs, positions, applications.
  • When creating a report, you first select the entity that you want to build your report around. You can only choose a single entity for each report.

  • The terms entity, row entity and reporting area are used interchangeably

Data point

  • Each entity has a set of data points associated with it. Think of data points as the fields that you want to include in your reporting.
  • Example of data points that are part of the Candidate entity: Candidate first name, candidate email, Candidate location.

  • We use the terms data points and data fields interchangeably

Report Builder - Data Model Deep Dive (1).png

Rows

  • Each row in the report represents a specific instance of an entity.

  • In the example below, each row of the report represents one Application.  

Columns

  • Data fields that make up a report are represented as columns in the report.

  • Each data field is represented as a single column.

  • Different data fields belonging to different entities can be included in a single report.

  • Which data fields can be chosen for a particular report depends on the relationship between entities.

Let’s take the concept of rows and columns and build an example:

  1. You create a new report, and choose “Application” as your row.

  2. Next, you’ll add columns to your report by selecting data points such as “Application Id”, “Candidate First Name”, “Candidate Last Name”, “Job Title”, “Ref Id”, “Application Status”.

  3. Preview the report and you’ll see all of the data points that you’ve selected represented as columns. Each row of your report will represent one candidate application in the system.

The question that you might be wondering is - “Okay, so I select any entity for my row, how does Report Builder know which data points can be added as columns to the report?”

Relationships

Report Builder is built on underlying relationships between entities in SmartRecruiters. Again, entities are the big, core components of SmartRecruiters such as jobs, positions, applications, etc. The relationship between entities allows Report Builder to be able to combine data from different parts of the system inside of a single report.

Here is a visual model of what a Candidate → Application → Job relationship could look like in the system:   

Even with only a couple entities, you can already see complex relationships going on between them.

We categorize entity relationships into 3 different types: Many to many, One to many, and one to one. Visually, they can be represented like this:

Many to Many: Many candidates can be linked to multiple jobs

One to Many: One candidate can have many applications.

One to one: Every application is linked to one job only.

All entities in the SmartRecruiters has one of these 3 relationships with other entities. Report Builder understands these relationships and is able to traverse them algorithmically when you create a report. However, the tabular nature of the data output, row/column model, means that Report Builder supports only 2 types of relationships: One to one and One to many.

Report Builder does NOT support Many to many relationships. If two entities have this relationships, then choosing either entity as your row, means the other entities’ data points could not be added as columns in the report, i.e. the entities cannot be joined.

When we encounter a Many to many relationship between entities, we try to break down the relationship into a set of One to one or One to many relationships instead. More on this later.

One to One relationships

Returning to the previous visual, let’s take a chunk of it to see what happens when we break down the complexity and identify the relationships:


We see that there is a One to many relationship between Candidate and Application and a One to one relationship between Application and Job. The One to one relationship is the simplest. Report Builder instantly understands that the data points of theses two entities can be joined together without conflict.

The most important one to one relationships between entities that exist in the platform today, include:

Note: The direction of the relationship also matters. For example, when moving from candidate to application it is a One to many relationship. One candidate can apply to multiple jobs, and thus have multiple applications. In the opposite direction, moving from application to candidate is a One to one relationship. A single application always belongs to only one candidate.

Here is a combined ERD (Entity Relationships Diagram) of all the One to one outlined above:

 

 

The arrows represent all the One to one relationships between these entities. Follow the One to one data direction flow to understand how data from different parts of the system can be joined together.

For example, If the starting entity (each row) of your report is Application then you’ll be able to pull in data fields from:

  • Application

  • Candidate

  • Job

  • Job Fields

If the starting entity (each row) of your report is Position then you’ll be able to pull in data fields from:

  • Position

  • Job

  • Job Fields

 

If the starting entity (each row) of your report is Hiring Team Member then you’ll be able to pull in data fields from:

  • Hiring Team Member

  • Job

  • Job Fields

Access the full diagram of one to one relationships between entities we have in Report Builder, here:

RB_ One to One Relationships (1).png

One to Many relationships

One to many is the second type of relationship that is supported in Report Builder. Again the tabular nature of the data means that your row must have a one to one mapping with your column. To make this “one to one mapping” we model One to many entity relationships in Report Builder using aggregations.

Aggregations are simply a summary of data, and they provide that one to one mapping between row and column in the report because many data points are summarized into a single whole.

Different aggregations types include:

  • Count: ⇒  how many unique values are in this list of values? Example: Count of Applications for a Job

  • Sum* ⇒ what’s the total after adding all the values together? Example: Total number of days an application spent in a particular status.

  • Minimum* ⇒ what’s the lowest value? Example: Of the candidates 3 applications, what was their Earliest application creation date.

  • Maximum* ⇒ what’s the highest value? Example: Of the 4 positions on the job, what was the Latest position creation date.

  • Average* ⇒ add all values and divide by the count of values. Example: Average Time-to-hire for all positions.

  • List ⇒ takes all values and generates a list

Those aggregations denoted with an * must have numerical data types (e.g.numbers or dates). Only numerical data types could be summarized in this way because a calculation needs to performed on it.  

The concept of a List aggregation is probably the strangest one. However, we’ve noticed that our users frequently request a “list of Recruiters” to be presented as a single column in a report. This type of aggregation collects all of the values to generate a single “list” value.

Aggregations are already pre-built data points in the Report Builder interface. You can select from a list of aggregations from the columns dropdown picker, same as you would for any other non-aggregated data point. Here’s an example:

Earliest Application Date, Number of Applications, Number of Candidates are all examples of aggregated data points.

The diagram below shows the One to many relationships between entities. It should reveal which entities would have aggregations as data points.  

Again, follow the One to many relationship flow to understand where you’ll be able to pull aggregation data points into your report.

For example, if the starting entity (each row) of your report is Job Fields then you’ll be able to pull in aggregation data fields from:

  • Hiring Team Member

  • Job

  • Positions

  • Applications

Here is the full One to Many relationship diagram:

RB_ One to Many Relationships.png

Putting it all together

Entities, data points, rows, columns, and relationships—that’s what powers Report Builder. The power of Report Builder is that it can traverse this web of relationships automatically, and that the underlying model is extensible. As the entities inside our product grow, for example when new features are added, the data available from Report Builder will grow as well.