Creating Queries with Advanced Reporting on Replicated Data

Objectives

After completing this lesson, you will be able to:

  • Create a new advanced reporting query
  • Manage fields in an advanced reporting query
  • Create calculated columns
  • Create aggregated calculated columns
  • Configure tables in the query
  • Define table restrictions
  • Configure person context
  • Create filters
  • Configure additional query options
  • Use change reporting
  • Configure metadata changes with advanced reporting
  • Navigate Employee Central data
  • Configure date options
  • Use date types to achieve desired results

Advanced Reporting Queries

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 Work Area

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 after Report Center is enabled
  2. Open Query - Not used after Report Center is enabled
  3. Save Query – Save the current query
  4. Save Query As - Not used after Report Center is enabled
  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 after Report Center is enabled
  13. Module Configuration – Open module configuration
  14. Change Reporting Column – Open change reporting configuration
Hint
The query is not completely saved the first time you create it unless you return to the canvas of the report. After returning to the canvas, you can use the Save button to in advanced reporting to save changes to the query.
Note
To create a pivot query with an advanced reporting query as the data source, add a pivot table to the canvas (Table or Chart based on published data), and choose new advanced reporting query as the data source when prompted. Using pivot query designer is covered in a different lesson.
Note
Module configuration is only available when the report contains data that uses forms. It is like Data Sets in other reporting tools for SAP SuccessFactors.

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.

Query Layout

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

Results Tab

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

Results Tab and Preview Rows

The results will default to the first 10 records, adjust this to a maximum of 100 by setting the Rows per page value:

Results Tab and Excel Exports

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

Calculated Columns

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 Column Wizard

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:

Value for Aggregation

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:

Aggregation Function

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

Objects Tab and Joins

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

Join Types

Inner Joins

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

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:

Objects Tab and Column Selection

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:

Objects Tab and Column Relabeling

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:

Columns Tab Overview

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

Objects Tab and Duplicate Objects

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

Objects Tab and Table Restrictions

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.

Objects Tab and Object Removal

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

Person Context

People Filtering

The query designer can choose to apply a person filter to the query to show just the user's direct and indirect reports. Choose Person Context from the menu bar to set a person filter:

Configure the options appropriately:

  • Select Person Column: Choose which User Id in the query the person to which the filter will be applied.
  • Qualifier: This field will be described below this list.
  • Distance: How "far" down the people structure to retrieve results - for example, a setting of 1 will retrieve just direct reports.
  • Person: Logged In User: This will default to the report to the reports of the user who is viewing the report – otherwise select this option to manually set a particular user.
Note
A user needs permission to view the status field of Employee Data for People Filtering to work properly.

People Filtering and Qualifiers

The following outlines people filtering and qualifiers:

  • Selected element only: Report will filter on just the selected user
  • Selected element and all descendants to distance: Report results will return the selected person and all their direct and indirect reports down to the level specified by Distance
  • All descendants to distance: Report results will return only the selected persons direct and indirect reports down to the level specified by Distance (note this option does not include the elected user itself)
  • All descendants at distance only: Report will return only the selected users reports at the level specified by Distance
  • Selected element and all descendants: Report will return the selected person and all their direct and indirect reports
  • All descendants: Report will return all the selected person’s direct and indirect reports

Note
Person Context is like People Scope functionality in the live data source.

Query Filtering Overview

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.

Example - Query Filter Set Up

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:

Note
The label matching an identifier will be displayed as a tool tip to help distinguish the correct identifier. This may be especially useful when the identifier is numeric or abbreviated (for example, the label for the Human Resources department may show in the tool tip when you are mousing over the department ID of 174).

Employee IDs

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.

Filter Management

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:

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

Sorting

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

Change Reporting

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

Change Reporting Configuration

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.

Note
Change reporting configuration can only capture the change on a single field. For more detailed change reporting, refer to the "Advanced Reporting: Create Change Reports Manually" on the SAP Help Portal.

Employee Central Metadata Change Syncing

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.

Note
Use this option cautiously because purging the metadata cache to sync the metadata changes might impact performance and cause errors for active users.

Employee Central Blocks and Fields

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.

Employment – Main Tables

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:

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

Employment - Additional Tables

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

Tables and Parent Tables - Supervisor

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:

Tables and Parent Tables - Accompanying Dependents

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:

Tables and Parent Tables - Positions

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 Category

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:

Person - Additional Tables

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)

Global and Country Specific Tables

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:

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.

Date Fields - No Effective Dates

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.

Date Fields - Custom Dates

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.

Date Options Tab

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.

Overrides (Advanced) Tab

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.

Setting an Override

  1. A list of tables is displayed with options to Edit or Clear
  2. Selecting Edit on a table will show a Date Type selector for that table
  3. Choose a date type and choose Set to apply it to the query or select Clear to remove previously set options.

Date Types

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

Default Behavior

Current Date or Show All

The first table pulled into a new query in Advanced Reporting will become the "Driving" (or "controlling") table and default to the Current Date date option (for effective dated tables) or the Show All date option (for table with no effective dates).

This example shows Job Information data for employee mhoff1. By default, the query will return the most current record - as identified by "today" being between the Effective Start and Effective End Dates:

This example shows Compensation data for employee mhoff1. Note that here, the employee can have multiple Pay Components current as at "today", hence the multiple records:

Show All Records

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.

As Of Date

To display records that were current on a specific date, choose As Of Date. Setting an As Of Date to December 09, 2016, will return records where December 09, 2016 is between the Effective Start and Effective End Dates.

Date Range (On Start Date)

To display records that started in a certain date range, select Date Range (On Start Date).

Setting a Date Range (On Start Date) from December 09, 2016 to December 09, 2016, will display records where the Effective Start Date is within this date range.

Date Range (Validity)

To display records that were active in a certain date range, select Date Range (Validity).

Setting a Date Range (Validity) from April 12, 2016 to April 12, 2016, will display records are active within this date range.

Multi-Table Queries

When a query has more than one table, the first table pulled into the query will become the driving (or controlling) table which is identified by the green outline in Object View. Subsequent tables will (by default) show records for the Current Date.

Effects on Results - Current Date

Use the default Current Date option to see the records that are applicable today.

The example shows a query on the Job Information table and the Compensation table, both showing "current date". When these tables are used in the same query, it will "match" the current date Job record with the current date Compensation records.

Effects on Results - Current Date + Show All

Use Current Date on the driving table to retrieve the current / active records from the  driving table and Show All on the related tables to only see historical values.

This example shows a query on the Job information table showing mhoff1 Job Title as of today and all records from the Compensation information table to view his compensation history:

The Driving Table Start Date option is only available on related tables. This option will retrieve the record that is applicable at the Start Date of the driving table.

 

Here we have a query on the Job Information table showing Job records January 1st 2011, which will show active employees as of that date. The compensation information returned is the active compensation as of the same date.

Exercise: Create a Query with Advanced Reporting: Part A

Business Example

In this exercise, you will create a list of employees and their assigned HR Manager. You will use joins and table restrictions to accomplish the task. You will also include a calculated annual compensation and pay grade values from two different time periods. In part A, you will learn how to navigate and join tables.

Exercise: Create a Query with Advanced Reporting: Part B

Business Example

In this exercise, you will create a list of employees and their assigned HR Manager. You will use joins and table restrictions to accomplish the task. You will also include a calculated annual compensation and pay grade values from two different time periods.

In part B, you will learn to use table restrictions.

Exercise: Create a Query with Advanced Reporting: Part C

Business Example

In this exercise, you will create a list of employees and their assigned HR Manager. You will use joins and table restrictions to accomplish the task. You will also include a calculated annual compensation and pay grade values from two different time periods.

In part C, you will limit results to direct reports of the logged in user.

Exercise: Create a Query with Advanced Reporting: Part D

Business Example

In this exercise, you will create a list of employees and their assigned HR Manager. You will use joins and table restrictions to accomplish the task. You will also include a calculated annual compensation and pay grade values from two different time periods.

In part D, you will display the current annual compensation by creating an aggregated calculated column on base salary and bonuses.

Exercise: Create a Query with Advanced Reporting: Part E

Business Example

In this exercise, you will create a list of employees and their assigned HR Manager. You will use joins and table restrictions to accomplish the task. You will also include a calculated annual compensation and pay grade values from two different time periods.

In part E, you will learn to add duplicate tables with different date options.

Log in to track your progress & complete quizzes