Reporting: Advanced Formatting for Report Writer

In this article you will find some examples of alternate formatting to pull information into Report Writers that aren't in the available Fields listing. These functions are supported in ReadyPay Desktop and in the Basic Report Writer found in RPO.

Current Date & Length of Service

Length of Service (LOS) is a calculated field and is not stored. The formula provided in this article explains how to take the current date and subtract the hire date to get the number of days the employee has been employed.

note

The LOS figure returned on the report may not be accurate for all employees if they have an adjusted seniority date or rehire date. When calculating LOS, the system considers if the Adjusted Seniority or Rehire Date fields are populated and will first look to those, then the Hire Date for LOS.

Current Date

Enter Now() to return the current date within the system.

Length of Service

Enter Now()-ee.hiredate to report the amount of days between the hire date and the current date.

For LOS in years, rather than days, simply divide by 365: (Now()-ee.hiredate)/365.

For an employee's next anniversary date, add 365: ee.hiredate + 365.

The example above does some basic math to return the Length of Service. Another way to do this includes using the VB scripting DateDiff function, which has the option of returning a variety of different intervals.

The syntax for the DateDiff function is as follows: DateDiff(interval, date1, date2).

Available intervals that can be returned and the parameter required for them are:

• yyyy — Year
• q — Quarter
• m — Month
• d — Day
• w — Weeks
• h — Hour
• n — Minute
• s — Second

When using the DateDiff function, you will need to use quotes around the interval. If you use a fieldname it does not need quotes around it.

Example: get the number of months the employee has been employed:

DateDiff("m", ee.Hiredate, Now())

Like the first set of calculations this ignores the rehire dates and adjusted length of service dates.

The above methods of calculating LOS will round the LOS to the nearest integer (if you do not have any decimal places configured on the field in Report Writer, in which case you will get a fraction). In order to prevent rounding and see the closest, lowest integer — for instance, if someone has worked 18 months, the following will return 1 year because they have only worked one full year — use the following:

INT((Now()-ee.hiredate)/365)
INT((DateDiff("d", ee.hireDate, NOW()) / 365))

Example: use DateDiff to return the Age of an employee in years:

DateDiff("d", ee.birthdate, Now())/365

This will display the "true" age of the employee as of today in years.

Accessing the employee annual salary

Enter the following expression in a field box to calculate the annual salary for an employee:

ee.AnnualSalary(co.safecheckdate)

Reformat Date Fields

Using an employee hire date of 01/03/2002:

Format as: Jan 3, 2002

Monthname(Month(ee.hiredate), True) + " " + Cstr(Day(ee.hiredate)) + ", " + Cstr(Year(ee.hiredate))

Format as: January 3, 2002

Monthname(Month(ee.hiredate), False) + " " + Cstr(Day(ee.hiredate)) + ", " + Cstr(Year(ee.hiredate))

Format as: 01/03/02

Right("00" + CStr(Month(ee.hiredate)),2) + "/" + Right("00" + CStr(Day(ee.hireDate)),2) + "/" + Right(Cstr(Year(ee.hireDate)),2)

Format as: 132002

CStr(Month(ee.hireDate)) + CStr(Day(ee.hireDate)) + CStr(Year(ee.hireDate))

Format as: 01032002

Right("00" + CStr(Month(ee.hireDate)),2) + Right("00" + CStr(Day(ee.hireDate)),2) + CStr(Year(ee.hireDate))

Use of "If Statements"

In Report Writer there is no way to write an If statement or use an If function like in Excel. If you need to use an If like function, you can use a Replace statement, which is a VB function. The basic syntax is: Replace(Field, "LastName", "XXX").

Example:

Replace(ee.Lastname, "Smith", "XXX")

When the report runs the above statement will replace any employees having a last name of Smith with XXX.

You may make multiple replacements at the same time by using nested Replace statements. The basic syntax is as follows: Replace({RS}, search text, replace text) where {RS} is a replace statement.

Example:

Replace(Replace(ee.lastName, "Smith", "YYY"), "Jones", "XXX")

When the report runs the above statement will replace Smith with YYY and Jones with XXX.

Replace(Replace(Replace(Replace(ee.termreason, "Term1", "Absenteeism/Tardiness"), "Term2", "Criminal Activity"), "Term3", "Drug Use"), "Term9", "Resignation")

You may also replace characters using Replace statements.

Example:

Replace(ee.ssn, "-", " ")

When the report runs the above statement will remove dashes from the social security number.

Dates of the Most Recently Processed Payroll

If you are running a report and need to use the dates of the most recently processed payroll, you may use the following syntax rather than using the reporting dates. You can set the report to run for the last payroll processed and not have to worry about the date ranges being passed in.

• co.mostrecentcheckdate.process — is the Process Number for the date
• co.mostrecentcheckdate.periodbegin — is the Period Begin date
• co.mostrecentcheckdate.periodend — is the Period End date
• co.mostrecentcheckdate.checkdate — is the Check Date

The different variables will return different formats of dates. For example, the process number will look like a process number YYYYMMDD##. These variables can be used in any of the fields, or can be used for filtering.

Advanced Calculations — Rates

The Report Writer is designed to pull "current" rates based on the report run dates. The following formula will calculate annual salary and use the current base rate:

ee.Rates("Base").salary * 12

Displaying Employee Pay Grade

Each employee can have a pay grade assigned to them via their base pay rate. Presently there is no easy way to obtain the salary rank information in Report Writer as this information is stored at the company level. The only information accessible on the employee is the grade that they are a member of. The following can be used to return the various components of the pay grade for each employee that has one assigned. If an employee does not have a pay grade assigned, they will have a blank value or a zero depending on your Report Writer.

Pay Grade Midpoint:

co.paygrades.itembykey(ee.paygrade(co.safecheckdate)).midpoint

Pay Grade Maximum:

co.paygrades.itembykey(ee.paygrade(co.safecheckdate)).Maximum

Pay Grade Minimum:

co.paygrades.itembykey(ee.paygrade(co.safecheckdate)).Minimum

The above examples use the company's current check date to obtain the pay grade from the employee. You can replace co.safecheckdate with a different date value or variable. If you are using a hard coded value (actual date), it must be in double quotes.

To obtain the %Mid and %Max dollar values from the employee's Rates/Reviews tab, you must compute the annual salary and divide by the value.

Displaying Cost Center Name

If you wish to display a cost center name on a Report Writer report instead of just the code, you will need to use the following as your field on the report:

Field	Formula
cc1	co.l1departments.itembykey(ee.cc1).name
cc2	co.l2departments.itembykey(ee.cc2).name
cc3	co.l3departments.itembykey(ee.cc3).name
cc4	co.l4departments.itembykey(ee.cc4).name
cc5	co.l5departments.itembykey(ee.cc5).name

Displaying Termination Reasons

If you wish to display the Term Reason Description on a Report Writer report instead of just the code, you may use the following as your field on the report:

co.TermReasons.itembykey(ee.TermReason).Description

Displaying Position Title

If you wish to display a Position Title rather than just the Position Code on a Report Writer report, you may use the following as your field on the report:

Co.Positions.itembykey(ee.positionCode).title