Open navigation

Report Designer: Seniority Report

The Seniority report lists employee’s by employee number and shows the total amounts for Years of Service, Months of Service, and Total Months.

12 21 2018 12 12 04 PM
  • The Years of Service column will show the total number of years since the initial hire date. The amount for this column will display the nearest whole-number of years: e.g. an employee with 14 months since the initial hire date will have 1 Years of Service and not a fractional amount.

  • The Months of Service column will show the number of months in addition to the years of service: e.g. that same employee with 14 months since the initial hire date will have 2 Months of Service.

  • The calculations are performed using the current date and the initial hire date of each employee.

Create a new Report Definition

In order to create the Seniority report we need to create a new Report Definition. A couple of things to keep in mind with every Report Definition:

  • New report definitions should follow the naming conventions of reports in your company. For this report the name HR S001 was chosen, but your company convention may be different.

  • Ensure the title of the report is descriptive. The description of the report can contain up to 128 characters so there is plenty of room to add a little detail about the report. For example, the title for this report is “Seniority Report (from Initial Hire Date)”.

    1. Open Report Designer > Report Definitions and press Insert to add a new Report Definition.

    2. Enter a report Name and Title for the report.

At this point you can press Apply to save the report. The next step will walk through adding all the columns and required data sources for the report.

Columns and Data Sources

The first thing to do with any new report definition will be to add all the columns required for the report. Columns are linked with data sources and will display the corresponding data as rows beneath the column for each employee in the report.

  • Columns are added from the Report Layout tab—this is the first tab near the top of the Report Definition window.

  • The order of the columns corresponds to the default sorting order of the rows. The rows will be sorted according to Column 1 by default unless a different sorting column is entered in Sort Order.

A brief explanation of how to add and rearrange columns within report designer is included below. You can skip to seniority report columns to continue to the setup steps for the report columns.

Adding & Replacing Columns

The fastest way to create new columns is to use the mouse to drag and drop data sources directly into the column area:

  • Drop a source onto an existing column to replace that column’s data source.

    12 17 2018 11 05 26 AM
  • Drop a source onto the blank area to the right of the columns to create a new column using that data source.

    12 17 2018 11 10 47 AM

Changing the Order of the Columns

Caution:

When changing the order of columns within a report ensure that any column variables used within column formulas are adjusted to use the new column number.

The order of the columns can be changed at any time:

  • Press and hold Alt, then drag a column by the column header to change the order of the column within the report.

    12 17 2018 11 17 28 AM
    1The column header is located at the top of the column.
    2The column outline will be displayed while dragging.
    3The destination location will be displayed as a thick vertical bar. You will need to drag the column at least halfway across a column for this bar to appear.

The column order can also be changed from the Column Settings by selecting a Column number directly.

12 17 2018 11 30 03 AM

Seniority Report Columns

The required columns and corresponding data sources for the Seniority report are listed below. Add each indicated data source to the corresponding columns in the report before moving onto the next step.

Add the following sources as columns to the Seniority Report:

Column 1Employee Profile > Personal > Employee Number
Column 2Employee Profile > Personal > Employee Name
Note:
There are many versions of the employee name data source. For this report, Employee Name (Surname, Preferred or Given) was used.
Column 3Employee Profile > Dates > Initial Hire Date
Column 4Employee Profile > Dates > Current Date
Columns 5,6,7Formula
Note:

When using formulas, the ordering of the columns will be important if column variables are used (C- and P-variables).

  • Columns are calculated from left to right which will require that any column variables used correspond to lower column numbers.

  • If a column variable of a higher column number is used you will always get a 0 for that variable’s values.

Changing the Column Header Text

When adding columns from data-sources the columns will be setup with default header text corresponding to the data-source used. For the formula columns this defaults to "Formula" which is not very informative. Let’s change the header-text of the three formula columns before moving on to help us keep track of the columns.

The column header text can be changed by first selecting the column, then changing the Header Text value within the Column Settings.

12 21 2018 10 53 51 AM

The column header text will be changed as below:

Column 5Total Years of Service
Column 6Additional Months of Service
Column 7Total Months of Service

When you have finished, the columns in the report should look like this:

12 21 2018 10 58 25 AM

Formula Driven Columns

Using a formula source lets you use a formula to calculate the output of the column. The three formula columns are where the seniority report will calculate and display the amounts for seniority years, months, and total months.

In the following sections the setup for each of the three formula columns will be covered. All three columns will use the built-in formula function DATEDIFF to handle the calculation and rounding of dates.

Rounding Dates

When calculating the seniority dates for employees, an important consideration is how to handle rounding dates that occur within the same month of different years.

To give an example, if an employee has an initial hire date of December 20th of the previous year, how many months of service will they have on December 1st the following year?

  • If you round to the nearest month, you would calculate 12 months of seniority.

  • If you round to the nearest day, you would calculate 11 months of seniority. This is because the employee’s initial hire date is further into December.

Note:

The formulas in this document are setup to round to the nearest month.

The DATEDIFF Function

Counting the number of years and months can be done quick and easy by using the DATEDIFF function within a formula.

Note:

When using the datediff or other built-in functions in a formula:

  • the name of the function is preceded by an at-sign (@),

  • the parameters of the function need to be enclosed in open and close braces, with the parameters separated by commas.

@DATEDIFF( OPERATION, VARIABLE, VARIABLE )

This function requires three parameters (ordered from left to right):

  • The first parameter is operation to perform. For the seniority report the YEARS and MONTHS operations will be used.

  • The second parameter is the date variable to compare from. (You can remember this by thinking: how much time has passed since this date.)

  • The third parameter is the date variable to compare until. (You can remember this by thinking: how much time has passed up to this date.)

Years of Service Column

The Years of Service column will count the years of seniority for each employee using the DATEDIFF YEARS operation. This operation returns the number of whole years between two dates.

This is what the column formula should look like:

Value = @DATEDIFF( YEARS, C3, C4 )
  • When using any functions within a formula you must proceed the function name with the at-sign (@).

  • The YEARS operation tells the DATEDIFF function to return the number of whole years between the two dates.

  • C3 is the column variable corresponding to Column 3, which is the Employee Initial Hire Date data-source for this report.

  • Similarly, C4 will hold the value of Column 5, which is the Current Date data-source.

To enter the formula for the column, select Column 5 then open the Formula setup under Column Settings (see below):

12 21 2018 11 01 23 AM

Additional Months of Service Column

The setup for the Additional Months of Service columns will be very similar to the previous column, except that it will use the DATEDIFF MONTHS operation.

Value = @DATEDIFF( MONTHS, C3, C4 )
  • The MONTHS operation returns the number of months remaining between two dates—this is not equal to the total number of months between the dates! The remaining months is calculated by first counting the number of whole years between the two dates and returning the number of months leftover.

To enter the formula for the column, select Column 6 then open the formula from Column Settings > Formula (see below):

12 21 2018 11 10 28 AM

Total Months Column

The total months column will effectively combine the Years of Service and Months of Service columns to display the total amount of seniority months per employee.

This is what the formula will look like (this has been split into multiple parts to make the steps clear):

F1 = 12 * @DATEDIFF( YEARS, C3, C4 )
F2 = @DATEDIFF( MONTHS, C3, C4 )
Value = F1 + F2
  • The first line returns the years of seniority (the same as the Total Years of Service column) and converts it into months (by multiplying by 12).

  • The second line returns the additional months of seniority (the same as the Additional Months of Service column)

  • The third line combines the values of the previous two lines giving the total months of service since the initial hire date.

Enter this formula into Column 5:

12 21 2018 11 13 02 AM

Finishing Touches

At this point the report you have designed will work but there are some simple adjustments that can be made to increase the readability of the report.

12 21 2018 12 35 50 PM
1The Auto Size property can be used to make the report fill the available page.
2The Visible column property can be used to hide columns in the report (hidden columns will not show up when the report is printed, but can still be used in formulas).
3The Custom Column Format column property can be used to  
4The Right- and Left Column Margin column properties can be used to add space between columns.

Maximize the Report Space using Auto Fill columns

The columns in the report are assigned a default fixed-width when they are added. This can have the effect of leaving a blank space on the right side of the report. It would be more aesthetic if the columns spanned the entire page, so that the service totals were aligned to the right side of the page.

We can achieve this effect by setting the Auto Fill property on one or more columns. Auto fill will adjust the columns width to fill up additional space on the report.

12 21 2018 12 33 11 PM
  • For this report we’ll enable Auto Fill for the Employee Name to allow it to fill up the page, pushing the service total to the right like we want.

Hide report columns

The initial date column in this report is redundant because the current date will be included in the report header. Instead of having this column take up extra space we can hide the column entirely by changing the Visible option in Column Settings to No.

12 21 2018 12 16 14 PM

Change the format of column data

By default, formula columns will show values to two decimal places of precision. However, all the amounts that are used in this report are to the nearest whole number of years and months, so we can get rid of the decimal places by changing the Column Format to one of the Numeric formats.

  • Change the Column Format of the Total Months of Service column to Numeric 1 (0). (This column format will display at minimum 1 digit, which is useful when we want to display zeros in the output.)

Custom Column Format labels

If you want to take things a step further, you can define custom columns formats. Custom formatting can be useful when you want to include extra information like units to the column output.

  • Change the Total Years of Service column to use a Custom Column Format that will add the units (years) to the number of years.

    12 21 2018 12 10 04 PM
    1Change the Column Format to Custom
    2Enter the Custom Column Format to use in Advance Column Settings. For the Total Years of Service Column the Custom Column Format used was 0 years:
    Note:
    • The '0' in the custom format works the same as the Numeric 1 (0) Column Format by displaying at minimum 1 unit.

    • The 'years' part will just be included with the output.

  • Do the same for the Additional Months of Service column but change the Custom Column Format used to include the months as a label instead: e.g. 0 months.

Customize date formatting

The format of dates can also be changed using the Custom Column Format column property.

The format specifiers for dates are below:

           
PartFormatDescription

day

d

Day of the month from 1 through 31 (one-digit minimum)

dd

Two-digit day of the month from 01 through 31

ddd

Abbreviated name of the day (Mon, Tues, Wed, …).

dddd

Full name of the day (Monday, Tuesday, …).

month

M

Month number from 1 through 12 (one-digit minimum)

MM

Two-digit month number from 01 through 12.

MMM

Abbreviated name of the month (Jan, Feb, …).

MMMM

Full name of the month (January, February, ….).

year

y

Last two digits in the year from 1 through 99 (one-digit minimum).

yy

Last two digits in the year from 01 through 99.

yyyy

All 4 digits of the year.

Note:
  • Spaces and punctuation can be included between the specifiers.

  • Specifiers can be included more than once.

  • For this report the Custom Column Format used was dd MMMM yyyy

    12 21 2018 1 16 36 PM

Add a bit of breathing space using column margins

One small but final touch that can be done to make any report pop is to add small amount of space around the columns: by default, the columns will be tightly packed to display as much as possible on the report, but for the seniority report we have the room to spare.

Add a small amount to the Right- and Left Column Margin properties of the report columns. (Try starting with a small amount like 4.)

12 21 2018 1 26 32 PM
Tip:
 Properties for multiple columns can be changed at once by selecting more than one column at a time. Any properties changed will affect all selected columns.

Conclusion

With the skills you’ve acquired to make a report like the seniority report you should have a good foundation to make custom reports that draw on the diverse data sources available within Report Designer.

Mastering formulas will enable you to create advanced reports that go beyond what is directly available from Report Designer sources.

J
Jack is the author of this solution article.

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.