Creating Queries with Advanced Reporting on Replicated Data

The Advanced Reporting designer allows users to query Employee Central data which are queries that can be used to generate reports for distribution and analysis among analytical, HR-related and managerial roles. This section outlines the functionality in the designer including:

• Adding and removing columns
• Setting query filters and table filters
• Creating calculated and aggregated calculated columns
• Setting date filters using date options
• Adjusting column order and setting column sorting

Just like the detailed reporting query designer, you access Advanced Reporting as an integrated functionality in Page Designer.

To access the Advanced Reporting tool, complete the following steps:

1. Navigate to the Report Center.
2. Choose New.
3. Select the report template Canvas and choose Select.
4. Provide a name for the report and choose Create.
5. The Page Designer will load to create a new page.
6. Add a list report component to the page. Edit the query of the new list report.
7. In select query, select the Advanced Reporting tab and select the New button.

Advanced Reporting groups data into a schematic that provides for quick and simple querying, yet it is flexible enough to support more complex reporting requirements.

Advanced Reporting consists of categories, tables, and columns:

• Categories consist of tables and columns grouped around a particular subject or concept - for example, employment, compensation, and person. A category is selected from the upper-left dropdown list on the query designer.
• Tables can be blocks, sections within blocks, Foundation Objects or Metadata Framework Objects. Tables are identified by a >which helps you expand and view all the columns under it. Choose the arrow to the right to continue navigating to related tables.
• Columns are fields or cells contained within the table.

The menu bar contains the following buttons, many are described in more detail later in the lesson.

1. New Query – Not used
2. Open Query - Not used
3. Save Query – Save the current query
4. Save Query As - Not used
5. Cancel Query Changes - revert to the last save
6. Manage Query Filter – Open query filter designer
7. Manage Runtime Filters – Open runtime filter designer
8. Person Context – Open person context selector
9. Date Options – Open date options
10. Sort Columns – Open column sorting
11. Calculated Columns – Open calculated columns designer
12. Pivot Query Designer – Not used
13. Module Configuration – Open module configuration
14. Change Reporting Column – Open change reporting configuration

Searching

Searching will produce a list of potential matches and the category and object the column is found in the following:

Category Selection, Object and Column Listing

Alternatively, a user can navigate the category listing using the dropdown selection box.

Selecting a category from here will display a list of objects and columns available for that category.

Opening objects and expanding objects will display other objects that relate to the "parent" object, as pictured in this screenshot below.

CTRL + click individual fields or entire objects and drag them into the Query Layout pane to build your query:

The Results tab will display a preview of the query data:

The Query tab is intended as a preview of results only. To view the entire result set, export the query to Excel:

Custom columns can be created using the Calculated Columns function. Choose Calculated Columns on the menu bar:

Creating Calculated Columns

The fields are described as follows:

• Name: Choose a name for your calculated column.
• Data Type: Choose whether your calculation is text, number, or date.
• Format: Choose a format for number or date calculations (for example, show the date as DD-MM-YYYY).
• Number/Date/Text groups: A list of all the columns available from the tables that are included in the query. These columns are grouped into their base format, select each header to slide open the list of columns available in that base format.

Choose and drag columns from the list and select operands and function buttons at the top to build your formula for the calculation.

Here we have used the IF/THEN/ELSE condition to output amount * 12 if the frequency of the compensation is monthly.

Static values are input with Click this button to add a text, number, or date static value into the calculation.

Note

For more detail and examples of using calculated columns, you can review the Creating Queries with the Detailed Reporting section.

Aggregated calculated columns can be used to aggregate or combine data into a single result or list. In this example, we will sum all Compensation records to get a Total Compensation result for each employee:

In this example, we will aggregate all Compensation values, so we will set the column Amount from the Compensation object:

With Amount chosen as the column for aggregation, we now choose how to aggregate. Since we would like the total compensation per employee, we will choose Sum:

There are now two calculations in the query. Toggle the display of these columns in the query by choosing the red/green buttons:

The Objects tab will display how objects are related and how they link together.

Inner Joins show only records that exist in both objects. In this example employee CCC is not shown in the query results because they do not exist in the Spot Bonus object.

Left Joins show all records from the object on the left and only records that match from the object on the right. In the example shown in the figure, Left Joins - Example, employee CCC is shown in the query results because they exist in the Global Job Information object, however with empty results from the Spot Bonus columns because they do not exist in that object.

Choose the Edit Table button on the object to see all columns in the table. You can quickly include columns by checking the box beside the column. Choose Apply when finished to apply the changes to the query results:

The Edit Table button also allows access to setting a different column label. Select the A icon to give the column a new label, and then choose OK to save the label and Apply to set the changes in the query:

You can manage columns labels, sorting, filtering from the columns tab.

You can duplicate whole objects (and their columns) by choosing the Edit Table icon and then the Duplicate icon:

You can restrict objects by choosing the Edit Table icon and then the Edit Restriction icon.

Object restrictions allow filtering on that specific table without affecting the overall query. This can be useful to only pull through specific data elements as required into the query result without affecting the overall result set.

Restrictions are defined on the object in the same manner as query filter. For example, showing only Base Salary compensation information, without removing employees that do not have a base salary from the query result set.

You can remove whole objects (and their columns) from the query by selecting X:

To add a static filter, choose Manage Query Filter from the menu bar.

Fields

Filter Field: Contains a list of the objects that are included in the query, and the columns in those objects – open these to select the column to which the filter will be applied.

Operator: Select an inclusive, exclusive or a "like" filter.

Value: This field allows for the following:

• Custom Filter: Enter a single value to be filtered.
• Report Values: Set a list of values to be filtered.
• Field Comparison: Choose another column in the query to which it is to be compared.

For example, choose a column to filter (Employment Status), choose Operator, and then select the value/values that will be filtered in or out of the query:

If you have a list of Employee IDs separated by commas, you may paste them in as filter values so long as the filter limit of 2000 is not exceeded. This is possible in either Manage Filters or Runtime Filters.

You can add, edit, or delete a filter from the Filter Designer window. Toggle filter conditions by clicking on the AND/OR links between filters:

You can select the columns that can be filtered on by report consumers when the report is run. Choose Manage Runtime Filters from the menu bar to open and set runtime filters:

To apply sorting on columns, choose Sort Columns on the menu bar.

Change Reporting will display the previous value for a selected column.

After selecting the Change Reporting button, set your change reporting configuration:

Change Reporting On: Choose a column from the query to show the previous value for it.

Change Reporting For: This column will be automatically selected according to the "key" for the query.

Change Reporting Filter: This allows you to set the same filter on the previous value results, as is applied to the query results by selecting the column that is filtered in the query.

Distance: Choose "1" to show the previous value for the selected column or "2" to show both the previous value and the value two changes ago.

Show Start Date for previous value(s): Include a Previous Start Date column/s in the query that show the start date for the previous value/s.

Filter records without a previous value: Remove records from the results that do not have a previous value.

Consider the following example: Employee EmployeeAB, with a Change Car Allowance event, changed from Department ARG010001 Health Service to Department ARGENTINA.

This employee started at Department ARG010001 Health Service on 16th July 2014 and then started at Department ARGENTINA on the 23rd of July 2014.

From time to time, EC administrators may make configuration changes to the metadata configuration such as the creation of new fields or custom Generic Objects. .

The metadata changes you make in Employee Central are automatically reflected in the Advanced Reporting solution the next day, as a daily job syncs the metadata changes.

However, if you want the Employee Central metadata changes to be reflected in Advanced Reporting immediately, you can use the Purge Advanced Reporting Metadata Cache option on the Workforce Analytics Administration page.

Advanced Reporting consists of categories, tables, and columns:

• Categories consist of tables and columns grouped around a particular subject or concept - for example, employment, compensation, and person. A category is selected from the upper-left dropdown list on the query designer.
• Tables can be blocks, sections within blocks, Foundation Objects or Metadata Framework Object. Tables are identified by a > which helps you expand and view all the columns under it. Choose the arrow to the right to continue navigating to related tables.
• Columns are fields or cells contained within the table.

Navigate to the Employment category by selecting it from the top dropdown. Alternatively, start typing employment into the dropdown. Select this category and its main table will appear, Global Job Information:

The Global Job Information table contains data typically found in the Job Information section of the Job Information block:

Additionally, the Global Job Information table contains the employees most current name (first, middle, last) found in the Personal Information section of the Personal Information block:

Opening Global Job Information will reveal more tables that contain data relating to an employee’s employment. Bold type indicates a group of tables.

• Foundation Objects: Contains the foundation objects that are relevant to the employee's Employment (for example, cost center, business unit, division, event)
• Incumbent Position: Contains the Position details that are relevant if the employee is nominated as a Position Incumbent
• Position: Contains the Position details that are relevant to the employee's current position
• Benefit, Compensation, Advances, Deduction: Contains the different compensation details that are relevant to the employee
• Person: Contains the personal details for the employee (for example, date of birth, gender)
• Time Off: Contains the absence/time off details for the employee

• Address: Contains the address details that are relevant to the employee
• Employment Details: Contains the standard assignment details that are relevant to the employee
• Global Assignment: Contains the global assignment details that are relevant to the employee
• Supervisor Global Job Information: Contains the employees Supervisor employment details
• Job Information (): Contains the country specific information for the employees Job (for example, EEO, FLSA)
• Job Relationships: Contains the relationships that are defined for an employee (for example, HR Manager)
• Pension Payments: Contains the pension details for the employee

Drilling deeper into the Global Job Information tree will reveal more tables and columns. These tables and columns will contain information relating to their parent table - for example, expanding related tables on the Global Job Information, will reveal the Global Job Information for the supervisor, opening the Global Job Information for the supervisor will reveal the Global Job Information for the supervisor’s supervisor:

Consider the following example: expand related tables on the Global Job Information, which will reveal the employees Accompanying Dependent. Opening the Accompanying Dependent will reveal the biographical and personal information about the Accompanying Dependent - as shown in this figure:

Consider the following example: expand related tables on Global Job Information, which will reveal the employees Position. Opening the Position will reveal the Positions parent details:

Person stores all information about an employee that relates to them as a person - including their name, date of birth, and gender. Other personal information includes (for example):

• Nationality and birthplace
• Marital status
• Preferred language
• Title (if applicable)
• Citizenship, visa and/or national ID information
• Emergency contacts
• Work eligibility information
• Phone, address, and email information

As with the Employment category, the Person category will allow users to "drill through" to information about the employee that is found in other categories, such as hire date, employment status (found in the Employment category) and compensation details like Advances and Spot Bonuses (found in the Compensation category).

Person - Main Tables

Navigate to the Person category by selecting it from the top dropdown list. Alternatively, start typing person into the dropdown list. Select this category and its main tables will appear:

Person > Personal Information

The Personal Information table contains data typically found in the Personal Information and Biographical Information sections of the Personal Information block:

Opening Personal Information will reveal more tables that contain data relating to an employee’s Person. Bold type indicates a group of tables.

• Foundation Objects: Contains the foundation objects that are relevant to the employees Person (for example, Territory)
• Employment: Contains the employment details that are relevant to the employee (refer to further detail in the Employment Category section of this document)

• Corporate/Home Address: Contains the corporate/business, home, vacation address, and so on, details for the employee
• Dependents/Emergency Contacts: Contains the details of any dependents and emergency contacts the employee may have
• Email/Phone Information: Contains the employees email addresses and phone numbers
• Global Information: Contains the employees global "person" information (for example, Person ID, User ID, Territory)
• National ID/Document Information ()/Work Permit Info: Contains visa, citizenship and any other documents relating to the employee
• Social Accounts: Contains the details for any social media accounts the employee might hold (for example, Twitter, Instagram, and so on)

Where applicable, drilling deeper into some tables will reveal country specific tables – identifiable by the country code in parentheses. Expanding related tables on the Global Information (Global) table (for example), will reveal the Country Specific tables for the same. Here we can see that the country specific columns for Brazil (Global Info (BRA)) are Naturalized Citizen and Race; whereas for the USA (Global Info (USA) they are for Veterans data:

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: 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: 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 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

To display all records for this employee, simply change the date options to Show All (note this date option is the default for tables that are not effective dated).

This example shows all compensation data for employee mhoff1. These records will include all compensation pay components since the employee was hired.