does anyone know if the following is possible ?

I am trying to retrieve the sales for every product and sales of the
product family that each product belongs to,
I tried the mdx query below, but I get back "unknown dimension:
[Product].[Product Family]",

with member [measures].[Product Family Sales] as
'([Product].[Product Family].CurrentMember,[Measures].[Unit Sales])'
select
{[Measures].[Unit Sales],[Measures].[Product Family Sales]} ON
COLUMNS,
{[Product].Members}
ON ROWS FROM
[Sales]

Greetings

Try the Ancestor() function, as follows:

with
member [measures].[Product Family Sales] as
'(Ancestor([Product].CurrentMember,[Product].[Product
Family]),[Measures].[Unit Sales])'
select
{[Measures].[Unit Sales],[Measures].[Product Family Sales]} ON COLUMNS,
{[Product].Members} ON ROWS
FROM [Sales]

HTH

Olivier.


"John Tai" <devtai (AT) yahoo (DOT) com> wrote

that's very good, thanks

but instead of the same dimension (Product dimension),
what if I want to see a measure from a different dimension,
for example,
i would like to retrieve the employee salary for every employee
(employee dimension),
and the total employee salary for the store (store dimension) that
each employee belongs to,


with member [measures].[Store Salary] as
'([Store].[All Stores].CurrentMember,[Measures].[Employee Salary])'
select
{[Measures].[Employee Salary],[Measures].[Store Salary]} ON
COLUMNS,
{[Employees].Members}
ON ROWS FROM
[HR]