combine two result set side by side using mdx

Question

hi i am new to mdx, i want to combine two select statement resultset (side by side like union) using mdx query,please any body help me to solve this query first query:

 SELECT
{ [Last Year] }
ON COLUMNS,

 { {[Location].[Location].&[7], [Location].[Location].&[12], [Location].[Location].&[11],
[Location].[Location].&[19], [Location].[Location].&[17], [Location].[Location].&[16],
[Location].[Location].&[9], [Location].[Location].&[18] },{[Location].[Location].[All]}}
ON ROWS

FROM [Cube1]

WHERE ( [Measures].[Labour %] )

here [Last Year] is a calculated set

[Last Year]=====

{STRTOMEMBER("[Date].[Month].&["+ cstr(year(now())-2) +"-11-01T00:00:00]"):
        (STRTOMEMBER("[Date].[Month].&["+ cstr(year(now())-1) +"-10-01T00:00:00]"))}

2nd query:

WITH MEMBER [Measures].[Budget ] AS IIF(avg([Last Year],IIF(([Measures].[Budget]>0),[Measures].[Budget],null)),
                                        avg([Last Year],IIF(([Measures].[Budget]>0),[Measures].[Budget],null)),0.00),

MEMBER [Measures].[YTD] AS IIF(avg([Last Year], IIF(([Measures].[Labour %]>0),[Measures].[Labour %],null))<>null,
                              avg([Last Year], IIF(([Measures].[Labour %]>0),[Measures].[Labour %],null)),0.00),
FORMAT_STRING = "Standard",
BACK_COLOR = CASE WHEN [YTD] = 0  THEN /*White*/16777215 /*White*/  
WHEN [YTD] <= [Measures].[Budget ] THEN 65408
     WHEN [YTD]<= [Measures].[Budget ] +5 THEN 65535
     WHEN [YTD]> [Measures].[Budget ] +5 THEN 255
END,
VISIBLE = 1
SELECT
{  [Measures].[YTD], [Measures].[Budget ] }
ON COLUMNS,

{ { [Location].[Location].&[7], [Location].[Location].&[12], [Location].[Location].&[11], [Location].[Location].&[19], [Location].[Location].&[17], [Location].[Location].&[16], [Location].[Location].&[9], [Location].[Location].&[18] },{[Location].[Location].[All]} }
ON ROWS

FROM [Cube1]

**here ==> [Measures].[YTD], [Measures].[Budget ] are calculated member i want result like in

coulmns===> ytd,budget,nov,dec,jan,feb.,,,,,,,,,,october and rows ====> locations and total(average of all locations)

please guide me to get solution like mdx query**

SouravA SouravA · Accepted Answer · 2015-02-20T16:43:27

Your concept of "UNION" wont work for 2 main reasons:

1. You can't UNION members from different hierarchies. 2. Query structures are different.

Only way to see them "side by side", would be to open the queries in two different windows.

Instead, if you just wanted to see a combined view of measures and members, use either of the queries below:

//Go for this if you want to see the Locations in output

WITH MEMBER [Measures].[Budget ] AS IIF(avg([Last Year],IIF(([Measures].[Budget]>0),[Measures].[Budget],null)),
                                        avg([Last Year],IIF(([Measures].[Budget]>0),[Measures].[Budget],null)),0.00),

MEMBER [Measures].[YTD] AS IIF(avg([Last Year], IIF(([Measures].[Labour %]>0),[Measures].[Labour %],null))<>null,
                              avg([Last Year], IIF(([Measures].[Labour %]>0),[Measures].[Labour %],null)),0.00),
FORMAT_STRING = "Standard",
BACK_COLOR = CASE WHEN [YTD] = 0  THEN /*White*/16777215 /*White*/  
WHEN [YTD] <= [Measures].[Budget ] THEN 65408
     WHEN [YTD]<= [Measures].[Budget ] +5 THEN 65535
     WHEN [YTD]> [Measures].[Budget ] +5 THEN 255
END,
VISIBLE = 1


SELECT
{  [Measures].[YTD], [Measures].[Budget ], [Measures].[Labour %] }
ON COLUMNS,
{ [Last Year] }
*
{ { [Location].[Location].&[7], 
[Location].[Location].&[12], 
[Location].[Location].&[11], 
[Location].[Location].&[19], 
[Location].[Location].&[17], 
[Location].[Location].&[16], 
[Location].[Location].&[9], 
[Location].[Location].&[18] },
{[Location].[Location].[All]} }
ON ROWS
FROM [Cube1]

OR

//A cleaner one. Go for this if you don't want to see the Locations in output

WITH MEMBER [Measures].[Budget ] AS IIF(avg([Last Year],IIF(([Measures].[Budget]>0),[Measures].[Budget],null)),
                                        avg([Last Year],IIF(([Measures].[Budget]>0),[Measures].[Budget],null)),0.00),

MEMBER [Measures].[YTD] AS IIF(avg([Last Year], IIF(([Measures].[Labour %]>0),[Measures].[Labour %],null))<>null,
                              avg([Last Year], IIF(([Measures].[Labour %]>0),[Measures].[Labour %],null)),0.00),
FORMAT_STRING = "Standard",
BACK_COLOR = CASE WHEN [YTD] = 0  THEN /*White*/16777215 /*White*/  
WHEN [YTD] <= [Measures].[Budget ] THEN 65408
     WHEN [YTD]<= [Measures].[Budget ] +5 THEN 65535
     WHEN [YTD]> [Measures].[Budget ] +5 THEN 255
END,
VISIBLE = 1


SELECT
{  [Measures].[YTD], [Measures].[Budget ], [Measures].[Labour %] }
ON COLUMNS,
{ [Last Year] } ON ROWS
FROM [Cube1]
WHERE
{ { [Location].[Location].&[7], 
[Location].[Location].&[12], 
[Location].[Location].&[11], 
[Location].[Location].&[19], 
[Location].[Location].&[17], 
[Location].[Location].&[16], 
[Location].[Location].&[9], 
[Location].[Location].&[18] },
{[Location].[Location].[All]} }

combine two result set side by side using mdx

1 Answers