Is it possible to collapse several similar nodes of different branches in a hierarchical dimension?

Question

Let's say i have the following hierarchy that i use as a dimension:

• Root
• A1
• B11
• B12
• ...
• B1N
• B1Special
• A2
• B21
• B22
• ...
• B2N
• B2Special
• ...
• AM
• BM1
• BM2
• ...
• BMN
• BMSpecial

Under each of the "B" nodes there are several more nodes at different levels. Each leaf of the hierarchy has a measure associated (SUM of some fact F). Is it possible with MDX to have the SUM of all and only the items children of the "Special" nodes?

Answer 1

I have to assume you want to see the sum of all 'Special' nodes only once, at the root level. In other words, you want to see just one number in your results set.

Assuming the hierarchy detailed in your original question was called 'Bob', and you had another dimension called 'Kate', you might try this...

WITH MEMBER [Bob].[Only the special levels] 
AS 'Aggregate(
   Filter(
      {[Bob].[Name of level which holds B members].members},
      InStr(1, [Bob].CurrentMember.Name, "Special") > 0
   )
)'
SELECT {[Kate].defaultMember} ON ROWS, 
{[Measures].[Whever you want to see aggregated]} ON COLUMNS 
FROM [Cube name] 
WHERE ([Bob].[Only the special levels])

This creates a new, temporary, member in the Bob dimension, which is an aggregation of several other members in the Bob dimension. We start with all the members that sit in one particular level. The Filter chooses only those members which have the word "special" in their name.

Note that InStr is a VBA function which is supported by Microsoft SSAS. It returns zero if the chosen string is not found. Alternative string searching functions may be available in other flavours of MDX.

You then use this new member in your WHERE clause, and slap your other dimensions/measures wherever you want.