Creating a Detailed List Report

List reports can be as simple as you need or they can be detailed, consisting of many different parts. If you’re new to list reports, we recommend starting with Creating a Simple List Report before diving into these more detailed variations. 

This article is one 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 (this article)
4. Using Dimensions in List Reports
5. Formatting a List Report

Quick reminder of the important points to remember when creating your list reports;

• You will either build a query from scratch or edit an existing query to create the list report. 
• Your metric selection will determine what other dimensions, columns and information to include or exclude in the list report. 
• Queries should specify a time period.
• This arrow  > symbol indicates that there is more information nested beneath the heading in a dropdown menu. This is how you will select your levels. 

Create a Detailed List Report

When creating a detailed list report, the choices and options available to you are dependent on the entity relationships in your data. If you’re unfamiliar with your data relationships, read the Relationship Between Data Entities in One Model. 

Let’s dive into creating a detailed list report.

Step 1. Navigate to the Explore tab

• Start at the Explore tab.

• Then select List from the Visualization Type dropdown menu.

Step 2. Choose your Metrics

• Select a Metric that is relevant to your report. 

Remember that your metric selection will determine the other dimensions, columns and information available in the list report. Different metrics have different options. 

For example; If you want an employee list report, choosing a Headcount metric will provide information about employees whereas if you want a list report related to recruitment, then you should select a Recruitment metric like Applications or Hires. 

Now, you can add multiple metrics to your query, but when you have finished building it, you should check that the output is giving you the desired format across all of your selections. Especially if you have selected data from multiple data sources.

Step 3. Choose a Time Period

• Choose a Time Period for the data you want to pull into the list report. 

A common example is an end of period Headcount report for “This Year”, or a list of all open requisitions for “This Month”. 

Remember that a list report is often going to produce thousands or potentially tens of thousands of records, so take care before choosing multiple time periods. 

Step 4. Select the Dimensions 

• Choose a Dimension to slice or filter the metric data by, if needed. 

In this example, rather than looking at the whole employee population at the end of this year, the query has filtered to employees in the Customer Service Org Unit, and the query has selections to display only employees aged 50 to <60 and whether they are in a Managerial or Non-Managerial role. Scroll to the end of this article about Basic Charts and Tables for more info about filters vs selections.

Step 5. Run Query

• Click Run query to see the aggregate headcount results.

If you choose multiple dimensions, you can arrange the dimensions by dragging and dropping their order in the Dimensions section in Explore. Their left-to-right order in the query panel will be reflected in the table and your overall query selections will appear in the following order (that cannot be changed): Dimension/s, Pivoted Dimension/s, Metric/s, Additional Column/s. 

*Help! My dimension selection is not returning any results in my list report!

As noted previously, the chosen metric will determine the dimensions that the query can work with. A Headcount metric will link to many employee-related dimensions, but if you add the Event Reason dimension, it would not return any output. 

Why? 

Because it isn’t joined in the data model since an employee can have multiple ‘events’ in a reporting period and it would multiply the headcount results. But if you’ve selected a Terminations metric (for example) then you’d still have access to employee-related dimensions as well as the Events-related dimensions. It’s all about the entity relationships (or FROM and TO joins) which are explained in this article about entity relationships. 

Step 6. Additional Columns > Fact Tables and Columns

It is very common to add additional ‘raw’ columns of data to the list report to see the record level data (vs aggregate result). Some examples include person ID, full name, requisition ID, action reason, and many more. In Explore, scroll down to the Additional Columns section and find the relevant fact table (e.g. employee-related details are usually located in a table titled ‘employee’). Or if you know the column name, you can use the search bar. 

• Expand any fact table (e.g. “application” or “employee”) to see all columns available to be added to the list report. Note, you will also see tables in the Additional Columns section labeled as “dim”. These are covered in the next section.

• Click on the columns that you want in the list report; these will appear in the Additional Columns section of the query builder. 

“one.employee.person_ID” and “one.employee Name” have been added to the query, and Run. Your table output now takes the form of a list. With “Headcount (EOP)” as the metric, we would expect one record per employee based on their unique person ID. You could validate this by checking there are 259 output rows to equal the aggregate headcount total shown in the query output before the columns were added. 

Want more details in your report? Simply add more columns.

You can arrange the order of Additional Columns in the list report by dragging and dropping in the query selector. In the output, the additional columns will always appear at the end of the report as per the fixed order of display: Dimension/s, Pivoted Dimension/s, Metric/s, Additional Column/s, as shown in the image below.

Step 7. Additional Columns > dim Tables

When building your list report, your Additional Column selections will be determined by the chosen metric/s, but also how you want the data to look for your users. In a lot of cases, the Fact Table columns are the raw data as it comes into One Model from your source system - and this isn’t always pretty or helpful! 

Looking at the Flex Arrangement data from the previous example, the query used the column called “one.dim_flex_arrangement Flex Arrangement”. This is a column from a dim table (aka dimension table). This means that the raw flex arrangement data from your source data has been transformed into a single or multi level dimension to allow for additional analysis and filtering, perhaps some wording changes or specific groupings of values to assist in reporting, and potentially join/s to other fact tables. 

Here’s what the fact table column version of the flex arrangement data looks like “one.employee.flex_arrangement” - zeros and ones. 

As a user, I couldn’t easily interpret that data, and I also may want to be able to filter my data for employees who do have a flex arrangement. So the dim table is created by your data engineer, converting the 0 to equal “No” and the 1 to equal “Yes”. If you wanted to filter THIS query by flex arrangement, then you would select the Dimension called “Flex Arrangement”; like we have done for Age, Org Unit and Managerial in the example. Using the dim column, puts the data at the end of the list report against each employee headcount record but doesn’t allow for specific selections or filters - that’s what the Dimension is for.

Note: Every dim table will have a fact table column - one.dim_flex_arrangement comes from the data in one.employee.flex_arrangement - but not every fact table column will have a dim table. Because there are some columns that don’t need that extra level of analysis or manipulation, for example, email address is fine in its raw format. Please refer to this article for further information about using dim tables and Dimensions. 

Back to your Query

• Back to our Headcount query, rearrange the additional columns if desired in the order you want them to appear in the list report by dragging and dropping within the Additional Columns section. 
• Click the Run Query button. 

Note: Since list reports often contain a large amount of data, the “Query too large” message may appear and you will need to export the report to see all of the results. 

Step 8. Pin your Query

Once the query has completed, you will have options to pin the table results to a new or existing storyboard or save to the Insight Library.

• To pin to a new Storyboard, select from the dropdown menu and complete the input boxes as shown below:

• To pin to an existing Storyboard, select from the dropdown menu and choose your location as shown below: 

• To save your list report to your Insight Library, select Save to Insight Library from the dropdown menu. Choose your category and name your insight as shown below:

Note:  it is possible to export the list report directly from Explore, but doing this prior to saving the report in a Storyboard could result in columns in the exported report being in a different order than expected.

Once you have created your detailed list report and pinned it to a Storyboard, you will have a range of options for formatting and exporting. 

Go to the next article: Using Dimensions in List Reports