New Functions Extending SQL-92

OLE DB Programmer's Reference

The following functions are new. They operate on a fully qualified member name. These functions are merely string manipulation functions and therefore are not aware of any metadata beyond the input fully qualified name.

PARENT(<fq_name>)

Returns the fully qualified parent name of <fq_name>. Semantically, this means the following: Output the substring of <fq_name> that is the result of deleting the rightmost period (.) and everything after it. If there are no periods in the input string, return NULL.

ANCESTOR(<fq_name>, <generation>)

Returns the ancestor of <fq_name> at <generation> levels above <fq_name>, as in the following example:

ANCESTOR(Geography.[All].USA.Kansas.Topeka, 1) = Geography.[All].USA.Kansas

Semantically, the output results from deleting the <generation> period and everything following it and then returning the resulting string. If LEVEL(<fq_name>) + 1 <= <generation>, the result is the dimension name.

LEVEL(<fq_name>)

Returns the zero-based level (root = 0) of the input member. Semantically, the output is equal to counting the number of periods in <fq_name> and decrementing the count by 1. If <fq_name> does not have any periods, –1 is returned.

REVERSE(<fq_name>)

Reverses <fq_name>. For example, "Geography.[All].USA.Kansas" is converted to "Kansas.USA.[All].Geography".

COMPONENT(<fq_name>, <level>)

Gives the fully qualified name of the ancestor of the input member at the specified level. The special level –1 can be used to get the dimension name. NULL is returned if <level> is less than –1, and ALL() is returned if <level> is greater than LEVEL(<fq_name>). For more information about ALL(), see "Special Value: ALL()."

1998-2001 Microsoft Corporation. All rights reserved.