Version(s): 8.2, 8.1
Last Modified Date: 16 Aug 2016
This article demonstrates how you can 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.
Step 1: Define a dimension as a group by using a calculated member
- 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:
- In the Name field, type France and Germany.
- In the Hierarchy field, select [Customer].[Customer Geography].
- In the formula box, enter the following MDX statement:
[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.
- Click Check Formula to confirm that the formula is valid, and then click OK.
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].
Step 2: Build the view
- From the Dimensions pane, drag [Customer].[Customer Geography].[Country] to the Rows shelf.
- From the Measures pane, drag Internet Sales Amount to Text on the Marks card.
The resulting view displays Internet Sales Amount for each country, including the new value of the group France and Germany.
Alternate Search Terms:Multidimensional data source, Groups, Multidimensional Expressions, Cube, OLAP