Using WITH to Create Named Sets

Using WITH to Create Named Sets

The WITH keyword is included as part of the MDX SELECT statement, to allow construction of named sets as part of an MDX query.

The following syntax is used to add the WITH keyword to the MDX SELECT statement:

[WITH <formula_specification>]
      [, <formula_specification>]
SELECT [<axis_specification>
       [, <axis_specification>...]]
  FROM [<cube_specification>]
[WHERE [<slicer_specification>]]

The <formula_specification> value for named sets is further broken out in the following syntax definition:

<formula_specification> ::= SET <set_name> AS '<set>'

The <set_name> parameter contains the alias for the named set. The <set> parameter contains the set expression to which the named set alias will refer.

For example, the [ChardonnayChablis] named set is used to refer specifically to all of the Chardonnay and Chablis wine members in the Product dimension of the FoodMart 2000 database. The syntax for the named set is depicted in the following example:

WITH SET [ChardonnayChablis] AS
   '{[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Good].[Good Chardonnay],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Pearl].[Pearl Chardonnay],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Portsmouth].[Portsmouth Chardonnay],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Top Measure].[Top Measure Chardonnay],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Walrus].[Walrus Chardonnay],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Good].[Good Chablis Wine],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Pearl].[Pearl Chablis Wine],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Portsmouth].[Portsmouth Chablis Wine],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Top Measure].[Top Measure Chablis Wine],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Walrus].[Walrus Chablis Wine]}'

You can also use MDX functions in the set expression used to create a named set. The following MDX query example uses the Filter, CurrentMember, Name, and InStr functions to create the [ChardonnayChablis] named set, as used in earlier MDX query examples in this topic.

WITH SET [ChardonnayChablis] AS
   'Filter([Product].Members, (InStr(1, [Product].CurrentMember.Name, "chardonnay") <> 0) OR (InStr(1, [Product].CurrentMember.Name, "chablis") <> 0))'

SELECT
   [ChardonnayChablis] ON COLUMNS,
   {Measures.[Unit Sales]} ON ROWS
FROM Sales