7

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?

Travis
  • 663
  • 1
  • 10
  • 16
  • What are you trying to achieve? (Why should you merge two rows and on which base/under which conditions they should be merged?) Have you tried anything? – Pieter Jul 16 '13 at 16:35
  • Added more info to the question, addressing the points you raised. Sorry for not putting in more info earlier – Travis Jul 16 '13 at 16:56

1 Answers1

13

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.

findango
  • 1,483
  • 2
  • 14
  • 16
  • I've tried this solution for calculated members and I get #Error in my results with the message "Aggregate functions cannot be used on calculated members in the Measures dimensions". Any ideas? – Neo Oct 01 '13 at 19:39
  • I'm experimenting with this approach at the moment, and whilst it looks promising, it looks like it requires a [Measure] to work (which in the specific cube I'm using is a little tricky - instead of passing a [Measures] member, you pass a [Calculation] member, and the results set will include numbers based on one of several [Measures] members that are hidden from users). Any suggestions to modify so that the grouped members don't need to have numbers under them at the time they are aggregated? – tobriand Mar 19 '15 at 18:12