Symptoms
Consider the following scenario:
-
You run a Multidimensional Expressions (MDX) query that uses a filter in Microsoft SQL Server 2012 Analysis Services (SSAS 2012).
-
The filter uses multiple members on a parent/child dimension.
-
The parent/child dimension uses unary operators.
In this scenario, the returned value is the rollup value for all children of all the members in the dimension. However, the returned value should be the aggregate of the members selected for the filter condition.
Resolution
The issue was first fixed in the following cumulative update of SQL Server.
Cumulative Update 6 for SQL Server 2012 SP1 /en-us/help/2874879
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:
More Information
In SSAS 2012, the VisualTotals function on hierarchies that have unary operators can be fully supported.
Notes:
-
After you apply the update, you have to set the new VisualTotalsEnabledForUnaryOperatorsAndSubSelects server property to 1 to enable the change. If you set the value of this property to another value, the change cannot be enabled, and you will experience the previous behavior in which the VisualTotals function returns the grand total of all the members in the hierarchy instead of the aggregate for the selected members.
-
After you enable the change, only +, -, ~, and weight unary operators are supported. * and / unary operators are not supported for the VisualTotals function. When you run an MDX query that uses the VisualTotals function on hierarchies that have * or / unary operators, validation errors are raised.
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
