Set Manipulation
Set Manipulation instructions reorder or filter the working list once elements are already in it - removing parent (consolidation) elements, sorting alphabetically or by value, and grouping members with blank rows between groups.
| Instruction | What it does |
|---|---|
| Remove Consolidations | Removes every element that has children, leaving only leaf-level elements. |
| Remove Items Using The Next Instruction | A modifier: removes from the working list whatever elements the next instruction specifies. |
| Sort By Name (Ascending) | Sorts the working list alphabetically by element name (or by alias, if one has been applied via Set The Alias For The Set). |
| Sort By Name (Descending) | The reverse alphabetical order. |
| Sort By Value Using The Next Instruction (Ascending) | Sorts the working list by the value the next instruction produces (e.g. a measure or formula). Workview column axis only. |
| Sort By Value Using The Next Instruction (Descending) | The descending equivalent - the usual choice for "top N by value" reports. |
| Group By Value Using The Next Instruction (Ascending) | Groups the working list by the value the next instruction produces, inserting blank rows between groups. Workview column axis only. |
| Group By Value Using The Next Instruction (Descending) | The descending equivalent. |
Remove Consolidations pairs with Expand All
The most common use of Remove Consolidations is directly after Expand All: expand a parent to its full subtree, then strip out every parent-level element so only the leaves remain. Reversing that order - removing consolidations before expanding - removes the only element in the list (since at that point it's still a parent), leaving nothing for Expand All to work with.
"Using The Next Instruction" instructions need a value provider
Sort By Value and Group By Value don't carry their own criterion - they read it from whichever instruction comes immediately after them in the stack, typically a measure reference or a formula:
1. Element: All Products
2. Expand All
3. Remove Consolidations
4. Sort By Value Using The Next Instruction (Descending)
5. Insert Using Formula: LINK("Sales", ["Revenue"])Step 5 isn't a separate addition to the set - it's the value the sort in step 4 uses. If the instruction after a "Using The Next Instruction" modifier doesn't produce a value, the modifier won't behave as expected.
See Common Patterns for a full sort-by-measure example.