Adding Calculations to the Cube - Expert Cube Development with SSAS Multidimensional Models - page 171

Named sets
Another very interesting topic slightly related to calculated members is that of
named sets. A named set is simply a set of members or tuples to which we assign a
name. We define named sets to make it easier for users to build their queries, and
also to help us as developers write more readable code.
Regular named sets
Let's take a look at an example of how named sets can be used. Our user might
want to build an Excel report that shows detailed information about the sales of
the current month, the sales of the previous one, and the total sales of the last three
years. Without a named set, at each start of the month, the user would need to
update the dates selected in the report in order to display the most recent month
with data. In order to avoid having to do this, we can define a named set containing
exactly the date range the user wants to see in the report that will never need manual
updating. Here's how to do this:
First of all, since the Date Order dimension contains dates in the future that do not
contain any sales, we first need to define the set of months where there are sales. This
is easy to do using the NonEmpty function:
CREATE HIDDEN SET ExistingSalePeriods AS
NonEmpty (
[Date Order].[Calendar].[Month].Members,
Measures.[Sales Amount]
);
We define the set as HIDDEN because we do not want to make it visible to the user; we
are only going to use it as an intermediate step towards constructing the set we want.
Next, since we are interested in the latest month where there are sales, we can simply
define a new set that contains the last item in the ExistingSalePeriods :
CREATE SET LastSaleMonth AS
Tail (ExistingSalePeriods, 1);
This set, even if it is another step towards our ultimate goal, might be useful for the
user to see, so we have left it visible. Our final set will contain members at the Year
level, so we need to define a new set containing the latest year containing sales:
CREATE SET LastSaleYear AS
Ancestor (
LastSaleMonth.Item (0),
[Date Order].[Calendar].[Calendar Year]
);