Results 1 to 2 of 2

Thread: MDX - Exclude Condition

  1. #1
    Join Date
    Dec 2005
    Posts
    1

    MDX - Exclude Condition

    I have to built a query to get the % for all the Region (Americas, Asia and Europe) from a cube.

    But in these regions some countries are excluded and treated seperate.

    Like Asia does not include India and Japan.

    How do I get the ASIA query using an EXCLUDE condition.

    Please help.

  2. #2
    Join Date
    Jan 2006
    Posts
    2
    I don't know what you mean by exclude condition.
    Anyway, you have several ways to implement the query.

    1) One option is to change your cube and build a new hierarchy for your alternative geographic organization.

    2) Another option is to create named sets. You can do this in the Analysis Manager. then you can use those named sets and assign them directly to axis in your queries.

    3) The third option is to use a query in which you create a set with all the memers and substract the expections from it. Something like these samples:

    Except({[Geography].[Country].Members]},{[Japan],[India]})

    or

    Except({[Geography].[Asia].Children]},{[Japan],[India]})

    You can use a combination of 2) and 3). i.e. creating named sets in 2) for specifying the exceptions in 3).

    Both solutions 2) and 3) involve the creation of calculated members (WITH MEMBER) in your query.

    You can try this in FoodMart:
    WITH MEMBER [Customers].[All Customers].[USA minus OR]
    AS 'Sum(Except([Customers].[All Customers].[USA].Children,{[Customers].[All Customers].[USA].[OR]}))'
    SELECT {AddCalculatedMembers([Customers].[Country].Members)} ON COLUMNS
    FROM Sales

    My firt option would be hte 1st one, as I suppose that you'll be doing this kind of query again and again. Your effort to create the new dimension will be more than paid.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •