Working with Filters in Query Designer

Applying filter is a good option to limit the data viewed without altering the design of the underlying object. Filter allows you to see only the data you want displayed.

Query Designer offers various filters that can be applied to the objects and query:
  • Object Filters: Simple Filter and Advanced Filter.

  • Query Filters: Advanced Filter

Applying Simple Filter to Your Query

Context

Use Simple Filter on an object to limit the records from one or more of its selected columns.

Procedure

  1. Select the object of your choice.

    A context menu appears.

  2. Select Add filters and choose Simple Filter.

    The Add Filter dialog appears.

  3. In the Search field select the required filter.

    The Select Members dialog opens.

  4. Under Available Members select your members and click OK.

Results

Simple filters are applied to your query.

Configuring Date Range in Time Filter

You can retrieve data from a selected schema table for a defined duration by configuring and applying the Date Range option of time filter.

Context

This configuration applies when you use a Time Filter with the Date Range option. To get to the date range configuration dialog, select a schema table on the query canvas, click , (remove existing filters, if any), and select Start of the navigation pathTime Filter Next navigation step Date RangeEnd of the navigation path. The Set Date Range dialog appears.

Procedure

  1. Go to the Range Definition section, select <Range Type> as 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"; whereas if you select granularity as Year, in Range Period you can select "This Year", "Last Week" or "Next Week".

  4. In the Range Extension section, widen the date range by adding instances 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 one of the following options in Range Parameters to further refine the 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. Optional: Check Range Overview, the topmost section of section of the Set Date Range dialog to review the date range you've defined.
  7. Go to the Records Returned section, and select one of the following options to define the impact of the date range while filtering the records.
    • 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.

  8. Click OK.

Results

You have successfully configured the date range of a time filter and applied it to the selected table.

Advanced Filter

Advanced filtering allows you to filter multiple data fields by defining a set of logical conditions. To apply advanced filter, select the object of your choice. From the contexual menu, select Start of the navigation pathAdd Filter Next navigation step Advanced Filter Next navigation step Set Conditions for Advanced FilterEnd of the navigation path. Add new conditions from the button, choose either dimensions or operators to create nested filters.

Applying an Advanced Filter

Advanced filtering allows you to filter multiple data fields by defining a set of logical conditions.

Context

Advanced filtering mainly uses AND or OR conditions to filter the data fields. These conditions can be set to Include or Exclude the data that satisfies the filter conditions.

Add new conditions using the "+" button, and choose either fields or operators to create nested filters.

Currently, advanced filtering can be used on both queries and stories. You can apply an advanced filter for an entire story page or on individual widgets. The option for advanced filters is enabled only after you add at least one table to the query canvas or one chart to the story page.

Procedure

  1. Click , and select Advanced Filter.

    The Set Conditions for Advanced Filter dialog appears.

  2. Enter a Name for the filter.
  3. 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 of the following conditions are met.
  4. Click the "+" button, and select either a Data Table to create a condition or an Operator to create nested filters.
  5. After you select a Data Table to create a condition, select a Column from the table.
  6. Select an operator to filter data from the selected column.
    • 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.

  7. Select filter type to complete the condition.
    Filter Type Allows you to...

    Free Text

    Enter a filter value you want to apply.

    Member Selector

    Choose field values from the selected column as your filter.

    Column

    Choose another column as your filter.

    Input Parameter

    Select a parameter as your filter, if you've created input parameters. For more information, see the "Creating Input Parameters for Advanced Filtering" topic.

  8. Repeat steps four through six to add more operators and conditions, if needed.
  9. Optional: On the AND or OR operator, use option to:
    • Allow or prevent viewers from disabling the condition.

    • Collapse child conditions.

    • Delete () the operator.

  10. After you have configured the conditions, click OK to apply the advanced filter.

Results

The advanced filter has been applied successfully.

Creating Input Parameters for Advanced Filtering

Make your data filtering more flexible using parameters in advanced filters.

Context

Parameters enable you to update your filter easily. Using Edit Prompts in Story, you can prompt users to provide filter inputs during story execution.

Procedure

  1. In Report Center, create a new Story report, or edit an existing Story report and go to Edit Datasource.

    The Query Designer page appears.

  2. Select {} (Input Parameter)

    The Input Parameter dialog box appears.

  3. Select to add an input parameter.
  4. Enter a suitable Input Parameter Name.
  5. Select Type of input parameter.
    While you can set the data type to either String, Numeric, Decimal, Integer, Date or Datetime, most of the fields in SAP SuccessFactors products are of type String, Decimal, and Datetime.

    The Column Values parameter type allows you to define a set of specific member values as the input parameter. In the input parameter definition, select a column for the list of values. At runtime, users can open the member selector for the selected column to choose values directly, without having to type them manually.

    Enter the ID of the member as the default value. To find the exact ID of a member, create a simple filter on the corresponding column table using Member Selector as the operand. In the member selector, go to Settings > Member Labels and select Description + ID to view the member ID.

  6. Select a column from the Selected Column.
  7. Enter Default Value for the input parameter.
  8. Select OK.

Results

You've successfully created an input parameter that you can use in advanced filters.