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

Question

On rows, I basically want to select all the members of a hierarchy but would like to combine two of them into one. For example, the members would include A, B, and C, so selecting [Group].[Group].members would give me All, A, B, and C but I would like to get All, A,andB&C, where B and C have been merged into one member.

Is this possible within a query?

The database I'm using stores information about the shipping speed of orders, being small parcel, less than truck load, and white glove. I would like to merge small parcel and and less than truck load so I can get aggregate data across the two shipping speeds.

I tried creating a calculate member: [Measures].[Not White Glove] as AGGREGATE([Order Product].[Ships Via Group].&[Small Parcel], [Order Product].[Ships Via Group].&[Less than Truck Load]) but am unsure how to then use that as I currently 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 error Query (14, 11) The Ships Via Group hierarchy is used more than once in the Crossjoin function.

Is there a better way to go about this/what does that error mean?

Answer 1

The error you're seeing is because of your syntax with the parentheses: (a, b, c) defines a tuple where a, b, and c are each members from a different dimension. If you're trying to union these members together, you should use the shorthand: {a, b, c}.

Now, to combine the members is possible, although maybe not as clean and easy as you would want. Here's an example of one way to do it, by creating a new member and then excluding (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 that helps set you on the right track.