Creating Queries with Detailed Reporting on Live Data

 To launch Report Center, complete the following steps.

1. Log in to SAP SuccessFactors.
2. Navigate to Home → Reporting.
3. The Report Center screen opens.

   Reports will be listed as the type, Canvas.

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 (Home → Report Center → Report name) in the top-left corner.

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.

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

Page Designer also allows you to manage the pages within a report, which includes:

• Adding Pages (new pages or pages that exist in another report)
• Renaming Pages
• Reordering Pages
• Deleting Pages (orphaned pages or pages from the current report)

To add a new page in Page Designer:

1. In Report Center, edit the report to create a new page.

2. Using the Page menu, select one of the following:

   • New Page to add a new, blank page with an auto-generated name to the report.

   • Add Existing Page to select to add either a page from another report or an orphaned page that is not assigned to any report.

     Note

     You can delete existing orphaned pages from the Add Existing Page dialog box.

   The new or selected page loads in Page Designer.

To change the name of pages in Page Designer:

1. In Page Designer, click the pencil icon.

2. Type in the name of the new page.

3. Click OK.

To change the order of pages in Page Designer:

1. In Page Designer, click the reorder icon.

2. Use the up and down arrows to update the page order.

3. Click OK.

To delete a page in Page Designer.

1. In the Page menu, select Delete.
2. Confirm the deletion by clicking OK.

When creating or editing a page, the page properties panel becomes available. You can use the panel to access several options:

• Edit page properties allows you to:

  • Name the page

  • Set page orientation

  • Configure page margins

  • Configure a page (canvas) size.

• Edit designer properties allows you to:

  • Configure a background grid to assist with the layout of components

  • Manage overlapping components

• Validate Page checks the pages for errors. allows you to:
• Grid Options allows you to configure quickly some of the same grid options that were available in Edit designer properties.

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.

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.

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

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 Edit → Calculated 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

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.

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")

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.

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.

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.

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.

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

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.

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.

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

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.

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.

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.

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:

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:

Columns in a query can be sorted by choosing Edit → Sorting. 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.

You can also hide duplicate rows by choosing Edit → Hide 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.

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 Edit → Column Formatting.

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 File → Save.

When you save your changes, if you make unwanted changes to the query, you can revert to the previous save point by choosing File → Cancel 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.

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 HCM 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.

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.

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.