Using Date Types to Achieve Desired Results

Objective

After completing this lesson, you will be able to use date types to achieve desired results in SAP SuccessFactors Canvas Reports.

Default Behavior

Current Date or Show All

The first table pulled into a new query in Advanced Reporting will become the "Driving" (or "controlling") table and default to the Current Date date option (for effective dated tables) or the Show All date option (for table with no effective dates).

Show All is selected as the Data Type.

This example shows Job Information data for employee mhoff1. By default, the query will return the most current record - as identified by "today" being between the Effective Start and Effective End Dates:

Job information is displayed for the employee mhoff1.

This example shows Compensation data for the employee mhoff1. Note that here, the employee can have multiple Pay Components current as "today", hence the multiple records:

Multiple records are shown for the employee mhoff1.

Show All Records

To display all records for this employee, simply change the date options to Show All. Note that this date option is the default for tables that are not effective dated.

This example shows all compensation data for employee mhoff1. These records will include all compensation pay components since the employee was hired.

Compensation data for the employee mhoff1 is shown.

As Of Date

To display records that were current on a specific date, choose As Of Date. Setting an As Of Date to December 09, 2016 will return records where December 09, 2016 is between the Effective Start and Effective End Dates.

As Of Date is selected as the Date Type.

Date Range (On Start Date)

To display records that started in a certain date range, select Date Range (On Start Date).

Setting a Date Range (On Start Date) from December 09, 2016 to December 09, 2016 will display records where the Effective Start Date is within this date range.

Current Date is selected as the Date Type.

Date Range (Validity)

To display records that were active in a certain date range, select Date Range (Validity).

Setting a Date Range (Validity) from April 12, 2016 to April 12, 2016 will display records are active within this date range.

Data Range (Validity) is selected as the Date Type.

Multi-Table Queries

When a query has more than one table, the first table pulled into the query will become the driving (or controlling) table, which is identified by the green outline in Object View. Subsequent tables will (by default) show records for the current date.

Multi-table query showing General Job Information and Compensation.

Effects on Results - Current Date

Use the default Current Date option to see the records that are applicable today.

The example shows a query on the Job Information table and the Compensation table, both showing the current date. When these tables are used in the same query, it will match the current date Job record with the current date Compensation records.

The current date Job record is matched to the current date Compensation record.

Effects on Results - Current Date + Show All

Use Current Date on the driving table to retrieve the current / active records from the  driving table and Show All on the related tables to only see historical values.

This example shows a query on the Job information table showing mhoff1 Job Title as of today and all records from the Compensation Information table to view his compensation history:

In the Driving Table Options, Current Date is selected as the Date Type. In Related Table Options, Show All is selected as the Date Type.

The Driving Table Start Date option is only available on related tables. This option will retrieve the record that is applicable at the Start Date of the driving table.

 

Here we have a query on the Job Information table showing Job records January 1st 2011, which will show active employees as of that date. The compensation information returned is the active compensation as of the same date.

In the Driving Table Options, As Of Date is selected as the Date Type. In Related Table Options, Driving Table Start Date is selected as the Date Type.

Exercise: Create a Query with Advanced Reporting: Part A

Business Example

In this exercise, you will create a list of employees and their assigned HR Manager. You will use joins and table restrictions to accomplish the task. You will also include the calculated annual compensation and pay grade values from two different time periods. In part A, you will learn how to navigate and join tables.

ExerciseStart Exercise

Exercise: Create a Query with Advanced Reporting: Part B

Business Example

In this exercise, you will create a list of employees and their assigned HR Manager. You will use joins and table restrictions to accomplish the task. You will also include the calculated annual compensation and pay grade values from two different time periods.

In Part B, you will learn to use table restrictions.

ExerciseStart Exercise

Exercise: Create a Query with Advanced Reporting: Part C

Business Example

In this exercise, you will create a list of employees and their assigned HR Manager. You will use joins and table restrictions to accomplish the task. You will also include the calculated annual compensation and pay grade values from two different time periods.

In part C, you will limit results to direct reports of the logged in user.

ExerciseStart Exercise

Exercise: Create a Query with Advanced Reporting: Part D

Business Example

In this exercise, you will create a list of employees and their assigned HR Manager. You will use joins and table restrictions to accomplish the task. You will also include the calculated annual compensation and pay grade values from two different time periods.

In part D, you will display the current annual compensation by creating an aggregated calculated column on base salary and bonuses.

ExerciseStart Exercise

Exercise: Create a Query with Advanced Reporting: Part E

Business Example

In this exercise, you will create a list of employees and their assigned HR Manager. You will use joins and table restrictions to accomplish the task. You will also include the calculated annual compensation and pay grade values from two different time periods.

In part E, you will learn to add duplicate tables with different date options.

ExerciseStart Exercise

Summary

Learning

SAP FacebookSAP YoutubeSAP LinkedIn