Calculated Members in Subselects and Subcubes

Calculated Members in Subselects and Subcubes

The SubQueries connection string property in ConnectionString or the DBPROPMSMDSUBQUERIES property in Supported XMLA Properties (XMLA) defines the behavior or allowance of calculated members or calculated sets on subselects or subcubes. In the context of this document, subselect refers to subselects and subcubes unless otherwise stated.

The SubQueries property allows the following values.

Using values of 1 or 2 in the SubQueries property allows calculated members to be used to filter the returning subspace of subselects.

An example will help clarify the concept; first a calculated member must be created and then a subselect query issued to show the above mentioned behavior.

The following sample creates a calculated member that adds [Seattle Metro] as a city to the [Geography].[Geography] hierarchy under Washington state.

To run the example, the connection string must contain the SubQueries property with a value of 1 and all MDX statements must be run in the same session.

First issue the following MDX expression:

//Remember to set Subqueries=1 in the connection string prior
//to issue these commands
//--> AS2008 behavior
CREATE MEMBER [Adventure Works].[Geography].[Geography].[State-Province].&[WA]&[US].[Seattle Metro Agg] 
   AS  AGGREGATE( 
                 { 
                   [Geography].[Geography].[City].&[Bellevue]&[WA]
                 , [Geography].[Geography].[City].&[Issaquah]&[WA]
                 , [Geography].[Geography].[City].&[Redmond]&[WA]
                 , [Geography].[Geography].[City].&[Seattle]&[WA]
                 }
                )  

Then issue the following MDX query to see calculated members allowed in subselects.

Select [Date].[Calendar Year].members on 0,
       [Geography].[Geography].allmembers on 1
from (Select {[Geography].[Geography].[State-Province].&[WA]&[US].[Seattle Metro Agg]} on 0 from [Adventure Works])
Where [Measures].[Reseller Sales Amount]

The obtained results are:

As said before, the ascendants of [Seattle Metro] do not exist in the returned subspace, when SubQueries=1, hence [Geography].[Geography].allmembers only contains the calculated member.

If the example is run using SubQueries=2, in the connection string, the obtained results are:

As said before, when using SubQueries=2, the ascendants of [Seattle Metro] exist in the returned subspace but no values exist for those members because there is no regular members to provide for the aggregations. Therefore, NULL values are provided for all ascendant members of the calculated member in this example.

To understand the above behavior you need to remember the fact that calculated members do not contribute to the aggregations of their parents as regular members do, see Calculated Members and Named Sets; the former implies that filtering by calculated members alone will lead to empty ascendants because there are no regular members to contribute to the aggregated values of the resulting subspace. If you add regular members to the filtering expression then the aggregated values will come from those regular members. Continuing with the above example, if the cities of Portland, in Oregon, and the city of Spokane, in Washington, are added to the same axis where the calculated member appears; as shown in the next MDX expression:

Select [Date].[Calendar Year].members on 0,
       [Geography].[Geography].allmembers on 1
from (Select {
               [Seattle Metro Agg]
             , [Geography].[Geography].[City].&[Portland]&[OR]
             , [Geography].[Geography].[City].&[Spokane]&[WA]
             } on 0 from [Adventure Works]
     )
Where [Measures].[Reseller Sales Amount]

The following results are obtained.

In the above results the aggregated values for [All Geographies], [United States], [Oregon] and [Washington] come from aggregating over the descendants of &[Portland]&[OR] and &[Spokane]&[WA]. Nothing comes from the calculated member.

Remarks

See Also

Reference

Concepts

Other Resources