Using WITH to Define Calculated Members, Sets, and Cache

Within an MDX query, you can use the WITH clause to specify a calculated member (or measure), a user-defined set, or cache that can be used only in the current query (that is, only within query scope). To define these items for use in session scope, use the data definition language (DDL) statements described in the subtopics of PivotTable Service. To define calculated members and user-defined sets for use in global scope, use the Calculated Member Builder.

An MDX query with the optional WITH clause has the following basic syntax:

WITH <single_formula_specification> [<single_formula_specification>...]

SELECT <axis_specification> [, <axis_specification>...]

FROM <cube_specification>

WHERE <slicer_specification>

The breakdown of the <single_formula_specification> syntax is:

<single_formula_specification> ::= <member_specification> | <set_specification> | <cache_specification>

The <member_specification> syntax is used to define a calculated member or measure, and it breaks down as follows:

<member_specification> ::= MEMBER <parent_of_member>.<member_name> AS '<value_expression>'

The <value_expression> value can include functions that support MDX syntax.

The <set_specification> syntax is used to define a set, and it breaks down as follows:

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

Following the WITH clause, the <member_name> or <set_name> value is used to reference the calculated member or set.

The <cache_specification> syntax is used to populate cache with a cube slice defined by sets of members, and it breaks down as follows:

<cache_specification> ::= CACHE AS '(<set> [,<set>])'

The <set> value, in both the <set_specification> syntax and the <cache_specification> syntax, can include functions that support MDX syntax.

When using <set> within the <cache_specification> syntax, the following rules apply:

• Each <set> must contain members from only one dimension. Each member must be distinct.
• Each <set> must be from a different dimension.
• The <set> cannot contain measures.

Examples

The following two MDX queries use the WITH clause to modify the dataset described in Cube and Dataset Example. A calculated member is defined to create new columns that sum the USA and Japan Sales. A set is defined to produce the y-axis but does not modify the dataset.

The revised dataset to be produced by each of the two queries is:

Example 1

The following MDX query produces the preceding dataset by defining a calculated member named USA_and_Japan that adds USA and Japan Sales. The calculated member is used in the x-axis (COLUMNS).

WITH MEMBER All_Countries.USA_and_Japan AS 'SUM({USA, Japan})'

SELECT CROSSJOIN({Smith, Jones},

   {USA_North.CHILDREN, USA_South, Japan, USA_and_Japan}) ON COLUMNS,

   {Qtr1.CHILDREN, Qtr2, Qtr3, Qtr4.CHILDREN} ON ROWS

FROM SalesCube

WHERE (Sales, [1997], Products.All)

Example 2

The following MDX query adds a set definition to the previous example but produces the same dataset. The set is named Expand_Qtrs_1_4 and contains the previously used members of the Quarters dimension. The set is used in the y-axis (ROWS).

WITH MEMBER All_Countries.USA_and_Japan AS 'SUM({USA, Japan})'

     SET Expand_Qtrs_1_4 AS '{Qtr1.CHILDREN, Qtr2, Qtr3, Qtr4.CHILDREN}'

SELECT CROSSJOIN({Smith, Jones},

   {USA_North.CHILDREN, USA_South, Japan, USA_and_Japan}) ON COLUMNS,

   Expand_Qtrs_1_4 ON ROWS

FROM SalesCube

WHERE (Sales, [1997], Products.All)