WITH
MEMBER [Date].[Fiscal].[DateSet] AS 'SUM({[Date].[Fiscal].[Fiscal
Semester].&[2004]&[1]})'
MEMBER [Employee].[Employee Department].[EmployeeSet] AS 'SUM({
[Employee].[Employee Department].[Department Name].&[Sales].&[Sales
Representative], [Employee].[Employee Department].[Department
Name].&[Sales].&[European Sales Manager], [Employee].[Employee
Department].[Department Name].&[Sales].&[North American Sales Manager]
})'

SELECT
{
[Measures].[Reseller Sales Amount]
}

ON COLUMNS,
NON EMPTY
{
NonemptyCrossJoin (
{[Product].[Product Model Categories].[All Products]},
{[Geography].[Geography].[All Geographies]}
)

}
ON ROWS
FROM [Channel Sales]
WHERE
(
[Date].[Fiscal].[DateSet],
[Employee].[Employee Department].[EmployeeSet]
)

--- chris h.

done more research. In AS2005 I can say:


SELECT
{
[Measures].[Reseller Sales Amount]
}

ON COLUMNS,
NON EMPTY
{
NonemptyCrossJoin (
{[Product].[Product Model Categories].[All Products]},
{[Geography].[Geography].[All Geographies]}
)

}
ON ROWS
FROM [Channel Sales]
WHERE
(
{[Date].[Fiscal].[Fiscal Semester].&[2004]&[1]},
{[Employee].[Employee Department].[Department Name].&[Sales].&[Sales
Representative], [Employee].[Employee Department].[Department
Name].&[Sales].&[European Sales Manager], [Employee].[Employee
Department].[Department Name].&[Sales].&[North American Sales Manager]}
)

And that works

If I try to define named sets:


WITH
SET [Date Set] AS '{[Date].[Fiscal].[Fiscal Semester].&[2004]&[1]}'
SET [Employee Set] AS '{[Employee].[Employee Department].[Department
Name].&[Sales].&[Sales Representative], [Employee].[Employee
Department].[Department Name].&[Sales].&[European Sales Manager],
[Employee].[Employee Department].[Department Name].&[Sales].&[North American
Sales Manager]}'

SELECT
{
[Measures].[Reseller Sales Amount]
}

ON COLUMNS,
NON EMPTY
{
NonemptyCrossJoin (
{[Product].[Product Model Categories].[All Products]},
{[Geography].[Geography].[All Geographies]}
)

}
ON ROWS
FROM [Channel Sales]
WHERE
(
[Date Set],
[Employee Set]
)


I get error "The definition of the Employee Set set contains a circular
reference." Not sure I understand that.


"ChrisHarrington" <charrington-at-activeinterface.com> wrote

Hi Chris,

The error message could be due to the fact that the "Where" clause of an
MDX query is resolved before Named Sets in the "With" clause, as
explained in Chapter 4 of MDX Solutions: "MDX Query Context and
Execution". So, maybe the context of the "Where" clause (which specifies
the named sets) is then applied to the computation of the named sets
themsleves, resulting in the circular reference. Of course, a set could
not be directly specified in the AS 2000 "Where" clause, so this
scenario did not exist.

One work-around would be to define intermediate string measures; but
there may be more straightforward solutions?

Member [Measures].[Date String] AS "{[Date].[Fiscal].[Fiscal
Semester].&[2004]&[1]}"
Member [Measures].[Employee String] AS "{[Employee].[Employee
Department].[Department
Name].&[Sales].&[Sales Representative], [Employee].[Employee
Department].[Department Name].&[Sales].&[European Sales Manager],
[Employee].[Employee Department].[Department Name].&[Sales].&[North
American
Sales Manager]}"

SELECT
{
[Measures].[Reseller Sales Amount]
}

ON COLUMNS,
NON EMPTY
{
NonemptyCrossJoin (
{[Product].[Product Model Categories].[All Products]},
{[Geography].[Geography].[All Geographies]}
)

}
ON ROWS
FROM [Channel Sales]
WHERE
(
StrToSet([Measures].[Date String]) as [Date Set],
StrToSet([Measures].[Employee String]) as [Employee Set]
)

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks Deepak, I'll try that approach also.

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote