Configuring Date Options

As a report creator, you need to understand how the configuration of date options affects the output of the query.

Many tables in Employee Central contain data that can change over time and ,as such, are "effective dated". This means every record will have a start date and an end date. Current records are identified by 31st December 9999. When data changes in the table, a new row is added to the table for the employee or object with a start date of current date and an effective end date of December 31, 9999. The previous row’s effective end date is updated to reflect the new end date.

All Advanced Reporting queries will use these effective dates by default - unless otherwise specified by the Query Designer.

There are a small number of tables in Employee Central that are not effective dated and will contain a single record for each employee. The most common examples of these tables are the Employment Information and Biographical Information tables. These tables contain information that does not change (for example, date of birth), and any modifications to information in these records simply overwrites the existing record.

Tables like these can be identified either by the lack of Effective Start I End Dates fields in them - or by pulling the table into a new query and noting that the Date Options will default to Show All, rather than Current Date.

National ID data for employee mhoff1 does not change over time. Any updates simply overwrite the existing record.

Many tables in Employee Central also contain other dates which may be used in the filters in Date Options.

Users who wish to use these dates (rather than the default Effective Dates), can choose the Use Custom Columns option in the Date Options dialog, and then select the date column to be used from the Start/End Date Column dropdown list.

There are several different options and date settings that can be applied to the query with Date Options:

• Driving Table: This shows the "controlling" table in the query, which other tables (if applicable) will use as a reference for their date settings.
• Date Type: Choose whether the query should return a single date or date range.
• Date Range: Set the From and To dates that will be applied to the query.
• As Of Date: (Not Displayed) Set the single date that will be applied to the query.
• Use Custom Columns: This is an option to override the default Effective Date columns and use another date column available in the table.
• Selectable At Runtime: Allow the report user or consumer to change the query dates when they run the report. (Note that this does not allow report users to change the Date Type.)
• Related Table Options: Set the default dates for all other tables in the query. This option is only used if there are no table-specific overrides set.

The Overrides tab allows you to set table-specific date options on other tables in the query. There may be cases where you need different date types for each of your related tables; for example, if you need the Global Assignment table to reflect the current date but you need the Dependents table to show data from the driving table start date, you will need to set up an override.

If a table has an override, it will obey the overriding date options instead of the related table options set on the previous tab. The driving table cannot be overridden.

Using overrides can also be helpful if you need to join the same table to your query multiple times and pull data from different time periods with each instance of that table. For example, you might want to compare Compensation data from a previous year with Compensation data from this year.

The following date types are available from the Overrides tab:

• Show All: Shows all available data with no restrictions on the date of the data. Returns all records regardless of start and end date
• Current Date: Shows the data that is applicable "today". Note that "today" is a moving target. For example, if today is July 10th, it will show records that were current on July 10th. On July 11th, the query will show records current for July 11th.
• As Of Date: Shows the records that are applicable on the selected date.
• Date Range (On Start Date): Shows the records that started within the selected date range.
• Date Range (Validity): Shows the records that were active within the selected date range.
• Driving Table Start Date (Note this option is only available on Related Tables): Shows the records that were applicable on the Start Date on the Driving Table.

As of Dates

As Of Dates can be static (for example, July 1, 2014) or dynamic (for example, First Day of the Month). Setting a dynamic As of Date means the report will consider the current day and apply the dynamic As of Date based on where it is in the calendar today.

For these dynamic As of Dates, a week begins on a Monday and ends on a Sunday. Quarters are considered as calendar year quarters.

Note

This date table is based on the date August 15, 2014.

The Dynamic Date: Last Successful Run Date is used with the Report Distributor and will return results for the query since the last time the report was distributed.

Date Ranges

Date Ranges can be static (for example, July 1, 2014 to 8th July 2014) or dynamic (for example, Current Month). Setting a dynamic date range means the report will consider the current day and apply it based on where the calendar is today.

• For these dynamic date ranges, a year begins on January 1st and ends on December 31st. Quarters are considered as calendar year quarters.
• Current Year/Month/Quarter to Date includes the days in the current year/month/quarter up to and including today, that is, it will not include future days in the year/month/quarter.
• Current Year/Month/Quarter to Go includes the days from today until the last day in the year/month/quarter, that is, it will include today and future days, no previous days.

Note

This date table is based on the date August 15, 2014