Question
How to use Multidimensional Expressions (MDX) to group cube dimensions.
How to use Multidimensional Expressions (MDX) to group cube dimensions.
Note: The steps use the attached MDX Groups Example workbook, which contains a connection to the Microsoft Analysis Services (MSAS) AdventureWorks cube. To use this workbook, you must change the Server value from scdemo-dbs to the name of your MSAS server that hosts AdventureWorks.
Download and open the MDX Groups Example workbook.
Click the Dimensions pane drop-down menu, and then select Calculated Members.
In the Calculated members dialog box, click New.
In the Calculated Member Definition section, do the following:
[Customer].[Customer Geography].[France] + [Customer].[Customer Geography].[Germany]
This formula groups two dimension values together, which in this case is the values of France and Germany from the Country dimension.
Note: This calculated member will not appear in the Measures pane because it is not being applied to [Measures]. Instead, this calculated member is assigned to the hierarchy [Customer].[Customer Geography], and creates a specific location with a new value in that hierarchy. In this example, the new value is a group of [France] and [Germany].
The resulting view displays Internet Sales Amount for each country, including the new value of the group France and Germany.