I use the adventure works DW cube to make a test with sets.

I create a set like this :

CREATE SET CURRENTCUBE.[Product 10000]
AS Filter(Descendants([Product].[Product
Name].CurrentMember,[Product].[Product Name].[Product Name]), ([Due
Date].[Calendar Time].CurrentMember, [Measures].[Reseller Sales-Sales
Amount]) > 10000) ;

I want that this set return the list of product, for a define period,
with a minimum amount of sales.

If I execute this query :
SELECT { [Due Date].[Calendar Time].[Calendar Year].&[2003] } ON
COLUMNS ,
NON EMPTY { [Product 10000] } ON ROWS
FROM [Analysis Services Tutorial]
WHERE ( [Measures].[Reseller Sales-Sales Amount] )
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

I have in the result all the products and not these with more than
10000 € of sales for the choosen year 2003. If I replace in the set
[Due Date].[Calendar Time].CurrentMember by a specific year, I have the
good results. I think that when the set is build, SSAS use the default
member of all the dimensions that are not specified in the set. Is it
true ? Do we have a solution to use in a set the current members chosed
by the user.

thanks for all information

It's true that a set is evaluated once and is reused. So I think a query
scope set is proper to meet your requirements.
In this case two ideas might be considered.

1.
WITH SET [Analysis Services Tutorial].[Product 10000] AS ....[2003]....
SELECT ....
FROM [Analysis Services Tutorial]
WHERE ...

2.
WITH SET [Analysis Services Tutorial].[Product 10000] AS ....
SELECT ....
FROM (SELECT {[2003]} ON 0 FROM [Analysis Services Tutorial])
WHERE ...

Ohjoo

"baty" <thierry.bacchetta (AT) fr (DOT) mcd.com> wrote

I use the adventure works DW cube to make a test with sets.

I create a set like this :

CREATE SET CURRENTCUBE.[Product 10000]
AS Filter(Descendants([Product].[Product
Name].CurrentMember,[Product].[Product Name].[Product Name]), ([Due
Date].[Calendar Time].CurrentMember, [Measures].[Reseller Sales-Sales
Amount]) > 10000) ;

I want that this set return the list of product, for a define period,
with a minimum amount of sales.

If I execute this query :
SELECT { [Due Date].[Calendar Time].[Calendar Year].&[2003] } ON
COLUMNS ,
NON EMPTY { [Product 10000] } ON ROWS
FROM [Analysis Services Tutorial]
WHERE ( [Measures].[Reseller Sales-Sales Amount] )
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

I have in the result all the products and not these with more than
10000 ¢æ of sales for the choosen year 2003. If I replace in the set
[Due Date].[Calendar Time].CurrentMember by a specific year, I have the
good results. I think that when the set is build, SSAS use the default
member of all the dimensions that are not specified in the set. Is it
true ? Do we have a solution to use in a set the current members chosed
by the user.

thanks for all information