The <SELECT slicer axis clause> expression must contain members in dimensions and hierarchies other than those referenced in the specified <SELECT query axis clause> expressions.

If an attribute in the cube is omitted from the specified <SELECT query axis clause> expressions and the <SELECT slicer axis clause> value, the attribute's default member is implicitly added to the slicer axis.

The NON VISUAL option in the subselect statement enables you to filter out members while keeping the true totals instead of filtered totals. This enables you to query for the top ten sales (persons/products/regions) and obtain the true total of sales for all queried members, instead of the total value of sales for the top ten returned. See the examples below for more information.

Calculated members can be included in <SELECT query axis clause> whenever the connection was opened using the connection string parameter subqueries=1; see Supported XMLA Properties (XMLA) and ConnectionString for parameter usage. An example is provided on calculated members in subselects.

Autoexists

When two or more attributes of the dimension are used in a SELECT statement, Analysis Services evaluates the attributes' expressions to make sure that the members of those attributes are properly confined to meet the criteria of all other attributes. For example, suppose you are working with attributes from the Geography dimension. If you have one expression that returns all members from the City attribute, and another expression that confines members from the Country attribute to all countries in Europe, then this will result in the City members being confined to only those cities that belong to countries in Europe. This characteristic of Analysis Services is called Autoexists and applies only to attributes in the same dimension. Autoexists only applies to attributes from the same dimension because it tries to prevent the dimension records excluded in one attribute expression from being included by the other attribute expressions. Autoexists can also be understood as the resulting intersection of the different attributes expressions over the dimension records. See the following examples below:

As per the following results, both sets (Top10SellingProducts, Preferred10Products) are the same

Reseller Sales Amount

Discount Amount

PCT Discount

Mountain-200

$14,356,699.36

$19,012.71

0.13%

Road-250

$9,377,457.68

$4,032.47

0.04%

Mountain-100

$8,568,958.27

$139,393.27

1.63%

Road-650

$7,442,141.81

$39,698.30

0.53%

Touring-1000

$6,723,794.29

$166,144.17

2.47%

Road-550-W

$3,668,383.88

$1,901.97

0.05%

Road-350-W

$3,665,932.31

$20,946.50

0.57%

HL Mountain Frame

$3,365,069.27

$174.11

0.01%

Road-150

$2,363,805.16

$0.00

0.00%

Touring-3000

$2,046,508.26

$79,582.15

3.89%

In the previous examples we created two sets: one as a calculated expression and the other as a constant expression. These examples illustrate the different flavors of Autoexists.

Autoexists can be applied deep or shallow to the expressions. The default setting is deep. The following example will illustrate the concept of deep Autoexists. In the example we are filtering Top10SellingProducts by [Product].[Product Line] attribute for those in [Mountain] group. Note that both attributes (slicer and axis) belong to the same dimension, [Product].

In the previous result set we have seven newcomers to the list of Top10SellingProducts and Mountain-200, Mountain-100 and HL Mountain Frame have moved to the top of the list. In the previous result set those three values were interspersed

This is called Deep Autoexists, because the Top10SellingProducts set is evaluated to meet the slicing conditions of the query. Deep Autoexists means that all expressions will be evaluated to meet the deepest possible space after applying the slicer expressions, the sub select expressions in the axis, and so on.

However, one might want to be able to do the analysis over the Top10SellingProducts as equivalent to Preferred10Products, as in the following example:

In the above results, the slicing gives a result that contains only those products from Preferred10Products that are part of the [Mountain] group in [Product].[Product Line]; as expected, because Preferred10Products is a constant expression.

This result set is also understood as shallow Autoexists. This is because the expression is evaluated before the slicing clause. In the previous example, the expression was a constant expression for illustration purposes in order to introduce the concept.

Autoexists behavior can be modified at the session level using the Autoexists connection string property. The following example begins by opening a new session and adding the Autoexists=3 property to the connection string. You must open a new connection in order to do the example. Once the connection is established with the Autoexist setting it will remain in effect until that connection is finished.

Examples

The following example returns the sum of the Measures.[Order Quantity] member, aggregated over the first eight months of calendar year 2003 that are contained in the Date dimension, from the Adventure Works cube.

To understand NON VISUAL, the following example is a query of [Adventure Works] to obtain [Reseller Sales Amount] figures in a table where the product categories are the columns and the reseller business types are the rows. Note that totals are given for both products and resellers.

The following SELECT statement:

select [Category].members on 0,

[Business Type].members on 1

from [Adventure Works]

where [Measures].[Reseller Sales Amount]

Produces the following results:

All Products

Accessories

Bikes

Clothing

Components

All Resellers

$80,450,596.98

$571,297.93

$66,302,381.56

$1,777,840.84

$11,799,076.66

Specialty Bike Shop

$6,756,166.18

$65,125.48

$6,080,117.73

$252,933.91

$357,989.07

Value Added Reseller

$34,967,517.33

$175,002.81

$30,892,354.33

$592,385.71

$3,307,774.48

Warehouse

$38,726,913.48

$331,169.64

$29,329,909.50

$932,521.23

$8,133,313.11

To produce a table with data only for theAccessories and Clothing products, the Value Added Reseller and Warehouse resellers, yet keeping the overall totals could be written as follows using NON VISUAL:

When compared with the previous results, you can observe that the [All Resellers] row now adds up to the displayed values for [Value Added Reseller] and [Warehouse] but that the [All Products] column shows the total value for all products, including those not displayed.

The following example demonstrates how to use calculated members in subselects to filter on them. To be able to reproduce this sample, the connection must be established using the connection string parameter subqueries=1.