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

mdx

Solution

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.

Problem

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,`and`B&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?

Original source