Calculating Age - Part 4

During the last several weeks we've looked at how to calculate someone's age for benefits and rate tables using Expression Builder.  What if you need to calculate an employee's age in a SAP Crystal Report?  To start with, Crystal Reports does not recognize the HRMS functions of AGE(), YEARSOLD() or AGESPOUSE().  This time we will need to use the function CurrentDate.  

Uses of CurrentDate

  • CurrentDate by itself will return the value of today's date using the system date of your computer
  • 4 days ago.  If you want to see the result as of four days ago use CurrentDate - 4
  • 1 month ahead or behind todays date.  To add or subtract a month use the function dateadd.  m will represent month.  dateadd("m",1,currentdate) will add one month.  dateadd("m",-1, currentdate) will subtract one month from today. For example, if today is 5/14/2022 and you add one month the date returned would be 6/14/2022.
  • To use the month, day, or year from the Currentdate.  Let's use the same example as above.  Assume today is 5/14/2022.  To pull the month from the CurrentDate the formula is Month(CurrentDate). The value returned is 5. To pull the year use Year(CurrentDate).  You will need to do some formatting on the field to remove the comma and decimal places as the value returned is 2,022.00.  By now you can probably guess how to create the formula to return the day.  Day(CurrentDate) will return the value of 14.

Let's get back to the original objective.  To calculate an employee's age as of today in a SAP Crystal Report.  As I did in a previous post, I went to the Sage Knowledgebase and searched how to calculate age in SAP Crystal Reports and found a couple of formulas I can use.  These formulas are very complex and I would definitely start with the Sage Knowledgebase article which you can access on Sage City.

To calculate an employee's age as of today:

if Month({HRPERSNL.P_BIRTH}) < Month(CurrentDate) or
(Month({HRPERSNL.P_BIRTH}) = Month(CurrentDate) and
Day({HRPERSNL.P_BIRTH}) <= Day(CurrentDate))
then Year(CurrentDate) - Year({HRPERSNL.P_BIRTH})
else (Year(CurrentDate) - Year({HRPERSNL.P_BIRTH}))-1

To calculate an employee's age as of the end of the year (December 31, 2022):

if Month({HRPERSNL.P_BIRTH}) < Month(Date("12/31/2022")) or
(Month({HRPERSNL.P_BIRTH}) = Month(Date("12/31/2022")) and
Day({HRPERSNL.P_BIRTH}) <= Day(Date("12/31/2022")))
then Year(Date("12/31/2022")) - Year({HRPERSNL.P_BIRTH})
else (Year(Date("12/31/2022")) - Year({HRPERSNL.P_BIRTH}))-1

Note: You can replace the Date("12/31/2022") with the date required for your SAP Crystal Report if you want it to be different than December 31, 2022.  Remember to always test your formulas and make sure the data returned is correct.

If you would like to watch videos on how to use SAP Crystal Reports or how to build a formula, I recommend the learning package 'HRMS - Sage HRMS - SAP Crystal Reports for Sage HRMS'.  This package is free if you have active Gold or Premium Support.  If not, you can purchase the package for $199 on sageu.com/hrms and search for the package name.

If you are interested in instructor-led training on how to create formulas in Crystal reports,  I would recommend Level 2 or 3 of the courses listed below.  Please note you will need to complete Level 1 prior to attending level 2, and before attending level 3 you will need to complete level 2 training. If you have active Gold or Premium Support, HRMS instructor-led training is 50% off.

HRMS - Sage HRMS - SAP Crystal Reports - Level I: Laying the Foundation

Duration 7 hours, 30 minutes

Details

SAP® Crystal Reports for Sage HRMS connects you to data above and beyond the standard reports included with Sage HRMS by allowing you to create powerful, formatted reports with Sage HRMS as your centralized database and single point of truth. During this virtual, instructor-led course we will train you to use SAP® Crystal Reports to extract the data you need from Sage HRMS and turn it into interactive, actionable information. Topics will include understanding the file structure of Sage HRMS, as well as using formulas and expressions. We will discuss creating and editing reports and using the Report Wizard. This training includes hands-on exercises so you can practice what you have learned in a demo environment.

HRMS - Sage HRMS - SAP Crystal Reports - Level II: Building your Skills

Duration 7 hours, 30 minutes

Details

Further develop your report-writing skills with this advanced training on SAP® Crystal Reports for Sage HRMS. Building on your understanding of the basics of SAP® Crystal Reports. During this virtual, instructor-led course we will discuss the linking tables interface, the application of formula special fields, the formula editor, and using formulas correctly. You will learn to gain more control over your data with advanced techniques for extracting and depicting information using the tools you already have in SAP® Crystal Reports to turn your Sage HRMS data into interactive, actionable information.

This training includes hands-on exercises so you can practice what you have learned in a demo environment.

HRMS - Sage HRMS - SAP Crystal Reports - Level III: Honing your Craft

Duration 7 hours, 30 minutes

Details

During this virtual, instructor-led course we will deep dive into SAP® Crystal Reports for Sage HRMS with advanced topics related to designing flexible, customized reports. Beyond the basics of report-writing, you will learn to create SAP® Crystal Reports with sub-reports, parameter fields, complex formulas, and running totals.  Become the SAP® Crystal Reports expert for your business.  This training includes hands-on exercises so you can practice what you have learned in a demo environment.

Stayed tuned...next week we'll take a look at how to create a formula in a SAP Crystal Report.