Applying Filters in Query Designer

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

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

Use Simple Filter on a table to limit the records from one or more of its selected columns. You can define a simple filter to display data for specific column values in a table. You can apply a simple filter on one column at a time, but you can apply multiple simple filters to a table.

For example, the above figure, Table Simple Filters, displays the simple filter screen for the Job Relationships table. To only return HR Managers, select the Relationship Type field, the add the member (value) HR Manager to the filter. To create a simple filter, complete the following steps.

1. Select the table to apply the simple filter.
2. In the table action menu, choose the filter icon and select Simple Filter. 
3. From the list of columns that you see, select a column for your simple filter.
4. From the Available Members section, select the values you want to include in your filter definition. 
5. Choose OK.

If you want to retrieve data on a specific date OR over a date range, you can use the time filter on a table while building a query. Time filters are applicable for Employee Central effective dated tables and the tables connected to Employee Central schemas. For example, you might want to return an employee's current job information and their base salary at the beginning of the year.

You could use the Job Information table with a time filter of today to retrieve the current job information. You could use a simple filter on the Compensation table, pay component field to retrieve only the base salary. Finally set the time filter on the compensation table to retrieve the active record on the first day of the year.Note: The default filter for the first table (driving table) is today, but prompt user at runtime.

The default for other tables is to use the same as the first table. To set a new time filter to a single date (As of Date…):

1. Select the table to apply the time filter.
2. In the action menu, choose the filter icon.
3. Select Time Filter.

   A popup to manage time filter appears.

4. If a time filter is already applied, choose Remove Filter.
5. After the today filter option is removed, select As of Date.
6. Select either a Fixed Date or Dynamic Date.

   a)  For fixed date, select the date with the date picker.

   b)  For a dynamic date, select the appropriate date from the dropdown list.

7. Choose OK.

To set a new time filter to a date range, complete the following steps.

1. Select the table to apply the time filter. 
2. In the action menu, select the filter icon.
3. Select Time Filter.

   A popup to manage time filter appears.

4. If a time filter is already applied, choose Remove Filter.
5. After the today filter option is removed, select Date Range.
6. Configure the Set Date Range dialog box as required.

   It is described below.

7. Choose OK.

When a filter is in place on a table, you can edit or remove the filter. In the action menu, select the filter icon and choose the Edit Filter or Remove Filter option.

We will now look at how to configure a Dynamic Date Range dialog.

1. Set the Range Type to Dynamic.
2. Select the Granularity of the date range to Day, Week, Month, Quarter, or Year.
3. Set the Range Period to current, previous, or future instance of the selected granularity.

   For example, if you select Day as the granularity of the date range in Range Period, you can select Today, Yesterday, or Tomorrow; in contrast, if you select the granularity as Year, in Range Period you can select This Year, Last Year, or Next Year.

4. In the Range Extension section, widen the date range by adding values of selected granularity to the Start and Finish values of the date range.

   For example, if you select Day as the granularity and Today as the range period, then your selected date range is set to the current day. However, if you set Start and Finish values to 1, your date range extends to three days, which starts from the previous day and ends on the next day.

5. Select the Range Parameters option to further refine the dynamic date range.From the first day until the last day of the defined range: This option sets the date range to the entire duration defined using the Granularity, Range Period, and Range Extension fields.Period to Date - From beginning of defined range until today: This option respects the start date defined using the Granularity, Range Period, and Range Extension fields, but it sets the end date of the date range to the current day.Period to Go - From today until the end of defined range: This option sets the start date of the date range to the current day, but it respects the end date defined using the Granularity, Range Period, and Range Extension fields.
6. Set the Records Returned option:
   • Include any records that partially or completely overlap your defined date range: Returns records from within the date range, and records with a date range that overlaps the defined date range
   • Include only records that begin during your defined date range: Returns only those records where the start date of the record lies within the defined date range

   • Example 1: if you want to return the 'quarter to date' records that started in the current quarter (such as to count hires or terminations), you would configure the dynamic date filter as the following:

   • Range Type: Dynamic
   • Granularity: Quarter
   • Range Period: This Quarter
   • Start/Finish: 0
   • Range Parameters: Period to Date
   • Records Returned: Include only records that begin in your defined date range

   • Example 2: if you want to return the salary records for a collection of individuals for the last 3 years, not including the current year, you configure the dynamic date filter as:

   • Range Type: Dynamic
   • Granularity: Year
   • Range Period: Last Year
   • Start/Finish: 2 / 0
   • Range Parameters: From the first date until the last day of the defined range
   • Records Returned: Include any records that partially or completely overlap your defined date range

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 table filters to only return records that have a status of active and have a hire date value.

1. Select the table to apply the advanced filter.
2. In the action menu, choose the filter icon.
3. Select Advanced Filter. The Set Conditions for Advanced Filter dialog appears. 
4. Enter a name for the filter. 
5. Under Conditions, select either OR to execute the filter when any of the following conditions are met, or AND to execute the filter when all the following conditions are met. 
6. Choose the + button and select either a Data Table to create a condition or an Operator to create nested filters.
7. After you select a Data Table to create a condition, select a Column from the table. 
8. Select an operator type corresponding to the selected column, and then select and configure the corresponding filter type to complete the condition. 
9. Optional: On the AND or OR operator, use  option to the following:

   a) Allow or prevent viewers from disabling the condition.

   b) Collapse child conditions.

   c) Delete the operator.

10. Choose OK to apply the advanced filter.

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, or 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 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 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 SAP SuccessFactors Learning data model has the following scope filtering on supported learning schemas:

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

Consider the following Scenario. Reilly Francis is the direct manager of four individuals. Each of those employees also have direct reports. Additionally, all of Reilly Francis' direct and indirect reports have William Muller as their HR manager.

A report designer creates a report. With the output unfiltered, the results return all employees and their respective managers and HR managers:

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

If Reilly Francis executes the report, only four 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 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 five 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. It's 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.