Helpful Crystal Reports Formulas [Solved]

Helpful Crystal Reports Formulas

Crystal ReportNo doubt that if you are reading this article you have something to do with reporting within your organization. As all of Vista HRMS clients know, PDS has standardized with Crystal Reports for development and delivery of reports.

Crystal can be a great tool to do reporting, allowing you the ability to include charts and graphs to help analyze your data. On occasion we find ourselves in need of a report that requires some ‘fancy footwork’ and we need to create formulas within your report to properly display the intended data set.

I am sure, if you are a report writer familiar with Crystal, that you have gotten a request to develop a Crystal report, only to find you need a formula. Formulas can help a great deal in analyzing your data, such as a report that I once wrote to let managers know what day of the week their employees were taking as vacation/sick days, hoping to identify trends. (This is easier then you think!)

PDS has helped to alleviate the need for a great number of the formulas that people commonly request. You will find that we have provided fields that have been formatted into the database views commonly used in reporting. For example:

  • There is no need to create a “Full Name” for your employees; we have a “Name” field for that purpose. Views still contain the separate fields for “First Name”, “Middle Name” and “Last Name” in case you need them.
  • We deliver both soc_sec_no (unformatted) and social_security_no (formatted).
  • You will also find city_state_zip as well as the separate fields.

With that said I would like to present to you a short list of common formulas that I have used over the years. (And a special “thank you” to Chrissy Koennecker for her original article back in 2005 where she shared a similar list.)

Extracting Data from a field:

This formula extracts only the year from a date:
Year (({table.hire_date} )

This formula extracts only the right 4 positions in the field (last 4 of SSN):
Right ({table.soc_sec_no},4)

This formula trims the address line and puts a line feed after the first line of the address:
TrimRight({table.line1}) + chr(10)

Examples of translation formulas:

To spell out a certain code using if-then statements:
if {table.exempt_flag} =’E’ then ‘Exempt’ else
if  {table.exempt_flag} =’N’ then ‘Non Exempt’ else ‘N/A‘

If you want to see the Month name spelled out instead of a number:
MonthName(month({table.hire_date} ))

To Show a Date range parameter in your report, add the following formula:

Totext(minimum({?ParameterDate}) &
” to ” &
Totext(Maximum({?ParameterDate})  )  )

To list an employee’s Length of Service in Months create the following formulas:

Formula1 called Months:
DateDiff(“m”, {emp_basic_all.adj_service_date}, currentdate)

Formula2 called LOS:
Truncate(({@Months}/12), 0)

You can also use formulas within other formulas……

To list an employee’s Age create the following formula:

Year (CurrentDate) – Year ({emp_basic_all.birth_date})

To show the multiple parameter values that were chosen for a report, create the following formula:

Join (@Parameter, chr(13) & chr(10))

* Place this formula in your report header

To show the Day of the Week, create the following formula:

if DayOfWeek({Date field}) = 1 then “Sunday” else
if DayOfWeek({Date field}) =2 then “Monday” else
if DayOfWeek({Date field}) =3  then “Tuesday” else

You get the hang of it! Crystal Reports defaults to Sunday as the 1st day of the week, unless you change it, (which you can do).