Specifying the Default Member

Every attribute in a dimension in Microsoft SQL Server Analysis Services has a default member, which you can specify by using the DefaultMember property for an attribute. This setting is used to evaluate expressions if an attribute is not included in a query. If a query specifies a hierarchy in a dimension, the default members for the attributes in the hierarchy are ignored. If a query does not specify a hierarchy in a dimension, the DefaultMember settings for dimension attributes take effect.

If the DefaultMember setting for an attribute is blank and its IsAggregatable property is set to True, the default member is the All member. If the IsAggregatable property is set to False, the default member is the first member of the first visible level.

The DefaultMember setting for an attribute applies to every hierarchy in which the attribute participates. You cannot use different settings for different hierarchies in a dimension. For example, if the [1998] member is the default member for a [Year] attribute, this setting applies to every hierarchy in the dimension. The DefaultMember setting in this case cannot be [1998] in one hierarchy and [1997] in a different hierarchy.

If you define a default member for a particular level in a hierarchy that does not aggregate naturally, you must define default members in all levels above that level in the hierarchy. For example, in the hierarchy All-Countries–Climate, you cannot define a default member for Climate unless you define a default member for Countries. Failing to do so creates query-time errors.

When levels in a hierarchy aggregate naturally, you can define a default member for any attribute in the hierarchy without regard to other attributes in the hierarchy. For example, in the hierarchy Country–Province–City, you can define a default member for City such as [City].[Montreal] without defining the default member for State or for Country.

See Also

Concepts