MDX Function Reference

flex.bi uses Mondrian OLAP reporting engine and MDX query language implementation. In addition to Mondrian provided MDX functions flex.bi defines additional MDX functions which can be used in calculated member formulas. Please start with an introduction about calculated members to learn how to create calculated members and MDX calculation formulas.

This page lists all supported MDX functions grouped by their category of return value, or you can select function by name from the alphabetical list in the table of contents (in the left navigation sidebar).

Operators

Operator	Description

Operator	Description
CAST	The CAST operator converts scalar expressions to other types, NUMERIC, STRING, or BOOLEAN. The expression is `CAST (<expression> AS <type>)`.
IS	Compares and returns whether two objects (Member, Level, Hierarchy, Dimension, Tuple) are the same.
MATCHES	The MATCHES and NOT MATCHES operators compare a string with another string or regular expression.
+	Adds two numbers
-	Substracts two numbers. Or returns the negative of a number
*	Multiplies two numbers
/	Divides two numbers
=	Compares and returns whether two expressions are equal.
\|\|	Concatenates two strings

Date Functions

Function	Description

Function	Description
DateAdd	Returns the date plus the specified number of the chosen time units, year (yyyy), quarter (q), month (m), week (ww), day (d), hour (h), minute (n).
DateAddDays	Returns the date plus the specified number of days.
DateAddWorkdays	Returns the date plus the specified number of workdays.
DateParse	Returns the date that is parsed from a string argument.
DateSerial	Returns the date for the specified year, month, and day.
DateToTimestamp	Transforms date and time from date format to timestamp format (integer).
DateWithoutTime	Returns the date without the time from the provided date or string argument.
getDate	Similar to get function but will always return the member property value as a datetime value.
NextStartDate	Returns the start date of the next time period.
Now	Returns the current date and time according your flex.bi server system date and time.
StartDate	Returns the time period start date.
TimestampToDate	Transforms date and time from timestamp format (integer) to date format.

Hierarchy Functions

Function	Description

Function	Description
CurrentHierarchy	Returns the current Time dimension hierarchy (default or Weekly) based on currently used hierarchy.
Hierarchy	Returns the hierarchy that contains a specified member or level.
Dimension	Returns the dimension that contains a specified member, level, or hierarchy.
Dimensions	Returns a hierarchy specified by a numeric or string expression.

Level Functions

Function	Description

Function	Description
Level	Returns the level of a member.
Levels	Returns the level whose position in a dimension or hierarchy is specified by a numeric expression or whose name is specified by a string expression.

Logical Functions

Function	Description

Function	Description
AnyDateInPeriod	Returns if any date from comma separated string is between time period start and end dates.
IsEmpty	Determines if an expression evaluates to the empty cell value.
DateAfterPeriodEnd	Returns if date is after time period end date.
DateBeforePeriodEnd	Returns if date is before time period end date.
DateBetween	Returns if date is between other two dates.
DateInPeriod	Returns if date is between time period start and end dates.
getBoolean	Similar to get function but will always return the member property value as a boolean `true` or `false` value.

Member Functions

Function	Description

Function	Description
Ancestor	Returns the ancestor of a member at a specified level or distance.
ClosingPeriod	Returns the last sibling among the descendants of a member at a specified level.
CurrentDateMember	Returns the Time dimension hierarchy level member which contains current date.
CurrentHierarchyMember	Returns current Time dimension hierarchy (default or Weekly) member based on currently used hierarchy.
CurrentMember	Returns the current member along a specified dimension or hierarchy during iteration.
CurrentUserEmail	Returns an email of the user which is currently using flex.bi
CurrentUserName	Returns a name of the user which is currently using flex.bi
DataMember	Returns the system-generated data member that is associated with a nonleaf member of a dimension.
DateMember	Return Time dimension level member that matches the provided date.
DateMembersBetween	Returns Time dimension level members between two dates.
DefaultMember	Returns the default member of a dimension or hierarchy.
FirstChild	Returns the first child of a member.
FirstSibling	Returns the first child of the parent of a member.
FuturePeriods	Returns a set of future time dimension members starting from the given Time member.
GetLinkedMember	Returns member with a specified key value in the same dimension level or specific hierarchy level.
GetLinkedMembers	Returns members with specified keys in the same dimension level or specific hierarchy level.
GetLinkedMemberName	Returns name of linked member with a specified key value in the same dimension level or specific hierarchy level.
GetLinkedMemberNames	Returns names of members with specified keys in the same dimension level or specific hierarchy level.
GetMemberByKey	Returns level member with specified key value.
GetMemberNamesByKeys	Returns names of level members with the specified key values.
Item	Returns a member from a specified tuple.
Lag	Returns the member that is a specified number of positions before a specified member along the member's dimension.
LastChild	Returns the last child of a specified member.
LastSibling	Returns the last child of the parent of a specified member.
Lead	Returns the member that is a specified number of positions following a specified member along the member's dimension.
Members	Returns a member specified by a string expression.
NextMember	Returns the next member in the level that contains a specified member.
OpeningPeriod	Returns the first sibling among the descendants of a specified level, optionally at a specified member.
ParallelPeriod	Returns a member from a prior period in the same relative position as a specified member.
Parent	Returns the parent of a member.
PrevMember	Returns the previous member in the level that contains a specified member.
PreviousPeriods	Returns a set of previous time dimension members for calculation of cumulative sums.
StrToMember	Returns the member specified by an MDX–formatted string.

Numeric Functions

Function	Description

Function	Description
Abs	Returns the absolute value of a number.
Aggregate	Returns a calculated value using the appropriate aggregate function, based on the context of the query.
Avg	Returns the average value of a numeric expression evaluated.
CoalesceEmpty	Coalesces an empty cell value to a number or string and returns the coalesced value.
ConstantColumnValue	Calculates the value only once per report execution and per each column and stores those values in the cache
ConstantValue	Calculates the value once per report execution and stores it as one value in the cache
Correlation	Returns the correlation coefficient of two series evaluated over a set.
Count	Returns the number of tuples in a set.
Covariance	Returns the population covariance of two series evaluated over a set, using the biased population formula.
CovarianceN	Returns the sample covariance of two series evaluated over a set, using the unbiased population formula.
CumulativeSum	Calculates the sum of visible row values cumulatively
DateCompare	Returns -1 if first date is less than second date, 1 if first date is greater than second date, and 0 if dates are equal.
DateDiffDays	Returns difference in days from first date to second date.
DateDiffWorkdays	Returns difference in workdays from first date to second date.
DateDiffHours	Returns difference in hours from first date to second date.
DateDiffMinutes	Returns difference in minutes from first date to second date.
DateDiffWorkhours	Returns difference in workhours from first date to second date.
FirstQ	Returns the 1st quartile value of a numeric expression evaluated over a set.
getNumber	Similar to get function but will always return the member property value as a numeric value.
IIf	Returns one of two values determined by a logical test.
Int	Returns the next lowest integer
Len	Returns the number of characters in a string
LinRegIntercept	Calculates the linear regression of a set and returns the value of b in the regression line y = ax + b.
LinRegPoint	Calculates the linear regression of a set and returns the value of y in the regression line y = ax + b.
LinRegR2	Calculates the linear regression of a set and returns R2 (the coefficient of determination).
LinRegSlope	Calculates the linear regression of a set and returns the variance associated with the regression line y = ax + b.
Max	Returns the maximum value of a numeric expression evaluated over a set.
Median	Returns the median value of a numeric expression evaluated over a set.
Min	Returns the minimum value of a numeric expression evaluated over a set.
NonZero	Returns numeric value if non-zero.
Ordinal	Returns the zero-based ordinal value associated with a level.
Percentile	Returns the value of the tuple that is at a given percentile of a set.
Power	Performs an arithmetic operation that raises one number by another number.
PreviousRowValue	Returns the expression value of the preceding row (row above).
Rank	Returns the one-based rank of a specified tuple in a specified set.
Round	Rounds the values in its first argument to the specified number of decimal places.
SparklineData	Provides a JSON result that can be used with flex.bi Sparkline formatter.
Stddev	Alias for Stdev.
StddevP	Alias for StdevP.
Stdev	Returns the sample standard deviation of a numeric expression evaluated over a set, using the unbiased population formula.
StdevP	Returns the population standard deviation of a numeric expression evaluated over a set, using the biased population formula.
Sum	Returns the sum of a numeric expression evaluated over a set.
ThirdQ	Returns the 3rd quartile value of a numeric expression evaluated over a set.
Val	Returns the numbers contained in a string as a numeric value of appropriate type.
Value	Returns the value of a measure.
Var	Returns the sample variance of a numeric expression evaluated over a set, using the unbiased population formula.
VarP	Returns the population variance of a numeric expression evaluated over a set, using the biased population formula.
Variance	Alias for Var.
VarianceP	Alias for VarP.

Set Functions

Function	Description

Function	Description
AddCalculatedMembers	Returns a set generated by adding calculated members to a specified set.
AllMembers	Returns a set that contains all members, including calculated members, of the specified dimension, hierarchy, or level.
Ascendants	Returns the set of the ascendants of a specified member, including the member itself.
BottomCount	Returns a specified number of items from the bottom of a set, optionally ordering the set first.
BottomPercent	Sorts a set and returns the bottom N elements whose cumulative total is at least a specified percentage.
BottomSum	Sorts a set and returns the bottom N elements whose cumulative total is at least a specified value.
Cache	Caches the result of the calculation in memory for query in execution. Can be useful when same calculation is repeated during the report more than once.
CascadingChildrenSet	Returns cascading children set of aggregate calculated member (set argument of Aggregate function). If any children member is also an aggregated calculated member then it is expanded to detailed members as well.
Children	Returns the children of a specified member.
ChildrenSet	Returns children set of aggregate calculated member (set argument of Aggregate function).
Crossjoin