Post reply

Who can help me to write a MDX script

  • 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?

  • 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

  • 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.

  • 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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply