PDA

View Full Version : to fetch members of same level using MDX query



sheetee
03-15-2013, 03:14 AM
I'm new to MDX and also this forum,so please forgive any misuse of terminology.
I have a dataset which has countries, Country ids and companies.


Countries and their ids


Countries 15013
Countries 15014
Countries 15015
Countries 15016
Countries 15017
Countries 15018


Country ids and their companies,
for company id 15013


15013 ALFA LAVAL
15013 ALFA LAVAL
15013 ASSA ABLOY
15013 ASSA ABLOY
15013 ATLAS COPCO
15013 ATLAS COPCO


and for company id 15014


15014 AKER SOLUTIONS (AKSO.OL)
15014 AKER SOLUTIONS
15014 AKER YARDS
15014 AKTIV KAPITAL


so on ...




Using the below MDX Query i can fetch all country ids.


SELECT [Measures].[Name] ON 0,
NON EMPTY {[Parent].[Countries] * [Child].Children} ON 1 FROM [Companies]


and to fetch companies for a particular id for ex : 15013 i run this query


SELECT [Measures].[Name] ON 0,
NON EMPTY {[Parent].[15013] * [Child].Children} ON 1 FROM [Companies]


I have around 60 country ids, so i have to write 60 MDX queries to fetch all the companies from all the countries and its time consuming.


I want to do this using a single MDX query dynamically without using the IDs.


I tried using Descendants but it didnt work. Below is the code using Descendants



SELECT [Measures].AllMembers ON 0,
Descendants ([Parent].AllMembers,[Parent].[Countries],AFTER) ON 1
FROM [Companies]





SELECT [Measures].AllMembers ON 0,
Descendants ([Parent].AllMembers, 2, AFTER) ON 1
FROM [Companies]


Can anyone please help me out in this .. Thanks in Advance.