Printable View
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.
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.