Mdx function set

Mdx function reference (MDX) -- msdnmdx function reference (MDX)

Microsoft SQL Server Analysis Services allows multi-dimensional expressions (MDX)
Use functions in syntax. Functions can be used in any valid MDX statement and are often used for queries, custom summary definitions, and other computations. This section introduces the analysis
The MDX function attached to services.

You can use the following tables to search for functions based on the returned values, or you can select functions based on names in alphabetical directories.

Array Functions

Function	Description
Settoarray (MDX)	Converts one or more sets to an array for use in user-defined functions.

Layered Functions

Function	Description
Hierarchy (MDX)	Returns a hierarchy containing a specified member or level.
Dimension (MDX)	Returns a dimension that contains a specified Member, level, or level.
Dimensions (MDX)	Returns the level specified by a numeric or string expression.

Level functions

Function	Description
Level (MDX)	Returned member level.
Levels (MDX)	Returns the level specified by the numeric expression in the dimension or hierarchy, or returns the level specified by the string expression.

Logical Functions

Function	Description
Isancestor (MDX)	Returns whether a specified member is the ancestor of another specified member.
Isempty (MDX)	Returns whether the calculated result of an expression is a null unit value.
Isgeneration (MDX)	Returns whether the specified member is in the specified generation.
Isleaf (MDX)	Returns whether the specified member is a leaf member.
Issibling (MDX)	Returns whether a specified member is a same-level member of another specified member.

Member Functions

Function	Description
Ancestor (MDX)	Returns the ancestor of a member at a specified level or distance.
Closingperiod (MDX)	Returns the last same level of the child of a member at the specified level.
Cousin (MDX)	Returns the child member with the specified child member having the same relative position under the parent member.
Currentmember (MDX)	Returns the current member of the specified dimension or hierarchy during the iteration.
Datamember (MDX)	Return the data members generated by the system and associated with non-leaf members of a dimension.
Defaultmember (MDX)	Returns the default member of a dimension or hierarchy.
Firstchild (MDX)	Returns the first child Member of a member.
Firstsibling (MDX)	Returns the first child Member of the parent member.
Item (member) (MDX)	Returns the members in the specified tuples.
Lag (MDX)	Returns the Members whose positions are prior to the specified members and whose front digits are specified digits.
Lastchild (MDX)	Returns the last child Member of the specified member.
Lastsibling (MDX)	Returns the last child Member of the parent Member of the specified member.
Lead (MDX)	Returns a Member whose position is later than the specified member and whose number of digits is later than the specified member.
Linkmember (MDX)	Returns a member equivalent to a specified member in a specified hierarchy.
Members (string) (MDX)	Returns the member specified by the string expression.
Nextmember (MDX)	Returns the next member of the specified member level.
Openingperiod (MDX)	Returns the first same level among the offspring of a specified level (or a specified member.
Parallelperiod (MDX)	Returns a member with the same relative position as the specified member in the previous period.
Parent (MDX)	Returns the parent Member of a member.
Prevmember (MDX)	Returns the previous Member of the specified member level.
Strtomember (MDX)	Returns the members specified by the MDX string.
Unknownmember (MDX)	Returns an unknown member associated with a level or member.
Validmeasure (MDX)	A valid measurement value in a virtual cube is returned by forcing an unsuitable dimension to its top-level.

Numeric Functions

Function	Description
Aggregate (MDX)	Returns a scalar value, which is obtained by aggregating the measurement values of the tuples in the specified set, or using a specified value expression to compute the tuples in the specified set (optional ).
AVG (MDX)	Returns the average value of the measured value calculated for the specified set, or the average value obtained for the specified set by an optional numeric expression.
Calculationcurrentpass (MDX)	Returns the current computing transfer of the cube for the specified query context.
Calculationpassvalue (MDX)	Returns the value obtained by passing the specified calculation on the multi-dimensional dataset using the MDX expression.
Coalesceempty (MDX)	Merge empty unit values into numbers or strings and return the merged values.
Correlation (MDX)	Returns the correlation coefficient of the two sequences that evaluate the set value.
Count (dimension) (MDX)	Returns the number of dimensions in a cube.
Count (level of hierarchy) (MDX)	Returns the number of levels in a dimension or hierarchy.
Count (SET) (MDX)	Returns the number of units in the set.
Count (tuples) (MDX)	Returns the number of dimensions in the tuples.
Covariance (MDX)	Returns the population covariance of the two sequences obtained from the set using the population formula with deviations.
Covariancen (MDX)	Returns the sample covariance of the two sequences obtained from the set using the population formula without deviation.
Distinctcount (MDX)	Returns the number of non-repeated non-null tuples in the set.
IIF (MDX)	Returns one of the two values determined by the logical test.
Linregintercept (MDX)	Perform linear regression calculation on the dataset and return the regression line formula.Y = ax + BThe intercept value.
Linregpoint (MDX)	Perform linear regression calculation on the dataset and return the regression line formula.Y = ax + BInYValue.
Linregr2 (MDX)	Perform linear regression calculation on the dataset and return the coefficient of determination (R2.
Linregslope (MDX)	Perform linear regression calculation on the dataset and return the regression line formula.Y = ax + BThe value of the medium slope.
Linregvariance (MDX)	Perform linear regression calculation on the dataset and return the regression line formula.Y = ax + BRelated variance.
Lookupcube (MDX)	Returns the value obtained from another specified multi-dimensional dataset in the same database using the MDX expression.
Max (MDX)	Returns the maximum value of the value expression for the set.
Median (MDX)	Returns the value of the value expression for the set.
Min (MDX)	Returns the minimum value of the value expression for the set.
Ordinal (MDX)	Returns the ordinal value associated with a certain level from scratch.
Predict (MDX)	Returns the value obtained from the data mining model using a numerical expression.
Rank (MDX)	Returns the ranking of the specified tuples in the specified set (starting from 1 ).
Rollupchildren (MDX)	Use the specified unary operator to aggregate the values of the child members of a specified member to return the generated values.
Stddev (MDX)	The alias of STDev (MDX.
Stddevp (MDX)	The alias of stdevp (MDX.
STDev (MDX)	Returns the sample standard deviation obtained from the set using the population formula without deviation.
Stdevp (MDX)	Returns the population standard deviation obtained from the set using a population formula with deviations.
Strtovalue (MDX)	Returns the value specified by the MDX string.
Sum (MDX)	Returns the sum of values obtained from a set using a numeric expression.
Value (MDX)	Returns the value of a metric value.
VaR (MDX)	Returns the sample variance obtained from the set using the population formula without deviation.
Variance (MDX)	The alias of VaR (MDX.
Variancep (MDX)	The alias of varp (MDX.
Varp (MDX)	Returns the population variance obtained from the set using a population formula with deviations.

Set Functions

Function	Description
Addcalculatedmembers (MDX)	Returns the set generated by adding the computing member to the specified set.
Allmembers (MDX)	Returns a set of all members (including computing members) of a specified dimension, hierarchy, or level.
Ancestors (MDX)	Returns a set composed of all the ancestors of a specified level or distance of a member.
Ascendants (MDX)	Returns the ancestor set of the specified member (including the member itself ).
Axis (MDX)	Returns the set defined in the axis.
Bottomcount (MDX)	Sorts the set in ascending order and returns the minimum number of tuples.
Bottompercent (MDX)	Sorts the set in ascending order and returns a minimum tuples. The cumulative sum of the set is equal to or less than the specified percentage.
Bottomsum (MDX)	Sorts the set in ascending order, and returns a minimum tuples. The total number of tuples is equal to or less than the specified value.
Children (MDX)	Returns the child of a specified member.
Crossjoin (MDX)	Returns the cross product of one or more sets.
Currentordinal (MDX)	Returns the number of current iterations in the set during the iteration process.
Descendants (MDX)	Returns the descendant set of a member at the specified level or distance. You can select to include or not include descendants of other levels.
Distinct (MDX)	Returns the set that is obtained after the duplicate tuples are deleted from the specified set.
Drilldownlevel (MDX)	Deepen the members of a set to the lower level indicated by the set, or to the lower level of any specified member level indicated by the set.
Drilldownlevelbottom (MDX)	Deepen the bottom-layer members at a specified level to the next level.
Drilldownleveltop (MDX)	Deepen the top member of a specified level to the next level.
Drilldownmember (MDX)	Deepen the members in the intersection of the first specified set and the second specified set. In addition, this function can deepen the set of tuples.
Drilldownmemberbottom (MDX)	Deepen the members in the intersection of the first specified set and the second specified set, and limit the number of members of the result set to the specified number. In addition, this function can also deepen the set of tuples.
Drilldownmembertop (MDX)	Deepen the members in the intersection of the first specified set and the second specified set, and limit the number of members of the result set to the specified number. In addition, this function can deepen the set of tuples.
Drilluplevel (MDX)	Indicates the members of a set at a specified level or lower.
DRDs pmember (MDX)	Indicates the members in the intersection of the first specified set and the second specified set.
Mongot (MDX)	Find different items between two sets, and you can choose to retain repeated items.
Exists (MDX)	Returns a group of Members from a set. The group members coexist with one or more tuples in one or more other sets.
Extract (MDX)	Returns the set composed of tuples In the extracted Dimension Element.
Filter (MDX)	Returns the set that is obtained after filtering the specified set according to the search condition.
Generate (MDX)	Apply a set to each member of another set, and then calculate the Union set for the obtained set. Alternatively, this function returns the concatenation string created by calculating the string expression for the set.
Head (MDX)	Returns the first few elements of a specified number in the Set, and retains the repeated items.
Hierarchize (MDX)	Sorts members of a set in a hierarchy.
Intersect (MDX)	Returns the intersection of two input sets. You can choose to retain duplicate items.
Lastperiods (MDX)	Returns the member set before a specified member.
Members (SET) (MDX)	Returns a member set in a dimension, level, or level.
MTD (MDX)	According to the year constraints in the time dimension, a group of same-level members are returned from the level of a given Member, starting from the first same-level member to the given member.
Nametoset (MDX)	Returns the set of members specified by the MDX string.
Nonemptycrossjoin (MDX)	Returns the cross product of one or more sets in the form of a set, excluding empty tuples and tuples that are not associated with data in fact data tables.
Order (MDX)	Arrange the members of a specified set. You can choose to retain or disrupt the original hierarchy.
Periodstodate (MDX)	According to the specified level constraints in the time dimension, a group of same-level members are returned from the level where a given Member is located, starting from the first same-level member to the given member.
QTd (MDX)	In the time dimensionQuarterLevel constraints: return the set of same-level members from the same level as a given Member, starting from the first same-level member to the given member.
Siblings (MDX)	Returns the same level of the specified member, including the member itself.
Stripcalculatedmembers (MDX)	Returns the set generated by deleting the computing member from the specified set.
Strtoset (MDX)	Returns the set specified by the MDX string.
Subset (MDX)	Returns the subset of the tuples in the specified set.
Tail (MDX)	Returns the subset at the end of the set.
Toggledrillstate (MDX)	Switch the drilling status of the member.
Topcount (MDX)	Sorts the set in descending order and returns the maximum number of elements.
Toppercent (MDX)	Sorts the set in descending order, and returns a maximum set of tuples. The cumulative total of the set is equal to or less than the specified percentage.
Topsum (MDX)	Sorts the set and returns the elements whose total value reaches at least the top of the specified value.
Union (MDX)	Returns the union of two sets. You can choose to retain duplicate items.
Unorder (MDX)	Deletes any forced sorting from the specified set.
Visualtotals (MDX)	Returns the set generated by dynamically calculating the total number of child members in the specified set. You can select to apply a certain mode to the parent member name in the obtained unit set.
WTD (MDX)	According to the week-level constraints in the time dimension, a group of same-level members is returned from the level of a given Member, starting from the first same-level member to the given member.
YTD (MDX)	In the time dimensionYearLevel constraints: return the set of same-level members from the same level as a given Member, starting from the first same-level member to the given member.

String Functions

Function	Description
Calculationpassvalue (MDX)	Returns the value obtained by passing the specified calculation on the multi-dimensional dataset using the MDX expression.
Coalesceempty (MDX)	Merge empty unit values into numbers or strings and return the merged values.
Generate (MDX)	Apply a set to each member of another set, and then calculate the Union set for the obtained set. Alternatively, this function returns the concatenation string created by calculating the string expression for the set.
IIF (MDX)	Returns one of the two values determined by the logical test.
Lookupcube (MDX)	Returns the value obtained from another specified multi-dimensional dataset in the same database using the MDX expression.
Membertostr (MDX)	Returns the MDX Format String corresponding to the specified member.
Name (MDX)	Returns the name of a dimension, level, or member.
Properties (MDX)	Returns a string that contains the member attribute values or a strongly typed value.
Settostr (MDX)	Returns the MDX Format String corresponding to the specified set.
Tupletostr (MDX)	Returns the MDX Format String corresponding to the specified tuples.
Uniquename (MDX)	Returns the unique name of the specified dimension, level, or member.
Username (MDX)	Returns the domain name and User Name of the current connection.

Subcube Functions

Function	Description
This (MDX)	Returns the current subcube.
Leaves (MDX)	Returns the leaf member set in the specified dimension, member, or tuples.

Tuples

Function	Description
Current (MDX)	Returns the current set of tuples In the iteration process.
Item (tuples) (MDX)	Returns the tuples of a set.
Root (MDX)	Returns a tuple consistingAllMembers.
Strtotuple (MDX)	Returns the tuples specified by the MDX string.

Other functions

Function	Description
Error (MDX)	If an error is thrown, you can choose to provide the specified error message as needed.