Using Dimensions in List Reports

A list report is very easy to build in Explore, but the Dimension and Additional Column selections will make a big difference to the end result. Here are our top tips with accompanying examples. 

This article is part of a series and we recommend reading through the articles in the following order:

1. Introduction to List Reports
2. Creating a Simple List Report
3. Creating a Detailed List Report 
4. Using Dimensions in List Reports (this article)
5. Formatting a List Report 

Multiple Data Sources - use the Additional Column dim table 

It is common to add multiple metrics to your list report, and they may come from multiple data sources, e.g. your core HRIS employee data and Recruitment. 

In the below example, the query is trying to report the end of period Headcount and Open Requisitions last year by job level, using the Additional Column ‘job_level’ from the employee data table. 

The query will run successfully, but on closer inspection, you will see the Headcount is split by job level as expected, but the Open Requisitions results are not split. This is because you are using an employee table column with a requisition table metric. Your raw job level data is coming from a different data source to your requisition data.

Never fear! Your next step is to check your Additional Columns for a dim_job_level dim table. 

This is linked to a one level dimension so go ahead and select ‘level1name’ and run your query. 

Now you will see both Headcount and Open Requisitions split by job level. 

This is because the dim_table (and columns like ‘level1name’ within the table) will also include the necessary logic to make joins with other data sources and related metrics. 

If you don’t find a dim_job_level table, or your metric still doesn’t split when you use the dim table, it’s time to raise a ticket or speak with your Customer Success Lead regarding a new configuration request.

Node Labeling - use the Table & Column Label Editor

As shown in the previous example, Additional Columns like ‘level1name’ from the dim_job_level table will provide the right data but isn’t a very helpful label to include in your list report. This is an easy fix for any Admin with the ‘CanEditDataWarehouseTableAndColumnLabels’ permission. 

Here is the default naming for dim_job_level in Explore

To change ‘level1name’, go to Admin > Table & Column Label Editor. 

Scroll or search for the corresponding dim table, and click Edit.

Enter your preferred label in the Column Label field (e.g. “Job Level”) and click Save.

Click Explore (or refresh an open window), and your change will immediately be reflected in the Additional Columns menu. It is important to note that this change will also immediately update any existing list reports using the dim_job_level column.  

Multiple Level Dimensions - use the Additional Column dim table

If the dimension you’ve chosen has multiple levels, and you want to display more than one level in the list report, rather than using the “Dimension”, you should find the corresponding “dim” table under the Additional Columns section, and select the relevant levels from there instead.

This is important for formatting and to avoid duplicate rows in your output. Every dimension has a corresponding dim table. 

For example, the standard dimension “Event Reason” may have 2 or 3 levels, and will have a corresponding “dim_event_reason” table with fields called level1name, level2name (and level3name if applicable). If you don’t see these exact field names, then your Admin has given them a more meaningful, custom label (via the Table and Column Label Editor as shown in the previous section) that will still reflect the levels you see in the dimension. Refer image below. 

In the below example, the query is trying to report Terminations this year with selections from the Dimension ‘Event Reason’ to display event reason level 1 and event reason level 2. 

Multiple-level Selection using Dimension “Event Reason” shows one column of results in the list report. 

Checking the values you will see the total records of level 1 nodes AND level 2 nodes; e.g. 594 Voluntary Terminations including 108 Redundancies and so on. If you add employee ID to your list report, your output will include 702 rows of data related to redundancy events (594 for level 1 and 108 for level 2) even though only 108 redundancy events occurred). 

Note: the query in this format using multiple level selections in the Dimension is exactly how you should set it up if you are planning to use the Expand/Collapse table view in your storyboard. Read more here. For the purpose of this example, we will proceed with a change to use the dim table. 

One column of results including totals may not be what you had in mind for your list report. So we will make a change now to multiple level selection using Additional Column dim table “dim_event_reason” 

The list report now shows two columns of results including only the total records of combined level 1 nodes and level 2 nodes; e.g. 108 Redundancies (that map to the level 1 Event Type = Voluntary Termination). If you add employee ID to your list report, your output will include 108 rows of redundancy events, 84 retirement events, and so on. 

The next article covers - Formatting a List Report