Public

The focus of this chapter will be the use of custom dimensions & measures to achieve more advanced reporting outcomes.

---

This content can also be viewed in the following video:

Nov 2021 - 1:28:11 min

• Using Custom dimensions to rename columns
• Using If statements to perform simple logic checks
• Using Case statements to perform simple value replacement
• Using Case Statements to perform multi-scenario assessment
• Using Custom Measures to filter existing measures

---

5.1 - Introduction

Now that you have the basics of building reports in the Enhanced Reporting platform down, it’s time to get into the fun stuff. Custom Dimensions are super powerful, and can help you make the most of your data.

The topics that will be covered will provide simple examples and guidance around how Custom Dimensions can help you achieve outcomes.

5.1.1 - Topics

At a high level, we’ll be covering

• Using Custom dimensions to rename columns
• Using If statements to perform simple logic checks
• Using Case statements to perform simple value replacement
• Using Case Statements to perform multi-scenario assessment
• Using Custom Measures to filter existing measures

5.1.2 - Column Names

This guide will be using standard names for dimensions. It may be that your system is configured with slightly different naming conventions.

For example:

• Job = What is sourced and applications are submitted to, also known as Requisition or ATR or Req
• Applicant = Person who applies for a job
• Application = The record created by an applicant applying to a job
• Candidate = An applicant whose application has progressed past initial submission for a job

5.2 - Renaming Columns

Renaming the columns as they appear in the visualisation is pretty straightforward, however, it only works when the report is being viewed in the system. This is because the visualisation is used when viewing the report, but the data section is used when extracting the report out of PageUp.

You can use custom fields to rename a column within the Data section.

5.2.1 - Scenario:

If your report is to be consumed by another system, and needs the column to be named a specific title, using a custom dimension will be a great option.

A job's Employment Type is captured on the job card, and displayed in reporting. It makes sense for your reports to have this column with the title of “Employment Type” except for this 1 specific report in your system which is consumed by a 3rd party system.
Instead, it needs to have the column title of ‘EMPTYPE’

5.2.2 - Solution Steps:

1. Edit your report
2. Create a custom dimension using “Custom Fields” in the Field picker list
   
   
   
   
3. In the expression box, set it to be the dimension that you want (e.g. ${job.work_type_description})
4. Give it the new name which matches what the 3rd party system requires (e.g. EMPTYPE)
   
   
   
   
5. Click Save

You will now have a column in your report which returns the same information as the “Employment Type” column, but with a different name.

5.3 - IF statements - simple logic checks

IF statements are a function which can be really useful for simple logical checks.

Think of it like ...

1. IF ‘this’
2. THEN ‘that’
3. ELSE ‘something else’

Logic statements can be really helpful in providing ‘Decisions’ based on the reportable values.
It can also be used to transform values from what is stored in the system to something that can be used in a different way.

5.3.1 - Scenario:

We’ve been asked to include a column in our report which says whether or not the Start Date listed against an offer is in the future or the past. 

An IF statement is an easy way to achieve this.

Field: Offer Start Date
If the start date is in the future, return ‘Future’
If the start date is not in the future, past ‘Past’

1. IF ‘start date is in future’
2. THEN ‘Future’
3. ELSE ‘Past’

5.3.2 - Preparation Steps:

If you already have a report on offer details, you can use it, if not, I suggest building a quick report to showcase how this can be used. 

As a guide, you can create a quick report with the following filters/dimensions

Create a new report using the Job via Position explore.

Filters:

• Offer Offer Accepted Date  is not null
• Offer Offer accepted (Yes / No) is Yes
• Offer Offer declined (Yes / No) is No

Dimensions

• Job Job No
• Job Job title
• Applicant Applicant ID
• Offer Offer Accepted Date
• Offer Start Date

Set the row limit on the ‘Data’ section to ‘10’ so that you only see the most recent 10 offers.

With this report, you’ll see 10 rows, with the details related to 10 separate offers.

Now we have some example data, we can build out a custom dimension with our IF statement logic check.

5.3.3 - Solution Steps:

Create a custom dimension using “Custom Fields” in the Field picker list

The expression will be

if(${offer.start_date} > now(), "Future", "Past")

The name will be 'Start relative to today'

The result will be a column showing the words 'Past' or 'Future'

5.3.3.1 - How does this work?

Syntax -

if(yesno_expression, value_if_yes, value_if_no)
If yesno_expression evaluates to yes, returns the value_if_yes value. Otherwise, returns the value_if_no value

now() = a function which returns the date/time that you’re running the report.

For our expression:

if(${offer.start_date} > now(), "Future", "Past")

The ‘YesNo expression’ is ‘is the start date greater than the date/time that it is now when  running the report’?
If yes, return the value ‘Future’
If not, return the value of ‘Past’.

After we’ve saved this expression, we will see that there is a new column in the report, and it’s showing “Future” or “Past” based on the date.

How cool is that?!?!?!

5.4 - Using Case statements to perform simple value replacement

What if we have multiple logic checks to perform on the same fields?

We could use IF statements, but there is a way to achieve this more easily and clearly.

CASE Statements are a logic check which can check for multiple scenarios, and return the first of those scenarios which is a match.

Syntax -

case(when(yesno_arg, value_if_yes), when(yesno_arg, value_if_yes), ..., else_value)
Returns value_if_yes for the first when case whose yesno_arg value is yes. Returns else_value if all when cases are no.

5.4.1 - Scenario:

In our earlier report on ‘Employment Type’ we rename a column (EMPTYPE) for our 3rd party system. Our stakeholders called out that there is a difference in the formatting of some of the values in the report.

Their system expected “Full-Time”
Our report output is “Full Time”

We can use CASE Statements to detect and transform values so that we can align this report to their preferred output.

Remember, we don’t want to change it for all reports, which would require us to update the names of the values in our system configuration, we just want to change it for this very specific use case.

Here is a mapping of the Values in PageUp vs the expected values from our 3rd party.

PageUp Configured	3rd Party Expected
Full Time	Full-Time
Part Time	Part-Time
Contractor	Contractor
Fixed Term	Fixed-Term
Casual	Casual

5.4.2 - Solution Steps:

Either Edit the ‘EMPTYPE’ custom dimension that we created earlier
OR
Create a new custom dimension which will be titled EMPTYPE Transformed

The following Expression will transform any examples of “Full Time”, “Part Time” & “Fixed Term” to the expected.

case(when(${job.work_type_description}="Full Time", "Full-Time"
)
, when(${job.work_type_description}="Part Time", "Part-Time"
)
, when(${job.work_type_description}="Fixed Term", "Fixed-Term"
)
,${job.work_type_description}
)

Our results look like this

EMPTYPE Transformed replaces each of our values with the expected values for the 3rd party system.

It returns Casual and Contractor & null values as they appear normally.

5.4.2.1 - How does this work?

case(when(yesno_arg, value_if_yes), when(yesno_arg, value_if_yes), ..., else_value)
Returns value_if_yes for the first when case whose yesno_arg value is yes. Returns else_value if all when cases are no.

As an example:

When the ‘${job.work_type_description}’ = "Full Time" the logic check returns a ‘Yes’. 

When it returns a ‘Yes’, return the specified value, in this case “Full-Time”.

We then repeat the ‘when()’ checks for as many scenarios as we need to accommodate the transformations required.

Next, we need to tell the expression what to do if none of the when() scenarios are met.

In this case, we’re getting the expression to return the value of the Employment type dimension without any transformation.

case(when(${job.work_type_description}="Full Time", "Full-Time"
    )
  , when(${job.work_type_description}="Part Time", "Part-Time"
    )
  , when(${job.work_type_description}="Fixed Term", "Fixed-Term"
    )
  ,${job.work_type_description}
  )

You’ll have noticed that there isn’t a transformation for “Casual” or “Contractor”. 

Why not?

Because those aren't mentioned in the when() statements, they won’t be detected, and the values will be returned by the else check as they normally appear.

5.5 - Using Case Statements to perform multi-scenario assessment

You’ve seen how to use IF Statements to perform a single yes/no logic check
You've seen how to use CASE Statements to perform multiple logic checks on the same field

In this section, you’re going to look at using CASE Statements to return a value based on information from Multiple Dimensions to provide a summary output.

5.5.1 - Scenario:

Our recruitment process requires that candidates complete 2 interviews. These are tracked by status change, e.g. Interview 1 complete, interview 2 complete.

When an application enters these steps, the recruitment step of Interview 1 and ‘Interview 2 are recorded against the application history.

We’re not interested in all applicants to a job, but only the candidates.

• A candidate is an applicant who’s submitted an application, and progressed through the screening process.
• The dimension Application Is candidate (yes/no) will be how we filter our report to accomplish this.

We could make a judgement call about which of the candidates we’re reporting on by reviewing a list of candidates, then checking whether or not they have a date in both the interview 1 and interview 2 recruitment steps.

You’ve been tasked with building a report which tells us quickly how many of the candidates remaining against the job have completed which number of interviews.

The candidates will fall into 4 ‘buckets’.

• First Interview completed only
• Second Interview completed only
• Both Interviews completed
• No interviews completed

5.5.2 - Preparation Steps:

Create a new report using the Job explore, as this is the one which will return all applications to a job.

This example will filter by a specific job as it has the relevant data for this scenario. You may apply a filter to a job relevant for your scenario.

Filters:

• Job Job no. = 512166
• Application Is Candidate? (Yes / No) is Yes

Dimensions

• • Job Job No
  • Job Job title
  • Application Is Candidate? (Yes / No) 
  • Application Steps > Date of interview 1 Interview1 Date

• Application Steps > Date of interview 2 Interview2 Date

Measures

• Application Total applications submitted

The results will look like this.

• There are 10 candidates who have completed no interviews
• There are a number of candidates who have a date for Interview1
• There are 3 candidates who have a date for Interview2
• There are 2 candidates who have a date for both Interview1 & Interview2

5.5.3 - Solution Steps:

Now it’s time for our dimension which will do the logic checks for us.

Create a custom dimension using “Custom Fields” in the Field picker list

The following expression will use the information in the Date of interview 1 Interview1 Date & Date of interview 2 Interview2 Date columns to determine which of the ‘buckets’ our candidates fit into.

The Name will be 'Interview Completion'

case(when(
    NOT is_null(${application_recruitment_steps.interview1_date}) 
    AND NOT is_null(${application_recruitment_steps.interview2_date})
    , "Both interviews complete")
  , when(
    NOT is_null(${application_recruitment_steps.interview2_date})
    , "Second interview only")
  , when(
    NOT is_null(${application_recruitment_steps.interview1_date})
    , "First interview only")
  , "No Interviews"
  )

Here is the result of our new Custom Dimension

5.5.3.1 - How does this work?

In addition to our CASE Statements, the below logic functions help us define our ‘Yes/No’ logic checks.

• isnull(value) = Returns Yes if value is null, and No otherwise.
• NOT [ NOT value ] = Returns Yes if value is No, and No otherwise.
• AND [value_1 AND value_2] = Returns Yes if both value_1 and value_2 are Yes, and No otherwise.

To break down the logic used to help us determine our when() statements.

is_null(${application_recruitment_steps.interview1_date})

Checks if the Interview1_date is null
If it’s null it will return ‘Yes’

Because CASE Statements rely on the ‘Yes’ being returned, this won’t work by itself, we want to know if the date has a value in it.
This is where the use of NOT comes in

NOT is_null(${application_recruitment_steps.interview1_date})

Will return the ‘Yes’ that we need for the case statements when the Interview1 date has a value

NOT is_null(${application_recruitment_steps.interview2_date})

Reusing this logic, we can then determine if there is a value in the Interview2 date as well.

Now that we know individually whether or not these dimensions have a value, we need a way to combine them so that we can identify those applications that have a value in both dimensions.

NOT is_null(${application_recruitment_steps.interview1_date}) 
    AND NOT is_null(${application_recruitment_steps.interview2_date})

AND is used to tell us those applications which have a value in Interview1 date AND Interview2 date.

when(
    NOT is_null(${application_recruitment_steps.interview1_date}) 
    AND NOT is_null(${application_recruitment_steps.interview2_date})
    , "Both interviews complete")

Putting it all together, means that this when() statement is checking

Is there a date in Interview 1?
AND
Is there a date in Interview 2?
If both of these separate checks return Yes
Return ‘Both interviews complete’

Now that we’ve checked the main bucket that we’re interested in, we have another 2 when() statements to identify other ‘buckets’ of interest, so we then check the other scenarios.

5.5.3.2 - Case Statement Order - Is very important

CASE Statements perform the logic checks in the order that you write them.

If we checked for ‘First interview only’ criteria first instead of last, any applications with a date in Interview1 Date will be a ‘Yes’ and therefore the criteria is met, and it does not check against later criteria.

To illustrate how the order can change the results, here is a tweaked version of the above working expression with one which doesn’t work so well.

case(when(
    NOT is_null(${application_recruitment_steps.interview1_date})
    , "First interview only")
  , when(
    NOT is_null(${application_recruitment_steps.interview1_date}) 
    AND NOT is_null(${application_recruitment_steps.interview2_date})
    , "Both interviews complete")
  , when(
    NOT is_null(${application_recruitment_steps.interview2_date})
    , "Second interview only")
  , "No Interviews"
  )

Here is an example of how the results would be returned if the ‘Both interviews complete’ check was not done first.

Note that instead of it returning ‘Both interviews complete’ as we desire, it’s returning ‘First interview only’ as that was the first detected match.

As this doesn’t achieve what is wanted, time to remove that dimension to save confusion later on.

5.5.4 - Final Output:

Now that there is custom dimension making running the logic for us, we can remove these columns from our report

• Application Steps > Date of interview 1 Interview1 Date

• Application Steps > Date of interview 2 Interview2 Date

With these removed, we now get a cleaner view of the number of candidates which fall into our ‘buckets’.

Looks like there are a lot of interviews which require completion!

What if we’re running this report across multiple jobs? Would multiple rows per job work in that report?

Probably not. 

Pivoting the Interview completion custom dimension will help move it all on to a single line per job.

1. Click the cog against the custom dimension
2. Choose the ‘Pivot’ option
   

Now, the number of applications submitted that fall into our buckets are neatly laid out along a single line per job.

With this outcome achieved, it’s time to move on to the next and possibly most exciting topic!

5.6 - Using Custom Measures to filter existing measures

So far, the focus has been on custom dimensions. While great in Line by Line reporting, if you’re looking for high level numbers, custom measures come into their own. Especially at a higher level like a business unit, and numbers related to the jobs/applications for that business unit.

5.6.1 - Scenario:

Stakeholders have requested the number of applications submitted for all jobs within a business unit for this calendar year.

In addition, they would like to see the gender breakdown of those applications, Male, Female, Unknown & the % of Female applications to that business unit.

It will include

• Business Unit title
• Total applications submitted
• Total Male applications submitted
• Total Female applications submitted
• Total Unknown application submitted
• % of Total Applications where the applicants gender is Female applications submitted 

5.6.2 - Preparation Steps:

Create a new report using the Job explore, as this is the one which will return all applications to a job.

This example will filter by a specific job as it has the relevant data for this scenario. You may apply a filter to a job relevant for your scenario.

Filters:

• Job Opening Date is on or after 2021/01/01

Dimensions

• Job > Organisational Hierarchy: Business unit

Measures

• Job Total Distinct Jobs

• Application Total applications submitted

We now have a list of the Business Units for which a job has been sourced, the count of those jobs and the number of applications which came through for those jobs in those business units.

5.6.3 - Solution Steps:

Next, we want to know the number of total applications submitted where the applicant's gender is either Male, Female or Unknown.

We will filter the results in our measures to achieve this quickly.

1. Find Application Total Applications Submitted in the field picker list 
2. Click ⋮
3. Choose Filter Measure
   
   
4. Update the Name of the custom measure
5. Set the filter to be Applicant Gender
   
   
   
   
6. It will default to ‘Is any value’
   
   
   
7. Click this and change to is Male
   
   
   
   
8. You’ll end up with this
   
   
   
   
9. Click Save

Once saved, the new custom measure ‘Male applications’ will appear in the data section of your report. Click Run to view the output.

The Male applications column in the above screenshot is showing the number of applications submitted to jobs in this business unit where the applicant who submitted the application has identified as Male.

1. Now, against our new custom measure in the Field picker list, click the ⋮ and choose Duplicate
   
   
2. Next click ⋮ and edit the new ‘copy’ custom measure
   
3. Update the name and the filter to be ‘Female’
4. Repeat for the Unknown gender, this time we’ll filter by is null
   

We now have a report which looks like this when run.

The final requirement is to include a % breakdown of Female applications in relation to the total applications submitted.

A table calculation is the easiest way to achieve this.

1. In the field picker list click, 
2. Custom Fields section click + Add 
3. Choose Table Calculation
   
   
   
   
4. Click into the Expression box, and choose the Female applications field
   
   
   
   
5. Then type "/"    (symbol for 'Divide by)
6. Then choose the Total Applications submitted option
7. Your expression will then look like this

${female_applications}/${application_view.total_applications_submitted}

• Set the format to ‘Percent’
• Enter a name e.g. % of applications which are female
• Click Save

The output that you’ll receive will be similar to the below.

The above chart is now providing the insight that of the total applications submitted for jobs within the Retail business unit (4) 3 of the applications were submitted by Female applicants, which is 75% of all applications for that business unit. 

5.7 - Conclusion

Together, we’ve worked through how to use Custom Dimensions & Measures, as well as Table Calculations to provide insights into our data within Enhanced Reporting.

• Using Custom dimensions to rename columns
• Using If statements to perform simple logic checks
• Using Case statements to perform simple value replacement
• Using Case Statements to perform multi-scenario assessment
• Using Custom Measures to filter existing measures.

The scenarios that we’ve covered can be used as a guide for building your own Custom Dimensions & Measures to achieve outcomes for any number of scenario’s that your various stakeholders will undoubtedly call on you to address.

---