Cube-Based MDX Calculations - Microsoft SQL Server 2008 Analysis Services - page 210

Now let's use dynamic named set rather than a static one:

1. Create a dynamic named set for finding our best customers, using the following
expression:

CREATE DYNAMIC SET [Warehouse and Sales].DynamicTopCustomers AS
'TopCount([Customer].[Customers].[Customer].members,5, [Measures].[Sales
Count])'
2. Execute query that should return the best customers in year 1997:

SELECT DynamicTopCustomers ON COLUMNS
FROM [Warehouse and Sales] WHERE
([Time].[Time].[Year].[1997],[Measures].[Sales Count])
3. Figure 12.14 shows the results of this query. Because our named set is dynamic, this
query returns the best customers in a context of the WHERE clause—member 1997 of
Time dimension.

Mary Francis Benigar James Horvat Jack Zucconi Ida Rodriguez Beradette Marschang

146 127 126 125 123

FIGURE 12.14 Analysis Services executes dynamic named set in context of the WHERE

clause.

4. You can also check that the result displayed in Figure 12.14 indeed shows the best
customers of 1997 by executing following query:

SELECT TopCount([Customer].[Customers].[Customer].members,5,
[Measures].[Sales Count]) ON COLUMNS
FROM [Warehouse and Sales] WHERE
([Time].[Time].[Year].[1997],[Measures].[Sales Count])

NOTE
Dynamic named sets are evaluated in a context of WHERE clause and SubSelect of
every query, but they are not evaluated in a context of every cell.

To reinforce this point, we modify a named set used in a query corresponding to Figure
12.12. We create a dynamic named set that contains a filter set and then use it in a query:

CREATE DYNAMIC
SET [Warehouse and Sales].FilterSet AS
Filter([Customer].[Customers].[Customer].members,
(Time.Time.CurrentMember,[Measures].[Sales Count])>10)
WITH MEMBER Measures.x AS 'COUNT(FilterSet)'
SELECT Measures.x ON COLUMNS,