Creating Queries for Story Reports

The Query Designer enables you select the fields you want to use to create reports using Story. The queries you build using the Query Designer are stored within the Stories and cannot be accessed outside the Story. They are saved as data source(s) for your Story.

The query designer has a toolbar with a File section and a Data Section. Tools on this toolbar affect the entire query. The 4 tools available are as follows:

• Save - Save the Story Report
• Create Filter - Show/Hide the query filter bar, covered in a later section
• Create Calculated Column - launch the Column Overview dialog used to manage selected columns and calculated columns
• Create Input Parameter - launch the Input Parameter dialog used to crate input parameters for the query
• Validation- provides warnings for transferred reports whose data source does not pass validation checks

After adding a table to the canvas, a table can be selected to display the table's action menu. The action menu tools available are as follows:

• Copy: Copy a related table.
• Show related tables: Shows the tables joined to the selected table
• Select columns: Enables you to select columns for the query
• Add filters: Enables you to add filters on your column selection
• Remove table: Removes the selected table from the canvas of the query designer

The use of these tools will be covered in later sections.

When you have added a table, select the individual fields by checking the appropriate boxes next to the fields to include.

To add fields from a single table, complete the following steps:

1. Expand the schema you are using in the report.
2. Locate the appropriate table.
3. Drag the table onto the canvas.
4. Choose the table in the selected data area. An action menu will appear.
5. Choose the Select Columns button.
6. Select the fields you wish to include in the query.
7. Choose outside the Select Column pane to close the window.

Alternatively, you can expand the tables in the Available Data section and double-click the fields (columns) you want to select. To confirm whether the field has been selected, check the color of the field name in the Available Data section. The color of a selected field appears blue.

When you are adding fields to your query, you might see some indicators/call-outs on the fields, in particular:

• Lock icon: It means you do not have the role-based permissions required to access the data in that field. You will still be able to select the field in the query; however, you will not see any data in that field. When other users run the report, they see data based on their role-based permissions. To resolve the constraint, check and update the data access permissions granted to you for the particular fields in that module.
• Shield icon: It means that the field includes sensitive information. If you add sensitive columns to the report, the read access logs are generated each time someone uses (previews, schedules or generates) the report. You must also include the context field (such as User Sys ID) to the report to enable the read access logging.

In many cases, tables may have fields with similar names.

Under certain circumstances, you may want to change the label of a field. For example, you may select the Last Name column from two different tables such as Basic User Information and Manager.

In that case, relabel the field to distinguish between the two fields, such as using Manager’s Last Name.

To relabel a field, complete the following steps.

1. In the Data section of the toolbar, choose the Calculated Column button.
2. In the column overview dialog, update the Column Description for the field(s).
3. Choose OK.

If your organization uses SAPSuccessFactors Learning, you can use Stories to report on the learning data. However, this will add an additional step to when creating a new Data Source.

For organizations with SAP SuccessFactors Learning and Stories, when you create a new data source, you will be prompted to choose a connection to the appropriate data model. The data model defines what schemas, tables, and fields can be used within the query:

• SAP SuccessFactors Reporting (SAPSFSFREP): All schemas that are NOT from SAP SuccessFactors Learning
• SAP SuccessFactors Learning (SAPSFSFLMS): ONLY the SAP SuccessFactors Learning Schemas

The process to create the data source using learning data is the same, only the schemas, tables, and fields are different.

To learn about the Learning schemas supported for Story reports, refer to the Available Data (Schema) in Story Reports topic in the document, Stories in People Analytics, on the SAP Help Portal.

If the configuration of the SAP SuccessFactors system changes, or if you import report definitions from a different system, you can end up with reports with Data Sources that are invalid. Therefore, SuccessFactors has integrated a query validation tool into the Query Designer.

When you preview or finalize a query, it triggers some validation checks that help you identify and fix issues in the query, if any exist.

The query validation checks help you to identify the following:

• Missing Columns (fields): The validation checks identify the fields used in the query (selected columns, filters, and/or calculations) but are unavailable in the system. To resolve the issue, you can either replace the missing field with a valid option or you can delete the missing field.
• Missing Objects (tables): The validation checks identify the tables used in the query (its fields are included in selected columns, filters, and/or calculations) but are unavailable in the system. To resolve the issue, remove the missing table from your query and remove the fields of the missing table from filters and/or calculations.
• Missing Permission (Metadata): When a user edits a query with filters, if the user doesn't have data access for any of the fields used in the filters, the query breaks. This validation check identifies such invalid filters that break the query. To resolve the issue, you can remove the invalid filters and save your changes to the query.

When there are missing columns, the steps to resolve the issue vary depending on where the field is used:

• If a missing field has been selected as a column in the query, the system provides options to either update or remove the field to fix the issue.
• If a missing field has been in a filter or in a calculation, the system provides details of the missing field and of the entity in which the field has been used. You must manually update the entity to fix the issue.

For most reports, you will need to use more than one table to retrieve the required data.

Tables in the reporting schema can be related to other tables, either within the same schema or in a different schema. After you have added a table to canvas, only the tables joined or related to that table appear in the Available Data section.

You can add related tables from either from the Available Data section or through the table action menu. One benefit of the Available Data is it has a search option.

To add a related table or field from Available Data, complete the following steps:

1. Navigate or locate the appropriate table / field in the Available Data section.
2. Double-click it or drag-and-drop the table/field on the query canvas.
3. You can add additional fields using the same method or the Select Columns command in the table action menu.

You can also add related tables using the Show Related Tables command from the table action menu. The related schemas and table will appear to the right of the selected tables. Schemas appear as collapsible/expandable Labels, such as 360 Review.

1. Choose the table in the selected data area.

   An action menu will appear.

2. Select the Show Related Tables button.
3. Select the > to expand the schema to show the related tables available.
4. To add a table, either:

   a. Choose the + icon to add the table without any fields

   b. Click into the table, then use the Select Columns action menu command to select the column(s)

5. The table is added to the canvas.

Currently, your query can include only up to 120 columns, which are selected from maximum 30 tables. Your query can retrieve data up to 1,000,000 cells. However, please ensure that the total number of columns from all queries doesn't exceed 180.

After a related table has been added to the canvas, you can adjust the join type between the two tables. Each related table will have a default, which can be overridden by selecting the table join icon.

A calculated column creates a new output column and calculates its values at runtime based on the result of an expression. For example, if you are creating a query on employees' Job Information and you want to report the duration of their employment, you can create a calculated column, Job Tenure, which would show the years between the current date and the employee hire date.

To create a calculated column, complete the following steps.

1. Navigate to the Query Designer.
2. Under the Data section, select the Create Calculated Column icon.

   The Column Overview dialog box appears.

3. Change to the Calculated Columns tab.
4. Choose the Add Calculated Column link.

   The Calculated Columns dialog box appears.

5. Enter a column ID and enter the name of the calculated column in Description.
6. In the Edit Formula section, create an expression using the available Functions, Conditions and Operators, and choose Format to verify whether the expression is correct.

   This will be described in more detail with examples in the following section.

7. Choose OK to add the calculated column to the list of columns in your report.

   The column appears in the Calculated Columns section of the Column Overview dialog box.

8. If you do NOT want to display the calculated column in the query output, turn off the Show in Grid switch.
9. Choose OK.

Expressions define a calculated column. Calculated columns are built as formulas. Formulas are comprised of fields, constants, functions, conditions, and operators. You can type formulas manually or use the formula editor if you're not sure how to construct the formula you want.

• Fields: Fields appear in formulas with the syntax as [Schema#Table#Field]. You can select fields in the query by typing the description of the field
• Values: add text in " ", add numbers as values, enter dates as #YYYY-MM-DD#
• Functions, conditions, and operators: Many will appear as FUNCTION() or as a single identifier such as >

When you are constructing expressions, note the following:

As you begin to type, a hint list shows all available options (including formula and fields) that match the text you've typed. The list shows values for both Field IDs and Field descriptions.

Instead of typing formulas manually, you can do the following:

• Press CTRL + SPACE (on your keyboard) to choose from a list of values that are valid for that location in the formula
• Type [ for a list of fields.
• If you select a formula from the hint list, a template is automatically entered in the formula entry bar.
• A short help text description is displayed when you move the mouse pointer over the template.

A concatenation is the joining of two strings, in this case, text fields.

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

Example Inputs: First Name field (Kenneth), SPACE, Last Name field (Roden)

Example Output: Kenneth Roden

To concatenate fields, you can use the CONCAT() function

Example Expression: CONCAT( [User#Basic User Information#First Name], CONCAT( " ", [User#Basic User Information#Last Name] ) )

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 Condition Editor with the appropriate operators. For example, you can create a formula that calculates the difference in New Salary and Current Salary:

• Example Inputs: New Salary (170,000) , Current Salary (165,000)
• Example Output: 5,000

To calculate values, you can use operators (+ - * /) as well as some functions such as POWER(), ROUND()

• [Compensation (Employee Central)#Compensation Information#New Salary] -

• [Compensation (Employee Central)#Compensation Information#Current Salary]

Date formulas work with fields that store date values. A variety of functions exist to work with dates.

For example, you can calculate the number of days since the current hire date:

• Example Inputs: CurrentDate () , Hire Date, 2018-04-01
• Example Output: 418

Many functions work with date fields, such as DAYS_BETWEEN(), YEAR(), ADDDAYTODATE(),

Example Expression: DAYS_BETWEEN([User#Basic User Information#Hire Date], CURRENTDATE() )

The IF() function compares two or more sets of data and test the results. If the results are true, the second parameter instructions are taken; if not, the third parameter instructions are taken. IFs can be nested to include more than 2 possible outcomes.

For example, you may want to substitute the value stored in the gender field with a different label for each record:

• Example Inputs: Gender (M)
• Example Output: Male

Use one or more IF functions to build conditional logic.

Example Expression: IF([User#Basic User Information#Gender]="M","Male" ,"Female")

IF() can also be used to get counts (total number of items) and ratios (percentages).

For example, may want to include the number of active employees in a division. In this circumstance, we can include a column that will display a value of 1 for active employees, and a value of 0 for inactive employees. During the display of the data, we can sum the column for the total count, or also display for each division.

• Example Inputs: Status (Active User)
• Example Output: 1

Use one or more IF functions to build conditional logic.

Example Expression: IF([User#Basic User Information#Status_Description]="Active User" ,1 ,0 )

You can apply filters to refine the data records retrieved using a query. Applying a filter is a good option to limit the data in a view without altering the design of the underlying object. It helps you to see only the data that you want displayed. You can apply multiple filters, while building queries and while designing the Story Report.

With Query Designer for Stories, you have Table filters that are applied to individual tables within a query, and Query Filters that are applied to the entire query. Various filter types that can be applied to the tables and to the query:

• Query Filters: Scope Filter and Advanced Filter
• Table Filters: Simple Filter, Time Filter, and Advanced Filter

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

For example, the Compensation table stores entries for different pay components for an employee. Therefore, there can be multiple records in the table for each employee. If you wish to only retrieve the Base Salary, you can create a table filter to limit the return of records on that table to only show Base Salary.

Advanced Filtering allows you to filter multiple dimensions by defining a set of logical conditions. The dimensions used in Advanced Filtering can be filtered by using AND or OR conditions. These conditions can be set to Include or Exclude the data that satisfies the filter conditions.

You will add a table filters to only return records that have a status of active and have a hire date value.

Consider the following when working with operators and data types on your column filters:

• For all columns, you can apply Equal to, Except, Is null, or Is not null as the operator.
• For String type columns, you can additionally apply Like.
• For Numeric, Integer, Decimal, Date and Datetime types of columns, you can additionally apply Greater than, Greater than or equal to, Less than, Less than or equal to, or Between.

For example, if you need to return the employees that are full time and have the employment status active or on leave, then you need to combine two expressions to create the filter.

Query filters allow filtering across all tables in the query. This can be useful to control the overall result set using criteria from multiple tables. For example, if you want to include employees with a status of active or paid leave, but only for those employed by a legal entity in the United States, then you could set the filter for employment status in Job Information table and for the related country field of the Legal Entity table.

The Scope Filter allows you to decide the scope of the report in terms of the people you want to include in the report. The scope filter is a query filter, which applies to the primary schema table in the query.

The scope filtering functionality is available only if the schema table selected on the query canvas supports it, else the Scope Filtering option appears gray.

To create a scope filter, complete the following steps.

1. In the Data section above the canvas, choose the filter icon, and select the blue filter plus icon to choose a query filter.
2. Choose the Scope Filtering option and select the table for which you are applying the scope filter.

   The Scope Filters dialog appears.

3. In the Start With list, define the starting point for the scope of the report. You can either select the default value of All Data Included or select a user role from where you begin filtering the report data, using terms like Manager, Human Resource, Matrix Manager, Custom Manager, and Second Manager. 
4. If you select a user role in Start With, in Up to, select the hierarchy levels under the selected user role to be included in the report:

   All Levels: Selects all direct and indirect reports of the Start With user role.

   Direct Reports: Selects only the direct reports of the Start With user role.

   Levels: Allows you to define the hierarchy levels under the Start With user role that you want to include in the report.

5. In the Who should be the subject of this report? section, select the employee with whom the scope of the filter begins.
6. In the Who should this report include from this domain? section, use the options to further refine the selection of employees you want to include in the report:

   Include inactive users in the data: Select this option to include inactive employees within the scope of the subject of this report. 

   Include user running the report in the data: Select this option to include the data of the employee running this report, even if the employee is not within the scope of the subject of the report.

7. Choose OK.

For example, to create a report that includes the direct reports of the logged in user, you would select: Start with Manager …Up to Direct Reports … Who should be the subject of this report? Logged in User … Who should this report include from this domain? None checked.

For example, to create a report that includes all HR reports of the HR Manager Ann Smith, including inactive employees, you would select: Start with Human Resource … Who should be the subject of this report? Ann Smith … Who should this report include from this domain? Include Inactive Users checked.

The SAPSuccessFactors Learning data model has the following scope filtering on supported learning schemas:

• User
• HRBP
• Supervisor (including levels)
• Instructor
• Administrator

The report designer now applies a scope filter to include a manager, who is logged on user's direct reports:

If Reilly Francis executes the report, only 4 records are returned - that is, the direct reports.

If Carla Grante executes the same report, Carla's 5 direct reports would be displayed:

Now the report designer now applies a scope filter to include the same setting EXCEPT to change the Up to into All Levels:

If Reilly Francis executes the updated report, 21 records are returned- the direct reports and indirect reports.

If Carla Grante executes the updated report, Carla's 5 direct reports would still be displayed as she does not have any indirect reports:

Finally the report designer now applies a scope filter to return HR reports for an HR Manager. Its set for the current logged in user:

If Reilly Francis OR Carla Grante execute this final revision of the report- Zero (0) records would be returned as they are not the HR manager of any employees.

If William Muller were to execute the report, it would return all employees that Muller is the HR manager. This would include employees in Francis's hierarchy- as well as employees of other managers:

You can make your data filtering more flexible using input parameters in advanced filters. Input parameters enable you to update your filter easily. Using Edit Prompts in Story, you can prompt users to provide filter inputs during story execution.

For example, you can prompt the selection of a gender at runtime on a diversity story.

To create the input parameter in Query Designer, complete the following steps.

To use Input Parameters for Advanced Filters, complete the following steps:

1. Select { } on the toolbar (Input Parameter).
2. To add an input parameter, select +.
3. Enter a suitable Input Parameter Name.
4. Select the Type (Data Type) of input parameter.
5. Enter Default Value for the input parameter.
6. Choose OK.

The schema joins in reporting enable you to navigate from one module schema to another, so you can include columns from multiple schemas in story reports. Default Joins are system-defined and cannot be altered.

The Report Schema Join Manager tool allows you to view default joins. All the default joins are available to all users who build story reports. On the Report Schema Join Manager tool, the predefined joins are available under the Default Join tab.

By default, all the standard MDF objects are available for reporting in Story. Custom MDF objects are also reportable by default, but they appear in the Available Data section of the Query Designer page only after you associate them with one or more schemas. You need to identify the schemas you want to link with the custom MDF object. You can also create a custom schema, if needed.

To add an Object to a Schema, complete the following steps:

1. Navigate to Manage Data.
2. In the Report Object Configuration Entity type, search for the custom MDF object.
3. Choose Take Action → Make Correction.
4. In the Sub-Domain Schema List section, select the schema(s) you want to link with the custom MDF object instance.
5. If you want the object to be selectable as the first table of a query, set Is Root to Yes.
6. Choose Save.
   Note

   If a record does not exist, create a record of the Report Object Configuration Entity type for the MDF object to make it reportable.

You can use Story Reports to retrieve data from custom blocks created using custom MDF objects in People Profile (Employee Profile). To achieve this, Administrators create a generic object with an externalCode of datatype user, and create a custom UI. They then utilize that UI in People Profile

Custom MDF objects configured as custom blocks in People Profile are accessible as related tables of Basic User Information in the User schema. If it is linked to multiple schemas, it appears as a related table for the Basic User Information table multiple times (once for each linked schema).

To make the custom MDF object auto-joined to the Basic User Information table, ensure that you link the object to at least one standard or custom schema as its root object.