Eliminations are a powerful mechanism for creating complex query structures known as ragged hierarchical grids or ragged queries. This feature allows users to pick different combinations of elements from different hierarchies to better display the data content without having to write custom MDX.
The specialized eliminations mechanism is only enacted from the right-click context menu under "Member Selections". User must choose "Eliminate Rows" or "Eliminate Columns" to create the desired effect. This should not be confused with the standard "Eliminate" function which removes the element from the hierarchy's selections in all permutations in the grid.
Once eliminated, users can turn different eliminations on or off from the eliminations dialog found in the query ribbon.
In this typical financial statements grid, users want to see 2001 and 2002 Actuals, but only want to see 2002 budget and 2002 budget variance.
Using the standard elimination of elements, a user eliminating CY2001 will eliminate it ACROSS all permutations, including Actuals.
The solution is to use the "Eliminate Columns" option in the context menu under member selection. This will eliminate the combination "Budget CY2001" and "Budget Variance CY2001" without removing "Actual CY2001".
|The desired effect is shown below.|
In general, the treatment of eliminations on other aspects of querying meet most users' expectations. These are explained below:
Eliminations are generally removed from any totals applied by the end user to a query. Except in these cases:
- They are NOT removed from the natural parent totals of elements.
- They are also NOT excluded when the elimination "tuple" or hierarchy combination includes a measure. This does not apply if measures are in the sub-sets (filter axis).
- If the data source is SSAS 2005
See visual totaling for more.
Sorts and Filters are applied AFTER eliminations. As such, any tuples removed by elimination will not be included in the final sorted or filtered results.