MDX query to join two different dimension members

Question

I have below select statement which I want to convert into cube members and calculate the sum.

SELECT NON EMPTY { [Measures].[volume] } ON COLUMNS, NON EMPTY { ([par_Account].[Account].[Account].ALLMEMBERS * [Product].[Hierarchy].[Local product var].ALLMEMBERS ) }  ON ROWS FROM ( SELECT ( { [Product].[Hierarchy].[local product Group].&[-33554010354150679].&[-952789350662854159].&[8639428195894987853] } ) ON COLUMNS FROM ( SELECT ( { [Time].[Financial Period].&[-8540082585673218205] } ) ON COLUMNS FROM ( SELECT ( { [Market].[Market].&[-3381499019102906042] } ) ON COLUMNS FROM [cube]))) WHERE ( [Market].[Market].&[-3381499019102906042], [Time].[Financial Period].&[-8540082585673218205] )

Please help me with this.

Can you add an image of the output? What is "cube member"? Do you need all combinations of [par_Account].[Account].[Account].ALLMEMBERS * [Product].[Hierarchy].[Local product var].ALLMEMBERS to be updated all the time new members added to any of these dimensions? E.g. member [Account].[Account].&[123] is added, it means that new "cube members" (not sure what it is!) are added: [123 for Local product var #1], [123 for Local product var #2] etc. — Alex Peshik

MoazRub MoazRub · Accepted Answer · 2018-11-28T19:06:07

I guess this is what you are looking for. However its always nice to explain what your problem is, or perhaps provide screenshot.

SELECT 
NON EMPTY { [Measures].[volume] } ON COLUMNS, 
NON EMPTY { 
(
[Market].[Market].&[-3381499019102906042] *
[Time].[Financial Period].&[-8540082585673218205] *
{ [Product].[Hierarchy].[local product Group].&[-33554010354150679].&[-952789350662854159].&[8639428195894987853] } *
[par_Account].[Account].[Account].ALLMEMBERS * 
[Product].[Hierarchy].[Local product var].ALLMEMBERS 
) 
}  ON ROWS 
from [cube]

MDX query to join two different dimension members

1 Answers