MDX to dynamically insert calculated members into row?

I need some help writing the MDX for selecting Row members in a particular
query. Here's the background:

The query shows sales results by region and office. The row members should
be like this:

OfficeA
OfficeB
OfficeD
Region1
Region1 (SOC)
OfficeE
OfficeF
OfficeG
OfficeJ
Region2
Region2 (SOC)
World
World (SOC)

There are two things to note about this:

1. The set of offices is being filtered to only show the "active" ones (i.e.
offices which are operational - offices C, H & I have closed down)

2. The Region1, Region2 and World members are straightforward sums of their
(active) children. However, the Region1 (SOC), Region2 (SOC) and World (SOC)
members are calculated - the idea is that they represent a "Same Office
Comparison", in other words only sum the offices that were active both last
year and this year.

The dimensions used to generate the row members are:

1. Region with levels Region and Office
2. Active Office with levels Active (True/False) and SOC (True/False)

This is the MDX I used to generate the members EXCEPT for the "SOC" members:

Member [Region].[World] as
' [Region].[All Region] ' , solve_order = 1

Non Empty
{
Filter(
Hierarchize( Except( [Region].Members , [Region].[All Region] } )
, POST ) ,
[Active Office].[All Active Office].[True] > 0) ,
[Region].[World]
}
ON ROWS

Where I am stuck is generating the "SOC" members and including them in the
set of row members in the correct order. I need to do this without hard
coding any references to specific regions or offices (regions and offices
change over time).

Any ideas?

Many thanks

HI Laurence,

As you have no doubt found, it is hard to insert a calculated member in a specific location in a hierarchy. What I would suggest is to create "real" members in your dimension tables for the "(SOC)" members, but don't have any data associated with them. Then use an assignment in your MDX script to calcuate the value for these members

You could hard code a calculation something like the following:

([Region 1 (SOC)]) = Aggregate([Region].[Region].&[Region 1].Chidren,[Region].[SOC].[True])

But if it were me I think I would probably want to make the calc more generic. You could possibly add an attribute or add a "Total" member to the SOC attribute so that you could pull out just the SOC total members and then grab the children of the previous member and filter them by [SOC].[True] - or something like that

|||

I do need to make the solution as generic as possible as the regions do change .... can you explain a bit more what you mean by '.... add an attribute or add a "Total" member to the SOC attribute ....'

|||

Sure. When I said "add an attribute" I was thinking that you could either change your existing [SOC] column to contain another value, so it would have "True", "False" & "Total" or you could add another column to your dimension table. Then what I would do is to add another row into your dimension table at the same level as the "Region 1" member, but called "Region 1 SOC" and tagged with a specific attribute value so that you could filter down to just these members .You would not have any facts associated with these members, they would simply be placeholders at a specific positions in the dimension. You would then use an assignment in the MDX Script to set the values for these members.

You might then be able use an assignment similar to the following (which is more psuedo code, but hopefully it will help illustrate my thought process)

Code Snippet

SCOPE [Region].[SOC].[SOC].[SOC Total] -- grab just the members tagged as "SOC Total"

-- i'm not clear on your exact structures and logic, but assuming

-- that the "SOC total" members are ordered after the real region totals,

-- the code below grabs the real region's children and adds them up,

-- filtering on only those members where [SOC] is true.

this = aggregate({[Region].[Region].[Region].Prevmember.children

, [Region].[SOC].[SOC].&{True]})

END SCOPE;

|||

Ah-ha, I think I got it! I will try it out, thanks very much.