MDX Query - Select Columns From Same Dimensions

Question

I have a requirement displaying data from same dimension in more than 1 column. For eg. I want to show data Year and Month wise. In my dimension structure, Year and Month belongs to same hierarchy. When I run below query I get error. PFB the query.

Select NON EMPTY {[Measures].[Target Actual Value]} ON 0,
NON EMPTY {[Realization Date].[Hierarchy].[Year Name].Members *
[Realization Date].[Hierarchy].[Month Year]} ON 1
From [Cube_BCG_OLAP]

The error I get is Query (2, 12) The Hierarchy hierarchy is used more than once in the Crossjoin function. I am new to MDX queries. Please help in this regard. Thanks in advance.

Answer 1

Select NON EMPTY {[Measures].[Target Actual Value]} ON 0,
NON EMPTY {[Realization Date].[Hierarchy].[Year Name].Members , 
[Realization Date].[Hierarchy].[Month Year]} ON 1
From [Cube_BCG_OLAP]

Instead of CROSSJOIN have a set as above. In a set, you can put members from same hierarchy

Answer 2

I like Sourav's answer - but it will put the results in one column which is slightly different than the question.

In AdvWorks this is in one column:

SELECT 
  [State-Province].MEMBERS ON COLUMNS
 ,{
    [Date].[Calendar].[Calendar Year].MEMBERS
   ,[Date].[Calendar].[Month].MEMBERS
  } ON ROWS
FROM [Adventure Works];

It is possible to switch to two columns and use a cross join but you need to find out the details of your Date dimensions Attribute hierarchies (as opposed to User hierarchies):

SELECT 
  [State-Province].MEMBERS ON COLUMNS
 ,
  [Calendar Year].[All Periods].Children 
* [Month].MEMBERS ON ROWS
FROM [Adventure Works];

In your cube maybe something like this:

SELECT 
  NON EMPTY 
    {[Measures].[Target Actual Value]} ON 0
 ,NON EMPTY 
    [Year Name].MEMBERS 
  * 
    [Month Year].MEMBERS ON 1
FROM [Cube_BCG_OLAP];