SQL Server - More than one slices from same dimension in Where clause not working

Asked By rupnicegu

17-Nov-09 03:35 AM

SELECT NON EMPTY {TopCount({Crossjoin({[Measures].[Average Sales
Amount]},{[Date].[Fiscal].[(All)].AllMembers})},20,[Measures].[Average
Sales Amount])} ON COLUMNS,
NON EMPTY {hierarchize(TopCount({[Geography].[Geography].
[(All)].AllMembers},20,[Measures].[Average Sales Amount]))} ON ROWS
FROM "Adventure Works"
WHERE ([Product].[Color].&[Black],[Product].[Color].&[Red])

Executing the query ...
The 'Color' hierarchy appears more than once in the tuple.
Execution complete

The issue is with using more than once slices from same dimension in
where clause e.g. ([Product].[Color].&[Black],[Product].[Color].&
[Red])

I have to use more than one slices from same dimension. But the MDX is
failing with error, please suggest how to acheive this?

Best Regards,
Rupesh

18-Nov-09 03:25 AM

In ([Product].[Color].&[Black],[Product].[Color].&[Red]), the () is a
tuple which needs members from different hierarchies.

If you only want to aggregate them, you can simply change ([Product].
[Color].&[Black],[Product].[Color].&[Red]) to be {[Product].[Color].&
[Black],[Product].[Color].&[Red]}.

19-Nov-09 09:32 AM

Thanks a lot. It really helped!

Best Regards,
Rupesh