Skip to Content
Technical Articles

EC Time Off Leave Report (via Canvas Report)

INTRODUCTION

In this blog post, we will learn how to build Leave Report to display current year leave entitlement(accrual), carry forward, leave availed till date, future dated leave, leave balance till date and future leave balance in one report using Report Canvas.

Current Year Leave Entitlement Carry forward from previous year Leave taken in current year (to date) Future dated leave Leave balance(to date) Leave balance(Projected)

This report will help you to configure all types of accrual leaves details in one report. Eases clients hassle to merge multiple report manually after downloading it.

PREREQUISITE

  • EC and EC Time off enabled.
  • Access to Report Canvas under Reporting.

CONFIGURATION

1. Object structure:

  • Select Time Management as your Table Element.
  • Select “Time Account” as the Driving Table.
  • Expand Time Account to add Related Table such as Time Account Details, Time Account Type.
  • You can select these table to pull other Org info Global Job Info, Personal Info and Legal Entity such as employee id which is required for any report, country mandatory requirement for most of the reports.

Pic%201%3A%20Object%20Table

pic1: Object Table

2. Static filter:

  1. In the query filter add all Booking Types as these booking types will be considered to formulate the calculated columns.
  2. Add all time types for Annual Leave.

pic3%20-%20static%20filter

pic2 – static filter

Note:- In my example, I have considered only Annual Leave. As per your requirement you can add other time types as well.

3. Formula for each calculated column:

Current Year Leave Entitlement Carry forward from previous year Leave taken in current year (to date) Future dated leave Leave balance(to date) Leave balance(Projected)

Note:- Add calculated column for each columns mentioned. Label can be modified as your requirement.

  • Formula for calculated column ” Current Year Leave Entitlement”

IF((bookingType == Accrual)) THEN

value = bookingAmount

ELSE

value = 0

 

  • Formula for calculated column “Carry forward from previous year”

IF ((bookingType != Null) AND (Posting Date <= Account End Date)) THEN

Value = bookingAmount

ELSE

value = 0

 

  • Formula for calculated column “Leave Taken in Current Year(to date)”

IF((bookingType == EMPLOYEE_TIME) AND (Posting Date <= bookingEndDate) AND (Posting Date >= Account Start Date) AND (Posting Date <= [%TODAY%])) THEN

value = bookingAmount

ELSE

value = 0

 

  • Formula for calculated column “Future dated leave”

IF((bookingType == EMPLOYEE_TIME) AND (Posting Date > [%TODAY%])) THEN

value = bookingAmount

ELSE

value = 0

 

  • Formula for calculated column “Leave Balance(to date)”

IF((bookingType !=NULL) AND (Posting Date <= [%TODAY%])) THEN

value = bookingAmount

ELSE

value = 0

  • Formula for calculated column “Leave Balance(Projected)”

IF((bookingType !=NULL) THEN

value = bookingAmount

ELSE

value = 0

 

3. Date Option:

Driving Table Date Type
Driving Table Options Time Account Show All
Related Table Options Current Date

 

4. Runtime Filter:

Add runtime filter as per the requirement such as employee id, employee status etc.

 

5. Pivot:

Measure:

Current Year Leave Entitlement Carry forward from previous year Leave taken in current year (to date) Future dated leave Leave balance(to date) Leave balance(Projected)
Pivot Function Sum Sum Sum Sum Sum Sum

Row:

Remaining selected fields from object table will be part of Rows.

Note:- at least one row item should be defined. 

Always put employee id, time type and Account Start Date and Account End Date as row entities.

 

SAMPLE REPORT

Below is a sample report for your reference. The reports shows all the details for year wise Account Start and Account end date for every employee including both Active and Inactive employees.

pic3%3A%20Sample%20Report

pic3: Sample Report

REFERENCES

Navigate to below links for more details on how to create Pivot for Canvas Reports.

  • https://launchpad.support.sap.com/#/notes/2557157
  • https://launchpad.support.sap.com/#/notes/2493771

Drop in your queries in comment section for any technical assistance with the report.

Hope this Article is helpful to configure Time Off related Canvas Reports !

10 Comments
You must be Logged on to comment or reply to a post.