About Selection Steps
The topics in this section explain how you can use selection steps in workbooks.
Selection Steps in Workbooks
In Oracle Analytics, you can use selection steps to specify which levels or members of a hierarchy you want to display in table or pivot table visualizations that use that hierarchy.
Selection steps are similar to filters, but instead of filtering to select values from a column, they let you select members of a hierarchy to participate in the query. You can choose which levels or members of the hierarchy you want to keep, add, or remove in your selection. You can add as many selection steps as you need, and they're applied in a specific order.
Hierarchies
Oracle Analytics supports using selection steps for both level-based hierarchies and parent-child hierarchies, including those with skip-level or ragged structures.
Level-based hierarchies (structure hierarchies) - In these hierarchies, members of the same type occur only at a single level, while members in parent-child hierarchies all have the same type. In level-based hierarchies, levels roll up from a lower level to higher level, for example, months can roll up into a year. These roll ups occur over the hierarchy elements and span natural business relationships.
Parent-child hierarchies (value hierarchies) - In these hierarchies, the business relationships occur between different members of the same real-world type such as the manager-employee relationship in an organizational hierarchy tree. Parent-child hierarchies don't have explicitly named levels. There isn't a limit to the number of implicit levels in a parent-child hierarchy.
How Selection Steps and Filters Differ
It's important to understand the differences between filters and selection steps in workbooks so you can apply them effectively.
Purpose and Application
Filters act on individual columns to reduce or focus the data in one or more visualizations. Filters can apply to any visualization that uses data from the same dataset or subject area as the filter, including joined datasets. For more information, see About Filters.
Selection steps act on hierarchies and don't actually filter the data in visualizations. Instead, they specify which levels or members from the hierarchy are displayed in any visualizations that use the same hierarchy that the selection step is based on. Selection steps have no effect on visualizations that don't use the same hierarchy.
Impact on Aggregation
Filters are applied to data before aggregation and therefore affect any aggregate values in visualizations. Selection steps are applied after aggregation and therefore have no impact on aggregate values.
For example, suppose you have a table visualization named Revenue in 2010 that uses attribute and measure columns to show revenue by quarter for the year 2010, including the grand total for the whole year.
Description of the illustration selection_steps_example_aggregation_filter1.png
If you add a canvas filter in the workbook filter bar to exclude the first quarter of 2010, the row for the first quarter disappears from the table and the grand total for the whole year changes to reflect the excluded filter value.
Description of the illustration selection_steps_example_aggregation_filter2.png
Now suppose your table visualization uses a time hierarchy to show the same data, now named Revenue in 2010 (Time Hierarchy).
Description of the illustration selection_steps_example_aggregation_ss1.png
If you add a selection step in the workbook filter bar to remove the first quarter of 2010, the row for the first quarter is gone from the table, but total revenue for the whole year doesn't change. This is because that selection step is applied after the query for revenue in 2010 was aggregated so it's only removing the 2010 Q1 member from the visualization.
Description of the illustration selection_steps_example_aggregation_ss2.png
Order of Operation
In the workbook filter bar, the order in which workbook or canvas filters appear is irrelevant. However, the order in which selections steps appear is critical because it dictates the order in which they're applied.
For example, suppose you have a table visualization named Revenue in 2010 that uses a time hierarchy to show revenue by quarter for the year 2010, including the total revenue for the year. Suppose the first selection step in the filter bar starts with the year 2010 and then removes the first quarter, 2010 Q1, so only the revenue amounts for the year itself and the remaining three quarters are displayed.
Description of the illustration selection_steps_example_order_before.png
If you add another, separate selection step in the filter bar that adds back the first quarter, 2010 Q1, the visualization displays all four quarters for the year 2010, as expected.
Description of the illustration selection_steps_example_order_add.png
If you swap the order of these two selection steps in the filter bar, the visualization doesn't display any data. This is because now, the selection step adding the member 2010 Q1 is applied first so the visualization starts with only 2010 Q1. The second selection step to keep 2010 and remove 2010 Q1 doesn't work because you can't keep the member 2010 if it's not in the selection to begin with.
Description of the illustration selection_steps_example_order_swap.png
It's important to make sure the selection steps appear in a logical order so that when they're applied, the visualization displays the data you expect.
Selection Steps Action and Operator Reference
Selection steps use actions and operators to help you access the specific data that you want to display in a table or visualization.
The combination of the action, operator, and values that you select determines what the selection step does.
To add a selection step, see Add Selection Steps in the Filter Bar.
Actions
There are three actions available for selection steps:
-
Add - Lets you select levels or members to add to the selection.
-
Keep Only - Lets you select the levels or members that you want to keep within the selection. This action doesn't add anything to the selection, it works only on levels or members that are already included in the selection. This is the default action.
-
Remove - Lets you select levels or members to remove from the selection.
Operators
The operators available for a selection step depend on the type of hierarchy you're using.
The Levels operator is available only for level-based hierarchies. It lets you specify the levels you want the selection step to act on; for example, Year, Quarter, Month, and Day for a time-based hierarchy.
When you choose any other operator, you can specify which members of the hierarchy you want the selection step to act on. Members are those data points residing at specific nodes in the hierarchy.
-
Members - Lets you specify the exact members you want the selection step to act on. Only the selected members are displayed in the visualization. This is the default operator.
-
+ Children - Lets you specify members so that the selection step acts on them and their children. The members and their children are displayed in the visualization.
-
+ Parents - Lets you specify members so that the selection step acts on them and their parents. The members and their parents are displayed in the visualization.
-
+ Descendants - Lets you specify members so that the selection step acts on them and their descendants. The members and all their descendants, down to the lowest members, are displayed in the visualization.
-
+ Ancestors - Lets you specify members so that the selection step acts on them and their ancestors. The members and their ancestors are displayed in the visualization.
-
+ Siblings - Lets you specify members so that the selection step acts on them and their siblings. The members and their siblings are displayed in the visualization.
-
+ Leaves - Lets you specify members so that the selection step acts on them and all the leaves under them. The specified members and the lowest members under them are displayed in the visualization. Any members in between are excluded.
-
Children - Lets you specify members so that the selection step acts only on their children (not the selected members themselves). Only the children are displayed in the visualization.
-
Parents - Lets you specify members so that the selection step acts only on their parents. Only the parents are displayed in the visualization.
-
Descendants - Lets you specify members so that the selection step acts only on their descendants. Only the descendants are displayed in the visualization.
-
Ancestors - Lets you specify members so that the selection step acts only on their ancestors. Only the ancestors are displayed in the visualization.
-
Siblings - Lets you specify members so that the selection step acts only on their siblings. Only the siblings are displayed in the visualization.
-
Leaves - Lets you specify members so that the selection step acts only on the leaves under them. Only the leaves are displayed in the visualization.
Operator Examples
-
Members
Level hierarchy example: For a table showing revenue for a time dimension, if you use a selection step with the action Keep Only, the operator Members, and the members 2010 and 2011, the table shows the revenue for only those two years.
Description of the illustration selection_steps_lvlhierarchy_ex_members.pngParent-child hierarchy example: For a table showing revenue for a sales representative hierarchy, if you use a selection step with the action Keep Only, the operator Members, and the members Helen Mayes and Monica Velasquez, the table shows the revenue for only those two representatives. The names in the table are displayed alphabetically.
Description of the illustration selection_steps_pchierarchy_ex_members.png -
+ Children
Level hierarchy example: For a table showing revenue for a time hierarchy, if you use a selection step with the action Keep Only, the operator + Children, and the members 2010 and 2011, the table shows the revenue for those two years and their children, in this case, four quarters.
Description of the illustration selection_steps_lvlhierarchy_ex_members_children.pngParent-child hierarchy example: For a table showing revenue for a sales representative hierarchy, if you use a selection step with the action Keep Only, the operator + Children, and the member Helen Mayes, the table shows the revenue for her and the representatives reporting directly to her. The names in the table are displayed alphabetically.
Description of the illustration selection_steps_pchierarchy_ex_members_children.png -
+ Parents
Level hierarchy example: For a table showing revenue for a time hierarchy, if you use a selection step with the action Keep Only, the operator + Parents, and the members 2010 and 2011, the table shows the revenue for those two years and their parent, in this case, Total Time.
Description of the illustration selection_steps_lvlhierarchy_ex_members_parents.pngParent-child hierarchy example: For a table showing revenue for a sales representative hierarchy, if you use a selection step with the action Keep Only, the operator + Parents, and the member Helen Mayes, the table shows the revenue for her and the representative she reports to. The names in the table are displayed alphabetically.
Description of the illustration selection_steps_pchierarchy_ex_members_parents.png -
+ Descendants
Level hierarchy example: For a table showing revenue for a time hierarchy, if you use a selection step with the action Keep Only, the operator + Descendants, and the members 2010 and 2011, the table shows the revenue for those two years and all their descendants, in this case, quarters, months, and days.
Description of the illustration selection_steps_lvlhierarchy_ex_members_descendants.pngParent-child hierarchy example: For a table showing revenue for a sales representative hierarchy, if you use a selection step with the action Keep Only, the operator + Descendants, and the member Helen Mayes, the table shows the revenue for her and all the representatives in the reporting structure under her. The names in the table are displayed alphabetically.
Description of the illustration selection_steps_pchierarchy_ex_members_descendants.png -
+ Ancestors - Lets you specify members so that the selection step acts on them and their ancestors. The members and their ancestors are displayed in the visualization.
Level hierarchy example: For a table showing revenue for a time hierarchy, if you use a selection step with the action Keep Only, the operator + Ancestors, and the members 2010 and 2011, the table shows the revenue for those two years and their ancestor, in this case, Total Time.
Description of the illustration selection_steps_lvlhierarchy_ex_members_ancestors.pngParent-child hierarchy example: For a table showing revenue for a sales representative hierarchy, if you use a selection step with the action Keep Only, the operator + Ancestors, and the member Helen Mayes, the table shows the revenue for her and the representative she reports to. The names in the table are displayed alphabetically.
Description of the illustration selection_steps_pchierarchy_ex_members_ancestors.png -
+ Siblings - Lets you specify members so that the selection step acts on them and their siblings. The members and their siblings are displayed in the visualization.
Level hierarchy example: For a table showing revenue for a time hierarchy, if you use a selection step with the action Keep Only, the operator + Siblings, and the members 2010 and 2011, the table shows the revenue for those two years and their siblings, in this case, the rest of the years between 2008 and 2012.
Description of the illustration selection_steps_lvlhierarchy_ex_members_siblings.pngParent-child hierarchy example: For a table showing revenue for a sales representative hierarchy, if you use a selection step with the action Keep Only, the operator + Siblings, and the member Helen Mayes, the table shows the revenue for her and the other representatives at the same reporting level. The names in the table are displayed alphabetically.
Description of the illustration selection_steps_pchierarchy_ex_members_siblings.png -
+ Leaves - Lets you specify members so that the selection step acts on them and all the leaves under them. The specified members and the lowest members under them are displayed in the visualization. Any members in between are excluded.
Level hierarchy example: For a table showing revenue for a time hierarchy, if you use a selection step with the action Keep Only, the operator + Leaves, and the members 2010 and 2011, the table shows the revenue for those two years and all their leaves, in this case, days.
Description of the illustration selection_steps_lvlhierarchy_ex_members_leaves.pngParent-child hierarchy example: For a table showing revenue for a sales representative hierarchy, if you use a selection step with the action Keep Only, the operator + Leaves, and the member Helen Mayes, the table shows the revenue for her and all the representatives at the lowest level under her, excluding any in between. The names in the table are displayed alphabetically.
Description of the illustration selection_steps_pchierarchy_ex_members_leaves.png -
Children - Lets you specify members so that the selection step acts only on their children (not the selected members themselves). Only the children are displayed in the visualization.
Level hierarchy example: For a table showing revenue for a time hierarchy, if you use a selection step with the action Keep Only, the operator Children, and the members 2010 and 2011, the table shows the revenue for the children of those two years, in this case, the quarters.
Description of the illustration selection_steps_lvlhierarchy_ex_children.pngParent-child hierarchy example: For a table showing revenue for a sales representative hierarchy, if you use a selection step with the action Keep Only, the operator Children, and the member Helen Mayes, the table shows the revenue for the two representatives reporting directly to her. The names in the table are displayed alphabetically.
Description of the illustration selection_steps_pchierarchy_ex_children.png -
Parents - Lets you specify members so that the selection step acts only on their parents. Only the parents are displayed in the visualization.
Level hierarchy example: For a table showing revenue for a time hierarchy, if you use a selection step with the action Keep Only, the operator Parents, and the members 2010 and 2011, the table shows the revenue for the parent of those two years, in this case, Total Time.
Description of the illustration selection_steps_lvlhierarchy_ex_parents.pngParent-child hierarchy example: For a table showing revenue for a sales representative hierarchy, if you use a selection step with the action Keep Only, the operator Parents, and the member Helen Mayes, the table shows the revenue for the representative she reports to.
Description of the illustration selection_steps_pchierarchy_ex_parents.png -
Descendants - Lets you specify members so that the selection step acts only on their descendants. Only the descendants are displayed in the visualization.
Level hierarchy example: For a table showing revenue for a time hierarchy, if you use a selection step with the action Keep Only, the operator Descendants, and the members 2010 and 2011, the table shows the revenue for all the descendants of those two years, in this case, quarters, months, and days.
Description of the illustration selection_steps_lvlhierarchy_ex_descendants.pngParent-child hierarchy example: For a table showing revenue for a sales representative hierarchy, if you use a selection step with the action Keep Only, the operator Descendants, and the member Helen Mayes, the table shows the revenue for all the representatives in the reporting structure under her. The names in the table are displayed alphabetically.
Description of the illustration selection_steps_pchierarchy_ex_descendants.png -
Ancestors - Lets you specify members so that the selection step acts only on their ancestors. Only the ancestors are displayed in the visualization.
Level hierarchy example: For a table showing revenue for a time hierarchy, if you use a selection step with the action Keep Only, the operator Ancestors, and the members 2010 and 2011, the table shows the revenue for the ancestor of those two years, in this case, Total Time.
Description of the illustration selection_steps_lvlhierarchy_ex_ancestors.pngParent-child hierarchy example: For a table showing revenue for a sales representative hierarchy, if you use a selection step with the action Keep Only, the operator Ancestors, and the member Helen Mayes, the table shows the revenue for the representative she reports to.
Description of the illustration selection_steps_pchierarchy_ex_ancestors.png -
Siblings - Lets you specify members so that the selection step acts only on their siblings. Only the siblings are displayed in the visualization.
Level hierarchy example: For a table showing revenue for a time hierarchy, if you use a selection step with the action Keep Only, the operator Siblings, and the members 2010 and 2011, the table shows the revenue for the siblings of those two years, in this case, the rest of the years between 2008 and 2012.
Description of the illustration selection_steps_lvlhierarchy_ex_siblings.pngParent-child hierarchy example: For a table showing revenue for a sales representative hierarchy, if you use a selection step with the action Keep Only, the operator Siblings, and the member Helen Mayes, the table shows the revenue for the other representatives at the same reporting level as her. The names in the table are displayed alphabetically.
Description of the illustration selection_steps_pchierarchy_ex_siblings.png -
Leaves - Lets you specify members so that the selection step acts only on the leaves under them. Only the leaves are displayed in the visualization.
Level hierarchy example: For a table showing revenue for a time hierarchy, if you use a selection step with the action Keep Only, the operator Leaves, and the members 2010 and 2011, the table shows the revenue for all the leaves of those two years, in this case, days.
Description of the illustration selection_steps_lvlhierarchy_ex_leaves.pngParent-child hierarchy example: For a table showing revenue for a sales representative hierarchy, if you use a selection step with the action Keep Only, the operator Leaves, and the member Helen Mayes, the table shows the revenue for all the representatives at the lowest level under her, excluding any in between. The names in the table are displayed alphabetically.
Description of the illustration selection_steps_pchierarchy_ex_leaves.png