Creating a Data Source

The Query Designer enables you to select the fields you want to use to create reports using the 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 sources 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 four 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 create input parameters for the query.
• Validation- Provide 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 fields.
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 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,Available Data (Schema) in Story Reports | SAP Help Portal , 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 either from the Available Data section or through the table action menu. One benefit of the Available Data is that 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 using 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 columns.

5. The table is added to the canvas.

Currently, your query can include only up to 120 columns, which are selected from a maximum of 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.

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 Outer Joins show all records from the object on the left and only records that match from the object on the right. In this 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.