Skip to Content
Technical Articles
Author's profile photo Poonam Kumari

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 !

Assigned tags

      11 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo baladeba behera
      baladeba behera

      Very Nice Blog Poonam, Thanks for sharing.

      Author's profile photo Poonam Kumari
      Poonam Kumari
      Blog Post Author

      Thank you for the appreciation !

      Author's profile photo Pallavi Kumari
      Pallavi Kumari

      Appreciate your effort Poonam, superb Blog, Thank you for sharing.

      Author's profile photo Poonam Kumari
      Poonam Kumari
      Blog Post Author

      Thank you for the appreciation, means a lot !

      Author's profile photo Antonio Ferreira Vicente
      Antonio Ferreira Vicente

      That's awesome these tips!!

      Author's profile photo Poonam Kumari
      Poonam Kumari
      Blog Post Author

      Thank you for the appreciation !

      Author's profile photo Baskaran Krishnamoorthy
      Baskaran Krishnamoorthy

      Hi Poonam,

       

      This article  is awesome.  Just tried it out for a customer requirement and the report  came through  smooth and swift .   Your contribution greatly appreciated.

       

       

       

      Author's profile photo Poonam Kumari
      Poonam Kumari
      Blog Post Author

      Thank you Baskaran for the feedback means a lot !

      Author's profile photo Leela S
      Leela S

      Hi Poonam,

      Very helpful blog on Employee Leave balances.  A Star light for us, showing the correct navigation to achieve. Thanks/Leela Tharun

       

      Author's profile photo vijaya patil
      vijaya patil

      Hi Poonam,

      Thanks for the Blog.

      As i have a requirement to build a report i have followed all the steps but in my calculated column it is coming as " 0" in report.

      Kindly help me understand how to build the measure.

       

       

      Regards,

      Vijaya-8639777341

      Author's profile photo Ofentse Koolopile
      Ofentse Koolopile

      Hi Poonam,

       

      Thanks, the report comes up exactly this way however the data is  wrong. It seems the calculations are only from the date we migrated to SFF( 6 months ago).

      Do I need to change the date options ? so that calculations start from the day an employee started ??