Sorting, Filtering, Prompting, and Totaling

Overview

In this chapter, we will further configure your custom reports to display just the data you need by sorting and filtering it. We will also explore prompt configurations and define default prompt values. Prompts provide more flexibility for reports by allowing users to specify the criteria for the report each time it runs. And, we will configure totaling, grouping, and outlining.

Objectives

By the end of this chapter, you will be able to:

Sort and subsort the data on a report.

Filter the data on a report using filters and subfilters.

Configure prompts on a report definition.

Display and group subtotals and grand totals on a report.

Sorting

All custom reports other than Advanced reports sort output by the left-most column of the primary business object by default. Advanced reports without enabled web services sort report output by a Instance ID by default to improve report performance. To enable sorting by the left-most column in Advanced reports, select the Sort by First Accessible Column checkbox.

The Sort tab on all report definitions allows you to override the default behavior and control the presentation and grouping order of report data.

In the Sort and Group grid, you can specify which fields from the primary business object should determine the data sorting. The sort logic starts at the first field and processes each subsequent field sequentially. So, the first field determines the highest level sort, the second field determines the next level sort, and so on.

In the following example, the report sorts by Supervisory Organization and then by Full Name.

Example of sorting by Supervisory Organization, then by Full Name.

Sorting on simple field types performs better than sorting on object field types because object field types access additional data in the background. In the example above, we sort by Full Name instead of Worker. Full Name is a text field, so it will perform better than Worker, which is a self-referencing field.

Note
: You do not need to display fields on the report output that you use for sorting.

Sub Level Sorting

You can sort data from related business objects in addition to sorting data from the primary business object. In the Sub Level Sort section, specify which fields from a related business object should determine sort order for the related data. If you do not configure sub level sorting, related instances sort by the left-most column of the related business object by default. Related business objects and fields used for sub level sorting do not need to display on the report output.

In this example, the report sorts instances of the Expense Reports - All Statuses related business object by the Expense Report Total Amount field.

Screenshot of the Sub Level Sort section.

You can use the Add button to sort data from additional related business objects on your report.

Logical Sort Order

You can only sort most fields in Workday alphabetically (either ascending or descending). However, certain fields in Workday can use logical sort order (either ascending or descending). These fields have a logical predefined order. By default, if a field is able to sort logically, the system does so.

The following example shows the Period field in a financial report. This field has a logical sort order of January, February, March, etc.

Screenshot of a field with a logical sort order option.

Filtering

The Filter tab allows you to filter the primary business object. A filter consists of one or more filter conditions. Define each condition in a row of the filter grid. You can filter on any field from the primary business object or Global business object (global fields). Filter fields do not need to display on the report output. The image below shows the fields to define when adding a filter.

Labeled screenshot of the, Filter on Instances, grid.

These are the options available when defining a filter:

Option	Description
And/Or	Enables you to specify how multiple filter conditions should evaluate. Selecting And indicates that each business object instance must satisfy all conditions in your filter criteria to display in the report. Select Or to indicate that each business object instance must satisfy at least one of the conditions to display in the report.
Parentheses	Some report types, like Advanced, allow you to use any combination of And and Or conditions within a filter. You can use parentheses to group conditions together and control the condition evaluation sequence.
Field	Specifies the field to evaluate.
Operator	Specifies the logical operator in the filter condition. The available choices depend on the field type.
Comparison Type	Specifies how the system should compare the field. The options are: Value specified in this filter - This option allows you to explicitly specify a value to filter on. Value from another field - This option allows you to compare the value of one field to another. Prompt the user for the value - With this option, the user-entered prompt value determines the report filter. Prompt the user for the value and ignore the filter condition if the value is blank - This option configures an optional prompt. If the user enters nothing in the prompt (i.e., blank value), the report ignores the filter condition. The report only uses the value in the filter if it is not blank.
Comparison Value	Specifies the value to compare to the Field value, or the user-selected prompt value. The ability to enable this field and available choices depends on the Field type, the Operator, and the Comparison Type.

Subfiltering

In addition to filtering data from the primary business object, you can also filter data from related business objects. As shown in the image below, on the Subfilter tab, you can specify fields from a related business object to use in filter conditions for the related data. Related business objects and fields in subfilters do not need to display on the report output.

Labeled screenshot of the Subfilter tab.

Filter Logic

It is important to understand how your report filter and subfilter interact. For a given instance of your primary business object, the subfilter logic executes first, then the filter logic.

For example, as shown in the image below take a report where Worker is the primary business object and Expense Report is the related business object:

For a worker on the Worker primary business object, the subfilter looks on the Expense Report related business object to see if the worker has corresponding expense reports.

If the worker has corresponding expense reports, the subfilter applies the "Amount > 1000" logic and returns expense reports that meet the criteria.

Next, the process moves back to the primary business object.

The filter determines if the worker lives in London and returns workers that meet the criteria.

Note that workers who live in London but do not have expense reports will also display on the report.

Note
: If you include the condition " RBO not empty" in the filter, the system filters out instances of the primary business object that do not have related data.

Image showing the filter logic flowchart.

Filtering on Aggregations

You can find two different types of configurable filters on the Filter tab of a report definition. The first section, Filter on Instances, allows you to identify a limited set of instances to return on your report.

The second section, Filter on Aggregations, provides another level of filtering ability for your report. This section allows you to filter on aggregate values of a field, such as Average, Minimum, Maximum, or Sum. This allows you to further filter your report data so that only the results you need display in the report output as shown in the image below.

Screenshot of the Filter on Aggregations grid.

For example, a Staffing Analyst might need to create a compensation report that shows the average compensation by position but excludes positions that have fewer than three incumbents. You can use the Filter on Aggregations section to configure this report.

Prompting

You can use prompts in your filters and subfilters instead of hardcoding filter values. Prompts provide more flexibility because users can specify the criteria for the report each time it runs.

Using Prompts in Filters and Subfilters

You can use the Filter and Subfilter tabs to control which fields should prompt the user for a value at runtime. When defining a filter or subfilter condition, there are two comparison types that you can use to prompt the user for a value.

Comparison Type	Description
Prompt the user for a value	Workday uses the entered prompt value to filter the report. If this prompt value is blank, the report filters by a blank value.
Prompt the user for a value and ignore the filter condition if the value is blank	With this option, if the prompt value is blank, the report ignores the filter condition.

Configuring Prompts

You can configure the prompt settings on the Prompts tab.

The Instructions field lets you specify information to display on the user prompt.

Screenshot of a report prompt window. Underneath the report name, there is a line of text that reads: Instructions: Select one or more employee types from the dropdown list. If you do not select an employee type, the report will run for all employee types. Below the text is a blank field labeled, Employee Type.

Some fields in Workday come with built-in prompts that automatically appear on your report if you add the field to your report definition. The Populate Undefined Prompt Defaults checkbox on the Prompt tab lets you quickly populate any prompt defaults. Select this checkbox to automatically add any undefined prompts to the Prompt Defaults grid.

The Display Prompt Values in Subtitle checkbox lets you show or hide the prompt values at the top of the report output. In the Prompt Defaults grid, you can use the Do Not Include in Subtitle setting to exclude specific prompt values from displaying in the subtitle.

You can specify options and default values for your prompt fields in the Prompt Defaults grid.

These are the options available when defining prompt defaults:

Option	Description
Field	Specifies the field for which you want to enter prompt data.
Prompt Qualifier	If you define a data range or a dataset prompt for this field, you must choose the Prompt Qualifier to identify which prompt you want to override.
Label for Prompt	Overrides the label for this prompt when you run the report.
Default Type	Determines the default values to use when the report runs. The options are: No default value - Specifies that there is no default value for the field. When the report runs, the system prompts the user for a value. Specify default value - Specifies that one or more default values in the Default Value field display to the user in the report prompt field. Determine default value at runtime - Uses the value in the Default Value field to determine one or more default values to display in the report prompt field.
Default Value	This specifies one or more default values or the field to use to determine the default prompt. This function goes with the Specify default value and Determine default value at runtime options.
Required	Requires the user to enter a prompt field value. The field cannot be blank when the report runs.
Do Not Prompt at Runtime	Suppresses the display of the prompt for this field. The system uses any default values automatically, skipping any interaction for the prompt for this field.
Do Not Include in Subtitle	Suppresses the display of the prompt values from the final report output. Any values the user selects from prompts appear below the report title (above the detail data) by default when the report runs.
Do Not Auto Fill for Related Tasks	Clears an incorrect prompt auto fill when running reports. Does not need to be selected unless a prompt is incorrectly auto filled.
Control Prompt Values	Allows users to configure pop-ups to apply filtering criteria to prompt results and specify parent fields to create mapping between prompts. This feature enables control over the range of values a prompt allows at runtime, such as limiting prompts or filtering for date ranges.

Resource
:
For more information on configuring the Control Prompt Values option please sign into Community and search for Report Prompt Control
. Select the release note.

Where Prompts Come From

In addition to defining them in filters and subfilters, prompts can come from other places in the system. You can set the defaults for these prompts on the Prompts tab.

Location	Example
Data Source	The Employees by Organization data source has these built-in prompts: Include Managers, Include Subordinate Organizations, and Organization.
Data Source Filter	The Journal Lines for Company data source filter has nine built-in prompts, including Company, Year, and Period.
Field	The Expense Report Total Amount in Reporting Currency field has a built-in prompt for Reporting Currency.
Calculated Field	You can create a calculated field using the Prompt for Value function.
Prompt Set	You can create a prompt set to group prompt field values. A prompt set allows you to enter the prompt field value once and then the system feeds the one value to the multiple uses of the field. An example is when a report group or a composite report contains multiple reports all prompting on the same field.

Note
: Use a data source with built-in prompts when possible, rather than manually creating filters and prompts on a more generic data source. This results in a more efficient report.

Totaling, Grouping, and Outlining

Totaling and Percentile

You can display subtotals and a grand total when building a custom report using the Advanced report type. These are the steps to set up totaling:

On the Columns tab, select a numeric or currency field (or fields) to aggregate. You can only select one aggregation in the Options field per row, for example Average, Maximum, Minimum, Sum, or a percentile (PCTL).

Optionally, you can add the Count global field as a column in the report. The report displays the count of instances for each subtotal and the grand total.

Note
: Select the 50th percentile option - 50 PCTL (Median) to choose the median value.

On the Sort tab, select which sort levels on the report should display a subtotal for all their instances.

Select if the report should include the group name in subtotals. In the following example, either Region or Cost Center appears in the subtotal rows.

Select if the report should include the word "Total" in subtotal rows.

Select if the report should display a grand total for all instances in the report.

Display subtotals checkboxes are selected for Region and Cost Center on Sort tab. Below, the following checkboxes are selected: Include Group Name in Headers and Subtotals, Include "Total" label in Subtotals, Display Grand Totals.

Important

Sum, subtotals, and the grand total show the sum of the data.

Average, subtotals, and the grand total show the average value of the data.

Maximum, subtotals, and the grand total show the maximum value of the data.

Minimum, subtotals, and the grand total show the minimum value of the data.

Percentile, subtotals, and the grand total show the percentile value of the data.

Grouping

You can add groupings to easily visualize the data when building an Advanced custom report. These are the steps to set up grouping:

On the Columns tab, add the Group Name global field to the top of the Columns grid. You can also override the column heading for this field.

On the Sort tab, select which groups should have a header line at the top of the group.

Select if the report should include the group name in headers. In the example below, either Region or Cost Center appears in the header rows.

Outlining

You can enable outlining to expand and collapse groupings when building an Advanced custom report. To do this, select the Enable Outlining based on Grouping checkbox on the Sort tab.

Note
: To use outlining, all fields on the report must be from the primary business object.

Report Output

The following image shows how the totaling, grouping, and outlining options map to the report output.

These are the totaling, grouping, and outlining options.

Option	Description
Field to Aggregate	Include the field in subtotals and grand totals.
Count	Displays the count of instances for each subtotal and the grand total.
Display Subtotals	Shows the total amount for selected groupings.
Display Grand Totals	Shows the grand total.
Display Headers	Shows headers for selected groupings to make the data easier to read.
Include Group Name in Headers and Subtotals	Shows the name of the grouping in heading and subtotals.
Include "Total" label in Subtotals	Shows the word Total in subtotals.
Enable Outlining based on Grouping	Allows you to expand and collapse groupings.

Summary Reports

You can also generate summary reports when building an advanced custom report. These reports only show totals, without any of the detail data. The following example shows the report summarized by the Region Field.

These are the steps to configure a summary report:

On the Sort tab, select the Summarize Detail Rows checkbox. You can only select the Summarize Detail Rows checkbox on the last row in the Sort and Group grid.

Remove fields from the Columns tab not used to sort or aggregate the data. The system displays an error message if the Columns tab includes additional fields.

Chapter Summary

Key Takeaways:

You can sort by fields on the PBO and sub-level sort by fields on RBOs.

You can filter by fields on the PBO and subfilter by fields on RBOs.

Workday executes subfilter logic first and then filter logic.

You can use prompts in filters and subfilters.

Prompts can come from a variety of factors like the data source, data source filters, and fields you use for your report.

You can specify options and default values for prompt fields.

You can display subtotals and the grand total on a report.

Grouping allows you to group the report data and improve the readability.

Outlining allows you to expand and collapse the groupings.