How can I combine two members into one in a query?

Question

In the lines, I basically want to highlight all the members of the hierarchy, but I would like to combine two of them into one. For example, members will include A , B and C , so selecting [Group].[Group].members will give me All , A , B and C , but I would like to get All, A, and B&C , where B and C were combined in one member.

Is this possible in the request?

The database that I use contains information on the speed of delivery of orders, which is a small package, less load on the truck and a white glove. I would like to combine a small package and less load on the truck so that I can get aggregate data for two delivery speeds.

I tried to create a calculation member: [Measures].[Not White Glove] as AGGREGATE([Order Product].[Ships Via Group].&[Small Parcel], [Order Product].[Ships Via Group].&[Less than Truck Load]) , but I don’t know how to use it, because I have [Order Product].[Ships Via Group].members ON ROWS .

When I put ([Measures].[Not White Glove], [Order Product].[Ships Via Group].&[White Glove], [Order Product].[Ships Via Group].&[All]) ON ROWS , I get the Query (14, 11) The Ships Via Group hierarchy is used more than once in the Crossjoin function. error Query (14, 11) The Ships Via Group hierarchy is used more than once in the Crossjoin function.

Is there a better way to do this / what does this error mean?

Answer 1

The error you see is related to your parenthesis syntax: (a, b, c) defines a tuple where a, b and c are members from another dimension. If you are trying to combine these members together, you should use the abbreviated expression: {a, b, c} .

Now, to unite members, it is possible, although perhaps not as simple and simple as you would like. Here is an example of one way to do this by creating a new member and then eliminating (via Except ) the original members from the hierarchy.

 WITH SET [Combined] AS { [Customer].[Customer Geography].[Country].&[France], [Customer].[Customer Geography].[Country].&[Germany] } MEMBER [Customer].[Customer Geography].[France & Germany] AS Aggregate([Combined]) SELECT [Measures].[Internet Sales Amount] ON 0, Union( Except([Customer].[Customer Geography].[Country], [Combined]), [Customer].[Customer Geography].[France & Germany] ) ON 1 FROM [Adventure Works] 

Results:

  Internet Sales Amount Australia $9,061,000.58 Canada $1,977,844.86 United Kingdom $3,391,712.21 United States $9,389,789.51 France & Germany $5,538,330.05 

Hope this helps you on the right track.