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.
pic1: Object Table
2. Static filter:
- In the query filter add all Booking Types as these booking types will be considered to formulate the calculated columns.
- Add all time types for Annual Leave.
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: 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 !
Very Nice Blog Poonam, Thanks for sharing.
Thank you for the appreciation !
Appreciate your effort Poonam, superb Blog, Thank you for sharing.
Thank you for the appreciation, means a lot !
That's awesome these tips!!
Thank you for the appreciation !
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.
Thank you Baskaran for the feedback means a lot !
Hi Poonam,
Very helpful blog on Employee Leave balances. A Star light for us, showing the correct navigation to achieve. Thanks/Leela Tharun
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
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 ??
Can the canvas report include "comment" field input that is submitted during submission
Hey Poonam
Thanks for sharing the details. Quick question - what is the Posting Date field in the Time Account Detail object? I can't seem to have that in my system. Also looking at it from your screenshot, is that a calculated field etc?
Please advise. Thanks in advance.
Jacinth