Who can help me to write a MDX script

• xywsuper

  Old Hand

  Points: 359

  More actions

  January 18, 2009 at 8:32 pm

  #217595

  I have a location dimension and in it I have region and site attribute. One region can have multi-sites

  Now I want to union two sets like

  Set 1:

  region1 50

  region2 30

  Set 2:

  site1 24

  site2 26

  site3 17

  site4 13

  And in the final result I want it looks like

  region1 50 1

  region2 30 1

  site1 24 2

  site2 26 2

  site3 17 2

  site4 13 2

  Here the third column is to indicate it's region or site

  Does anyone can help me for this?

• Wookie-292627

  SSC Veteran

  Points: 243

  More actions

  January 19, 2009 at 5:14 am

  #929117

  Hi xyzsuper,

  if I want to have the MDX-Code of any query, I usually display my cube through a end-user-client. I check "Options - Display MDX-Code" an then I copy and paste the code wherever I want to. You can download a trial-verion here: http://www.rbic.de/dwee

  Regards

  Ralf

• Franky Leeuwerck

  SSCrazy

  Points: 2036

  More actions

  January 19, 2009 at 5:16 am

  #929121

  Hi,

  If you already have the two sets,

  then you can 'union' them like this :

  Union(Set1,Set2)

  or

  Set 1 + Set 2

  or

  {Set1,Set2}

  Franky

  Franky L.

• Dave Balsillie

  SSCertifiable

  Points: 5589

  More actions

  January 19, 2009 at 10:36 am

  #929314

  Ok first, you need to remember that you can't create a "set" of members from more than one attribute hiearchy. Logically attributes are "crossed" to create results.

  However, you can combine attributes to create a hierarchy. While this might seem to violate the above rule, think of it as creating a third thing, which you're going to use.

  Create an attribute hierarchy, something like [Location].[by Region]. Put the region attribute as the first level, then the Site attribute as the second level.

  Now, on to your sets.

  The first set could either be defined as the list of the individual members { [Location].[by Region].&[region1], [Location].[by Region].&[region2] } or more compactly as [Location].[by Region].[Region].Members. The second set could also be compactly defined as [Location].[by Region].[Site].Members. Creating the union of these two sets is simply { [Location].[by Region].[Region].Members, [Location].[by Region].[Site].Members }. This is valid, because both sets are part of [by Region].

  For your "third column", simply create a measure (i.e. [RegionLevel] ):

  [Location].[by Region].CurrentMember.Level.Ordinal