CODEWITHSUNDEEP
subquerymdx
KosteK
KosteK

Reputation: 13

SubSelect MDX Query as filtered list of main query

SubSelect MDX Query as filtered list of main query

Hi all I want to write MDX query like to SQL:

select a, b, sum(x) from table1 where b = "True" and a in (select distinct c from table2 where c is not null and d="True") group by a,b

I try something like this:

`Hi all I want to write MDX query like to SQL:

select a, b, sum(x) from table1 where b = "True" and a in (select distinct c from table2 where c is not null and d="True") group by a,b

I try something like this: 

    SELECT 
 NON EMPTY { [Measures].[X] } ON COLUMNS, 
 NON EMPTY { [A].[Name].[Name] 
  *[B].[Name].[Name].&[True]
  } ON ROWS
 FROM
 (
  SELECT 
    { ([A].[Name].[Name] ) } ON 0 
     FROM 
    ( SELECT ( 
        {EXCEPT([C].[Name].ALLMEMBERS, [C].[Name].[ALL].UNKNOWNMEMBER) }) ON COLUMNS 
        FROM
         ( SELECT ( 
         { [D].[Name].&[True] } ) ON COLUMNS 
         FROM [CUBE])) 
  )

But it returns me the sum of x from subquery.

How it should look like? '

Upvotes: 1

Views: 501

Answers (1)

Danylo Korostil
Danylo Korostil

Reputation: 1484

Does X's measure group have relationship with D dimension? If it's true, the following code must just work:

 Select 
 [Measures].[X] on 0, 
 Non Empty [A].[Name].[Name].Members * [B].[Name].&[True] on 1
 From [CUBE]
 Where ([D].[Name].&[True])

If you have many-to-many relationship, you need an extra measure (say Y):

Select 
[Measures].[X] on 0, 
Non Empty NonEmpty([A].[Name].[Name].Members,[Measures].[Y]) * [B].[Name].&[True] on 1
From [CUBE]
Where ([D].[Name].&[True])

Upvotes: 1

Related Questions