Creating Queries with Detailed Reporting on Live Data

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Create a new Canvas Report
  • Create a new detailed reporting query
  • Manage fields in a detailed reporting query
  • Create calculated columns
  • Configure people scope
  • Create static and runtime filters
  • Define data sets
  • Configure additional query options
  • Create a multi-domain query
  • Identify LMS specific query configurations and limitations

Report Center

 To launch Report Center, complete the following steps.

  1. Log in to SAP SuccessFactors.
  2. Navigate to HomeReporting.
  3. The Report Center screen opens.

    Reports will be listed as the type, Canvas.

Create a Canvas Report

To create a new report in Report Center, 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.

    You can include translated report names with Add Localization.

  5. The Page Designer will load to create a new page.

You can return to Report Center by selecting Report Center from the breadcrumb menu (HomeReport CenterReport name) in the top-left corner.

Introduction to Page Designer

When you create a new canvas report with Report Center, a report with a single blank page is generated. The content of canvas report page is built using Page Designer. Page Designer builds pages based upon components.

You can add a report component by dragging and dropping the appropriate icon onto the canvas OR right click the canvas page. The Page Designer will insert a generic placeholder for the component.

List Reports Component

Queries are the building blocks of canvas reports in SAP SuccessFactors. Queries on live data from all SAP SuccessFactors modules can be built using the Detailed Reporting query tool.

The Detailed Reporting query tool provides the query functionality within Page Designer.

A list report component pulls data from queries and displays the information as a list of transactions or records in the Page Designer tool. When adding a list report component, you can build a new query or select a query previously built using Detailed Reporting. The following explains how to use the Detailed Reporting tool to build queries.

To add a List Report component to the canvas in Page Designer, complete the following steps.

  1. Right-click the blank area of the canvas.
  2. Select Add TableList Report.
  3. Resize the table on the canvas by dragging the edge of the component.

Detailed Reporting Query Launch

To populate the list report component with data, you must design the query. To create a query with live data, you will use the Detailed Reporting query tool. To launch the query tool, complete the following steps.

  1. Select a list report component and edit a query.
    • Select the list report component.
    • Choose edit query in the edit tile.
  2. Ensure Detailed Reporting is selected and launch the Detailed Reporting query tool.
    • On the Select Query page, verify Detailed Reporting is selected and choose New.

Exercise: Create a new Canvas Report

Business Example

In this exercise, you will create a new canvas report and configure the page settings.

Reporting Domains, Tables and Fields

Reporting domains

To begin building a query, add a field to the workspace by either clicking and dragging or double-clicking on the field. You can also add fields using the Edit tab, which allows you to select multiple fields at one time. As you add fields, the query will start to build.

Note

If certain fields have been configured as sensitive in the source modules, (and Read Logging has been enabled) the fields will be displayed with a SENSITIVE label. This helps report designers avoid using sensitive data in reports unless truly required. For more information about this and other features related to GDPR, please view the information at https://help.sap.com/docs/SAP_SUCCESSFACTORS_HXM_SUITE.

Note

You can add all the fields of a table by dragging the table to the workspace.

To remove a field, on the Edit tab, select the table that has the field you want to remove. From the list of the fields, deselect the box beside the field to be removed. Choose the table again to close the field list.

Reorder Columns

To move a column in a query, choose and hold the column header and drag the column to a new location.

Exercise: Build an Employee Information Query

Business Example

In this exercise, you will build an Employee Information query using several fields.

Introduction to Calculated Columns

A calculated column is a custom column added to a query that calculates results or links text fields.

To add a calculated column, begin by choosing EditCalculated Columns. Choose + to create a calculated column and open the Calculated Column Designer. Choose the data type and use the condition editor to define the parameters of the calculated column. Three different data types can be used in Calculated Columns:

  • Text - alphanumeric information (sometimes called strings)
  • Number - numeric information that supports calculations
  • Date - store dates and/or time information

The Calculated Column Designer has 3 distinct sections:

  • Column Settings (Top Left): Provide a Name, data type of the output, and formatting (if applicable)
  • Tables and Fields (Bottom Left): Allows selecting the table and fields. Note that each data type is in a separate pane
  • Formula Builder (Right): Area where you build your formula and add operators

To add a field to the formula:

  1. Click on the appropriate box in the work area where you want to place the field.
  2. Locate the field to be added by selecting the appropriate data type and expanding the appropriate table.
  3. Drag the field into the selected box

    Tip: locate the field(s) you want to use in your formula in the query designer first, as they are listed in alphabetical order in that interface.

To add an operator to the formula, such as add or divide:

  1. Click on the appropriate box in the work area where you want to place the operator.
  2. Drag the operator to the appropriate location in the formula (i.e.,. to the left or right of a field).

    Tip: if you click an operator, it will add it as the last item in the selected box.

In the calculated column designer,tokens allow you to add additional information that is not contained in a field.

You can add static text, number, and dates. For example, if you want to use a formula that divides and annual salary by 12, you would use a token that stores the number 12, you will use a token that stores the number 12 in the formula.

You can also use a token to represent some dynamic data. For example, you can use a token to represent Today’s date. That means every time someone runs the report, it will use the current date in the formula.

To delete a part of the formula, drag it into the box labeled Trash in the lower right.

A concatenation is the joining of two data strings, in this case text fields. First, select the data type as Text and add two or more fields to the workspace by dragging them into the Value box.

For example, you can create a text concatenation that will show a user's full name:

  • Set the data type to Text
  • Add the Text fields first name
  • Add the second Text field last name

An example of this is as follows: "Employee" "First Name" + "Last Name" (for example, "Kenneth Roden" "Kenneth" + "Roden").

Use number formulas to calculate sums, differences, products, and quotients. First, select the data type as Number and add two or more fields to the workspace with the appropriate operators. You can edit the format to show decimal places or to change the number to a percent.

For example, you can create a number formula that will show a user's combined salary:

  • Set the data type to Number
  • Add the field Total Pay
  • Add the + operator
  • Add the field Bonus

An example of this is as follows: "Base & Merit" "Total Pay" + "Bonus" (for example, "56000" "50000" + "6000")

The date formula can either be a number or a date data type. If using a date data type, you can edit the format to post the dates using numbers or to use a custom format.

When creating a calculated column that will be used to calculate the number of days for an event, use Number as the data type, and then select a Date field.

For example, to calculate days overdue:

  • Set the data type to
  • Add the Form End Date date field
  • Add the - operator
  • Add the field Form Due Date date field

An example of this is as follows: "Form Overdue Days" "Form End Date" - "Form Due Date" (for example, "4" "01/20/2013" - "01/16/2013")

Hint

The output of this comparison of two dates is a Number of days. Be sure to select the appropriate data type.

Note

You cannot add a static value directly to a date field

If / Then / Else statements compare two or more sets of data and test the results. If the results are true, the THEN instructions are taken; if not, the ELSE instructions are taken.

When creating a calculated column that will be used to calculate if/then/else statements, use Number, Text, or Date as the data type. Then choose the if / then / else statement icon on the workspace:

For example, to calculate gender:

  1. Set the data type to Text.
  2. Choose the If/Then/Else statement icon.
  3. On the IF line, add the Gender field, the = operator, and M text.
  4. On the THEN Value line, add Male.
  5. On the ELSE Value line, add Female.
  6. If necessary, use the && and OR icons to add in more statements.

Example: If Gender M, Then Male Else Female.

You can nest IF statements to provide more than 2 possible outcomes. Nesting means to put one IF statement inside another as part of the THEN or ELSE. For example, you could convert the number of days of the week into text. You would need 7 possible outcomes so you would nest 6 IF statements.

Nested IF statement effect performance of the report. SuccessFactors recommends no more than 10 levels of nesting.

Functions

You can utilize functions in the calculated column editor. Functions perform an operation on a field or token to return a different value than the original.

The calculated column editor has several text function and date function.

To use a function:

  1. Click on the field or token.
  2. click Edit by the function.
  3. Select the appropriate function.
  4. Supply any additional parameters required for the function.
  5. Click OK.

Date Functions

The calculated column designer has two types of date functions:

Year, Month, Quarter, Day functions all return a numeric value for the portion of the date of the input. For example, if the input value of the hire date is February 12, 2022, then the result of the functions would be:

Year 2022

Quarter 1

Month 2

Day 12

You can also use the Age function, based on a selected date column. You must supply a parameter of the comparison date, either a static date or a dynamic date such as today.

Results will be in whole numbers and increment on the same day and month as the selected date column.

The age function can be used to calculate the age of a person (using Birthdate) or the age of a tenure (using Hire Date). Choose the date field from which to calculate age and set the Age function.

The calculated column designer has three text functions. They all return part of the input text. You can input their text length requirements and choose whether they would like the left, middle, or right part of a selected text.

You can input their text length requirements and choose whether they would like the left, middle, or right part of a selected text. Choose a field that you would like to extract a few characters from. Set the middle, left or right function and the number of characters to show.

Example: If the National lD has a "-" at the second character, show the first 5 characters in Document Number, if the National lD has a "-" at the fourth character, show the first 6 characters in Document Number.

You may re-use common formulas in Calculated Columns by sharing a calculation. Other users can quickly and easily bring the shared calculation into their queries and adjust the formula in their query as required.

You can also control whether a calculated column is displayed in the query results. You set it by choosing the green/red bubble in the Calculated Column window. It will toggle between green (on) and red (off).

For example, you may have a calculated column that returns an individual’s tenure in days, then a second calculated column that converts the days into years. In this case, you do not wish to display the calculated column that is the number of days.

Exercise: Concatenate Text fields in a Query

Business Example

In this exercise, you will create a full name calculated column by concatenating first and last name text fields to add to you Employee Information query.

Exercise: Add a Field with an IF/THEN/ELSE statement

Business Example

In this exercise, you will identify which employees have been identified as future leaders. We will use this information to determine the percentage of employees that are currently identified as future leaders.

Steps

  1. Add the Future Leader field to the query.

    1. Choose the Edit tab.

    2. Choose Employee Information table to display the fields available.

    3. Select the box to the left of the Future Leader.

    4. Collapse the table field list by selecting Employee Information again.

  2. On the query building screen, navigate to EditCalculated Columns.

  3. To open the Calculated Column Designer, choose +.

  4. Enter the name Future Leader.

  5. Select the Number data type from the dropdown menu.

  6. Choose Edit Format, change the Format to percentage, and choose OK.

  7. Set the Conditional Statement (Check if a statement is TRUE of FALSE) to ‘if future leader is Yes’.

    1. Drag and drop the Conditional Statement ( ) item into the Value box.

    2. Under Employee ProfileEmployee Information, add Future Leader to the IF field. (Approximately the 45th field down in the table).

    3. Drag the Equal to conditional operator ( ) to and drop it in the IF statement.

    4. Drag the Token Editor ( ) and drop it in the IF statement.

    5. Enter Yes in the text field then validate by choosing OK.

  8. Set the value if the condition is TRUE to a value of 1.

    1. Choose the Value box located next to the THEN statement.

    2. Drag the Token Editor ( ) and drop it in the Value box that you have just selected.

    3. Select Number.

    4. Select the 0 next to Enter Number.

    5. In Enter Number, type 1.

    6. Choose OK.

  9. Set the value if the condition is FALSE to a value of 0.

    1. Select the Value box located next to the ELSE statement.

    2. Drag the Token Editor ( ) and drop it in the Value box that you have just selected.

    3. Select Number.

    4. Verify the number is 0.

    5. Choose OK.

  10. Notice all the box borders are green to validate the expression. Choose OK.

  11. Choose Done.

  12. Verify your Calculated Column is working correctly and remove the future leader with yes/no values.

    1. Verify employees with the value of Yes have 100% in the last column.

    2. Choose Edit on the left panel.

    3. Expand Employee Information (>>).

    4. Deselect Future Leader.

    5. Hide Employee Information (<<).

  13. Save your query by navigating to FileSave.

Scope Definitions: People or Records

To define the scope of user data returned in the query,select People Scope from the Edit drop-down menu.

People scope is only applicable when reporting on user data.

Define Scope of People

People Scope allows you to customize your query to a select group of people. You can define the scope by people, division, department, or location. By default each query starts with the Team view (direct reports) of the Logged in user.

If the user does not have any direct reports then the query results will be empty. You need to change the People Scope in order to see query results.

Hint

If you are a report designer and do not have any direct reports, you must change the default configuration while building your query, otherwise you will not return any data. You can change it when the query is finalized

This feature allows queries to return results that include the person running the report (if set to logged in user) or include the user the report is "starting from". First select the Reporting Type you would like to customize the query around from the People Scope window. Then, select Logged in User from the Define Team window.

Note
Like to the feature in Table Report Builder, you can search for another user when setting the People Scope of a report to run the report as that user.

User Prompted People Scope

You can also make the People Scope user prompted when running the report. To do this, you need to select the checkbox.

Exercise: Use People Scope in a Query

Business Example

In this exercise, you will customize a query to direct reports of logged in users in your compensation planning query.

Static and Runtime Filters

Introduction

Filters allow you to limit the data you view in a report or query. In detailed reporting queries with live data source, there are two types of filters you can set:

  • Static Filter: Filters rows based on a value of a specified field. When it is added, it is permanently applied to a query.
  • Runtime (user prompted) Filter: Filters rows based on the field(s) selected, allowing report consumers to choose the filtered value for fields.

For example, suppose your report needs to reflect only the Sales department. You could go back to the query and apply a static filter on all rows from the Sales department. Alternatively, you can set up a runtime filter in the Department field, allowing report consumers to choose Sales or Marketing before running the report.

Reports with runtime (user prompted) filters, people scope, and/or date options, will prompt the user to make filter selections prior to displaying results. The user will see a pop-up on top of the report page with filter criteria before seeing report results.

Note: If the report does not have any runtime filters, people scope, or date options enabled, there will be no prompt for the report consumers.

Add a Static Filter

To add a static filter on the query building screen, navigate to Applied Filters on the left side of the screen and choose Manage Filters. You can then define the filter field, operator, and value.

With the value you can enter a value, select a dynamic variable or select a report value from the database for that specific field.

Filter Search Values

When setting up a filter in Canvas, you may enter any part of the value and the Filter Designer will permit you to select the desired value from the list of results.

Filter Groups

It is possible to add multiple filters to a query. These filters can be grouped using the Filter Designer. When adding multiple filters, you can toggle between the AND / OR clause by clicking on the AND / OR statement. By doing this, you can create Filter groups:

Runtime Filters

To add a runtime filter on a query building screen, complete the following steps:

  1. Choose EditManage Runtime Filters.
  2. From the Available Columns list, select the fields which will be used for filtering.
    Note
    If you are using pivot charts or tables with list reports that have a runtime filter, be sure to create the pivot charts or tables after adding the runtime filter. When you create a pivot chart, it uses data that existed at the time of creation. If you add the runtime filters after creating a report, return to Page Designer, edit the component to make modifications to the report, and re-pivot the data using the Pivot Query Designer. When you apply the runtime filter, data reflected in the pivot chart will also be filtered.

Exercise: Add a Static Filter to a Query

Business Example

In this exercise, you will add a static filter to your employee information query to hide employees new to a position.

Exercise: Add a Runtime Filter

Business Example

In this exercise, you will add a runtime filter for selection at the launch of a report.

Note
You will not see the impact of the Runtime Filter. In this exercise, you will add a runtime filter for selection at launch of a report when you include a query in a report using Report Center.

Exercise: Work with Filters

Business Example

In this exercise, you will work with filters. You will customize a query to include 2 levels below the logged in user in your Employee information query. You will add a static filter and a runtime filter.

Data Sets

Define Data Sets

Depending on the reporting schema/domain you select you will have the option to define Data Sets. If you select a field from a domain that supports Data sets you will have the option on the left side of your screen to define the data sets to be used in your query:

Data Sets can be defined with reporting domains that use forms.

 Within the configuration you can set the set of Form Templates you would like to include in your query:

Column Sorting

Columns in a query can be sorted by choosing EditSorting. You can rearrange the order of the columns by choosing on the column name in Available Columns and then using the up and down arrows to set the sorting order of the columns. You can toggle between ascending and descending by selecting the blue button.

Hide Duplicate Rows

You can also hide duplicate rows by choosing EditHide Duplicate. When selected, duplicate rows will automatically be hidden from view. You can toggle between showing and hiding duplicate rows by selecting the checkmark next to Hide Duplicate Rows on the menu.

Note

Hiding rows is a feature that is not available when using the replicated data source.

Column Formatting

When adding columns to the query it is possible to change the format of specific fields used in it. You can change the format of the following:

  • Date type fields (switch between Date or Date / TimeStamp)
  • Text type fields that are using picklist values (switch between the Value or the ID)

You can change the format by navigating to EditColumn Formatting.

Note

Column Formatting is only applicable to Detailed Reporting using live data.

Save Queries

As you make changes to the query by adding data, static filters, and People Scope, you can save the query at key steps. To save the query, choose FileSave.

When you save your changes, if you make unwanted changes to the query, you can revert to the previous save point by choosing FileCancel Changes.

If you want to use the query in another report, you can export (save) the query in the query list. In the list component formatting page, choose the export button and provide a name for the query. The query will appear in the query list when adding a new list report component.

Exercise: Sort Rows in a Query

Business Example

In this exercise, you will sort your Employee Information query by ascending name.

The Addition of Fields from Different Modules

The cross-domain reporting feature allows you to add columns from different modules, and the query will auto-link the data. You can also use the Pivot Query Designer to aggregate fields across different modules after setting up the query; For example, a list report can display results from three different modules:

  • SAP SuccessFactors HXM module: User Sys ID, First Name, Last Name, Hire Date, and Job Code
  • Compensation module: Current Salary
  • Performance module: Overall Performance Rating Description

The Detailed Reporting tool helps you identify which fields are available for cross-domain reporting. Once you add a field to the query, unavailable domain, and field names under the Add tab turn grey, indicating that they cannot be joined to the working query.

When you have data coming into a query from more than one module, you will have access to Relationship Scope, an option on the Edit menu which tells you how the data is joined. The Compensation Planning User ID is joined with an inner join to Employee Profile User Sys ID. If you need to change the joined field, use either dropdown menu.

Note

Relationship Scope is a feature that is not available when using the replicated data source. Joining data from multiple modules is handled in a different fashion. For more information, review the appropriate lesson.

Exercise: Join Data from Different Modules into a Single Query

Business Example

In this exercise, you will add fields from other modules (Employee Profile and Performance Management) to your compensation planning query.

LMS Reporting Schema and People Scope

The SAP SuccessFactors Learning Management System is an external system to the SuccessFactors HXM platform. It is possible to report on data of LMS due to APIs.

In the LMS reporting schema there are "User" tables and "Admin" tables. The admin tables can be found in the categories that are not pre-fixed by "User". The "User" table can be found in the categories that are pre-fixed by "User".

The difference between the "Admin" and "User" tables is that the "Admin" tables will return all data and the "User" tables will only return data from users that the LMS user is allowed to view based on his permissions within the LMS system.

Note

Not all LMS, fields are available for reporting. For an overview of which fields are reportable and which fields can be used as a filter you can download 'Learning Data in Online Report Designer' from the SAP Help Portal.

LMS Admin

To create a query in a canvas report on LMS data, you will need an (Admin) user in the LMS system with the same user ID as the SAP SuccessFactors user ID. That (Admin) user needs to have a role assigned that has access to one or more ad hoc Reporting Permissions.

In the LMS it is possible to create security domains for a role that will limit the target population for a user. These security domain groups can be set for detailed reporting as well by applying the permission restrictions. Restrictions can be set on Security Domain Group ID's and on Status restrictions.

Date Ranges

Due to the large volume of data in an LMS system there is a limit of data returned for a query. When you are running a report on LMS data, there will always be a Runtime Filter pop-up where it is possible to select a date range. By default the Date range is set to 36 months. This default value can be changed in the LMS Configuration.

Log in to track your progress & complete quizzes