CODEWITHSUNDEEP
mdx
user138957
user138957

Reputation: 259

merge two mdx queries

I have two MDX queries.

First query :

SELECT {[Measures].[Actual]} ON 0,
{ DESCENDANTS([Dim Office].[Offices].[Office Level 02].ALLMEMBERS)
*DESCENDANTS([Dim Business Line].[Business Lines].[Business Line Level 02].ALLMEMBERS)
*{[Dim Date].[Fiscal Dim Date Calculations].&[Current Dim Date]}} ON 1
FROM
[BI]

Second one :

SELECT {[Measures].[Actual]} ON 0,
{ DESCENDANTS([Dim Office].[Offices].[Office Level 02].ALLMEMBERS)
*[Dim PNL].[PNL].&[6]
*{[Dim Date].[Fiscal Dim Date Calculations].&[Current Dim Date]}} ON 1
FROM
[BI]

How can I get the union of the two queries ?

Upvotes: 0

Views: 3391

Answers (3)

user3843858
user3843858

Reputation: 331

try this,

SET1 as
{ DESCENDANTS([Dim Office].[Offices].[Office Level 02].ALLMEMBERS)
*DESCENDANTS([Dim Business Line].[Business Lines].[Business Line Level 02].ALLMEMBERS)
*{[Dim Date].[Fiscal Dim Date Calculations].&[Current Dim Date]}}
SET2 as
{ DESCENDANTS([Dim Office].[Offices].[Office Level 02].ALLMEMBERS)
*[Dim PNL].[PNL].&[6]
*{[Dim Date].[Fiscal Dim Date Calculations].&[Current Dim Date]}}

SET3 as 
Union(SET1,SET2)

select  {[Measures].[Actual]} ON 0,
SET3 on 1
From [BI]

Upvotes: 1

George
George

Reputation: 702

There is a workaround, you could use T-SQL EXEC statement to make such thing. Please check my answer here, to find the way to query AS server from SQL server. In short, you just need two temp tables:

declare @tsqlquery1 varchar(1000) = ...
declare @mdxquery1 varchar(1000) = ''' SELECT {[Measures].[Actual]} ON 0,
 { DESCENDANTS([Dim Office].[Offices].[Office Level 02].ALLMEMBERS)
   *DESCENDANTS([Dim Business Line].[Business Lines].[Business Line Level 02].ALLMEMBERS)
 *{[Dim Date].[Fiscal Dim Date Calculations].&[Current Dim Date]}} ON 1
 FROM
 [BI])'''' + ''' + ')'

 -- same for second mdx
 ...
 -- add temp tables
 create table #t1 (...)
 create table #t2 (...)

 -- insert MDX results into temp table1
 insert into #t1
 EXEC(@tsqlquery1 + @mdxquery1)

 -- insert MDX results into temp table2
 insert into #t2
 EXEC(@tsqlquery2 + @mdxquery2)

 -- get union
 select * from #t1 union select * from #t2

Upvotes: 0

FrankPl
FrankPl

Reputation: 13315

If you mean you want to concatenate the results of both queries, this is not possible in MDX. MDX is a strongly typed language, and a report has to have the same structure with regard to hierarchy on all axes. And your row axes differ: the first uses [Dim Business Line].[Business Lines] as the second hierarchy, and the second uses [Dim PNL].[PNL] as the second hierarchy.

You should concatenate both results in the client tool that you are using.

Upvotes: 0

Related Questions