The Seniority report lists employee’s by employee number and shows the total amounts for Years of Service, Months of Service, and Total Months.
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 S001was 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)”.
Open
Report Designer > Report Definitionsand press Insert to add a new Report Definition.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.
Drop a source onto the blank area to the right of the columns to create a new column using that data source.
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.
1 The column header is located at the top of the column. 2 The column outline will be displayed while dragging. 3 The 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.
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 1 | Employee Profile > Personal > Employee Number | ||
| Column 2 | Employee Profile > Personal > Employee Name
| ||
| Column 3 | Employee Profile > Dates > Initial Hire Date | ||
| Column 4 | Employee Profile > Dates > Current Date | ||
| Columns 5,6,7 | Formula |
Note: When using formulas, the ordering of the columns will be important if column variables are used (C- and P-variables).
|
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.
The column header text will be changed as below:
| Column 5 | Total Years of Service |
| Column 6 | Additional Months of Service |
| Column 7 | Total Months of Service |
When you have finished, the columns in the report should look like this:
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:
|
@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):
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):
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:
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.
| 1 | The Auto Size property can be used to make the report fill the available page. |
| 2 | The 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). |
| 3 | The Custom Column Format column property can be used to |
| 4 | The 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.
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.
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 Servicecolumn toNumeric 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.
1 Change the Column Format to Custom 2 Enter 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:
| Part | Format | Description |
|---|---|---|
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:
|
For this report the Custom Column Format used was dd MMMM yyyy
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.)
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.