MDX function reference

eazyBI uses the Mondrian OLAP reporting engine and implements MDX query language. In addition to the Mondrian-provided MDX functions, eazyBI defines additional MDX functions that can be used in calculated member formulas; these functions are marked with the EAZYBI tag.

Please start with an introduction about calculated members to learn how to create calculated members and MDX calculation formulas.

This page lists supported MDX functions, categorized by return value. Click on a function name to see details and examples.

On this page:

Operators

Operator	Description
IS	The IS and NOT IS operators compare and return 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 a regular expression.
+	Sums two numbers.
-	Subtracts 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.
:	Returns the set of members between a given pair of members.
{}	Curley Brace operator constructs a set.

Date Functions

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	EAZYBI Returns the date plus the specified number of days.
DateAddWorkdays	EAZYBI Returns the date plus the specified number of workdays.
DateParse	EAZYBI Returns the date that is parsed from a string argument.
DateSerial	Returns the date for the specified year, month, and day.
DateToTimestamp	EAZYBI Transforms date and time from date format to timestamp format (integer).
DateWithoutTime	EAZYBI Returns the date without the time from the provided date or string argument.
GetDate	EAZYBI Similar to the Get function, but will always return the member property value as a datetime value.
NextStartDate	EAZYBI Returns the start date of the next time period.
Now	Returns the current date and time according to your eazyBI server system date and time.
StartDate	EAZYBI Returns the time period start date.
TimestampToDate	EAZYBI Transforms date and time from timestamp format (integer) to date format.

Hierarchy Functions

Function	Description
CurrentHierarchy	EAZYBI Returns the dimension hierarchy of the current member.
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
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
AnyDateInPeriod	EAZYBI Returns if any date from comma separated string is between time period start and end dates.
Case	Let's you conditionally return specific values from multiple comparisons.
DateAfterPeriodEnd	EAZYBI Returns if the date is after the time period's end date.
DateBeforePeriodEnd	EAZYBI Returns if the date is before the time period's end date.
DateBetween	EAZYBI Returns if the date is between the other two dates.
DateInPeriod	EAZYBI Returns if date is between time period start and end dates.
GetBoolean	EAZYBI Similar to the Get function, but will return the member property value as a Boolean true or false value.
IIf	Evaluates different branch expressions depending on whether a Boolean condition is true or false.
StringInCSV	EAZYBI Returns if a string exists in another comma-separated string.

Member Functions

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	EAZYBI Returns the Time dimension hierarchy level member that contains the current date.
CurrentHierarchyMember	EAZYBI Returns the current dimension hierarchy member.
CurrentMember	Returns the current member along a specified dimension or hierarchy during iteration.
CurrentUser	EAZYBI Returns a key value of the user (from all user dimensions, for example, Assignee, Logged By, etc) who is currently using eazyBI.
CurrentUserEmail	EAZYBI Returns the email of the user who is currently using eazyBI.
CurrentUserName	EAZYBI Returns the name of the user who is currently using eazyBI.
DataMember	Returns the system-generated data member that is associated with a non-leaf member of a dimension.
DateMember	EAZYBI Return the Time dimension level member that matches the provided date.
DateMembersBetween	EAZYBI 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	EAZYBI Returns a set of future members from the Time dimension to calculate cumulative sums.
GetLinkedMember	EAZYBI Identifies and returns a member in the same dimension level or a specified hierarchy level that matches the key from the current member property.
GetLinkedMembers	EAZYBI Identifies and returns members in the same dimension level or a specified hierarchy level that match the keys from the current member property.
GetLinkedMemberName	EAZYBI Returns the name of a member in the same dimension level or a specified hierarchy level that matches the key from the current member property.
GetLinkedMemberNames	EAZYBI Returns names of members in the same dimension level or a specified hierarchy level that match the keys from the current member property.
GetMemberByKey	EAZYBI Returns a level member with the specified key value.
GetMembersByKeys	EAZYBI Returns keys of level members with specified key values.
GetMemberNameByKey	EAZYBI Returns a level member name with the specified key value.
GetMemberNamesByKeys	EAZYBI Returns names of level members with the specified key values.
Item	Returns a member from a specified tuple.
Key	Returns the key of the selected member.
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	EAZYBI Returns a set of previous time dimension members for calculating cumulative sums.
StrToMember	Returns the member specified by an MDX–formatted string.

Numeric Functions

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.
Cast	The Cast operator converts scalar expressions to other types, NUMERIC, STRING, or BOOLEAN. The expression is Cast(<Expression> AS <Type>).
CoalesceEmpty	Coalesces an empty cell value to a number or string and returns the coalesced value.
ConstantColumnValue	EAZYBI Calculates the value only once per report execution and for each column, and stores those values in the cache.
ConstantValue	EAZYBI 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	EAZYBI Calculates the sum of visible row values cumulatively.
DateCompare	EAZYBI Returns -1 if the first date is less than the second date, 1 if the first date is greater than the second date, and 0 if the dates are equal.
DateDiffDays	EAZYBI Returns the difference in days from the first date to the second date.
DateDiffWorkdays	EAZYBI Returns the difference in workdays from the first date to the second date.
DateDiffHours	EAZYBI Returns the difference in hours from the first date to the second date.
DateDiffMinutes	EAZYBI Returns the difference in minutes from the first date to the second date.
DateDiffWorkHours	EAZYBI Returns the difference in work hours from the first date to the second date.
FirstQ	Returns the 1st quartile value of a numeric expression evaluated over a set.
Fix	Returns the integer portion of a number. If negative, returns the negative number greater than or equal to the number.
GetNumber	EAZYBI Similar to the Get function, but will always return the member property value as a numeric value.
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.
Mod	Returns the remainder of a division operation.
NonZero	EAZYBI Returns a numeric value if non-zero, and an empty value if the numeric value is 0.
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	EAZYBI 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.
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 the 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
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 the same calculation is repeated during the report more than once.
CascadingChildrenSet	EAZYBI Returns a cascading children set of aggregate calculated member (set argument of the Aggregate function). If any child member is also an aggregated calculated member, then it is expanded to detailed members as well.
CalculatedChildrenSet	EAZYBI Returns a children set of an aggregate calculated member (set argument of the Aggregate function) and just for calculated member only.
Children	Returns the children of a specified member.
ChildrenSet	EAZYBI Returns a children set of an aggregate calculated member (set argument of the Aggregate function).
Crossjoin	Returns the cross product of two sets.
Descendants	Returns the set of descendants of a member at a specified level, optionally including or excluding descendants in other levels.
DescendantsSet	EAZYBI Returns the set of descendants of a member or aggregate calculated member (set argument of the Aggregate function) on a specified level.
Distinct	Returns a set, removing duplicate tuples from a specified set.
DrilldownLevel	Drills down the members of a set, at a specified level, to one level below. Alternatively, drills down on a specified dimension in the set.
DrilldownLevelBottom	Drills down the bottommost members of a set, at a specified level, to one level below.
DrilldownLevelTop	Drills down the topmost members of a set, at a specified level, to one level below.
DrilldownMember	Drills down the members in a set that are present in a second specified set.
Except	Finds the difference between two sets, optionally retaining duplicates.
Exists	Returns the set of tuples of the first set that exist with one or more tuples of the second set.
Extract	Returns a set of tuples from extracted dimension elements. The opposite of Crossjoin.
Filter	Returns the set resulting from filtering a set based on a search condition.
Generate	Applies a set to each member of another set, and then joins the resulting sets by union. Alternatively, this function returns a concatenated string created by evaluating a string expression over a set.
Head	Returns the first specified number of elements in a set.
Hierarchize	Orders the members of a set in a hierarchy.
Intersect	Returns the intersection of two input sets, optionally retaining duplicates.
LastPeriods	Returns a set of members prior to and including a specified member.
Members	Returns the set of members in a dimension, level, or hierarchy.
Mtd	Returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by the Year level in the Time dimension. A shortcut function for the PeriodsToDate function that specifies the level to be Month.
NonEmptyCrossJoin	Returns the cross product of two sets, excluding empty tuples and tuples without associated fact table data.
Order	Arranges members of a set, optionally preserving or breaking the hierarchy.
PeriodsToDate	Returns a set of periods (members) from a specified level, starting with the first period and ending with a specified member.
Qtd	Returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by the Quarter level in the Time dimension. A shortcut function for the PeriodsToDate function that specifies the level to be Quarter.
Siblings	Returns the siblings of a specified member, including the member itself.
StrToSet	Returns the set specified by an MDX–formatted string.
StripCalculatedMembers	Returns a set generated by removing calculated members from a specified set.
Subset	Returns a subset of tuples from a specified set.
Tail	Returns a subset from the end of a set.
ToggleDrillState	Toggles the drill state of members.
TopCount	Returns a specified number of items from the top of a set, optionally ordering the set first.
TopPercent	Sorts a set and returns the top N elements whose cumulative total is at least a specified percentage.
TopSum	Sorts a set and returns the top N elements whose cumulative total is at least a specified value.
Union	Returns the union of two sets, optionally retaining duplicates.
Unorder	Removes any enforced ordering from a specified set.
VisibleColumnsSet	EAZYBI Returns the set of all members visible on columns, where each member represents one column.
VisibleRowsSet	EAZYBI Returns the set of all members visible on rows where each member represents one row.
TotalColumnsSet	EAZYBI Returns the set of all members from columns. Used internally for predefined calculations. For custom formulas, use VisibleColumnsSet instead.
TotalRowsSet	EAZYBI Returns the set of all members from rows. Used internally for standard calculations to calculate the % of total. For custom formulas, use VisibleRowsSet instead.
VisualTotals	Dynamically totals child members specified in a set using a pattern for the total label in the result set.
Wtd	Returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by the Week level in the Time dimension. A shortcut function for the PeriodsToDate function that specifies the level to be Week.
Ytd	Returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by the Year level in the Time dimension. A shortcut function for the PeriodsToDate function that specifies the level to be Year.

String Functions

Function	Description
AllProperties	EAZYBI Returns all property names and values for a member.
Cast	The Cast operator converts scalar expressions to other types, NUMERIC, STRING, or BOOLEAN. The expression is Cast(<Expression> AS <Type>).
CoalesceEmpty	Coalesces an empty cell value to a number or string and returns the coalesced value.
ConstantColumnValue	EAZYBI Calculates the value only once per report execution and per each column and stores those values in the cache
ConstantValue	EAZYBI Calculates the value once per report execution and stores it as one value in the cache
ExtractString	EAZYBI Extracts a string fragment using a regular expression.
Format	Formats a number or date to a string.
Generate	Applies a set to each member of another set, and then joins the resulting sets by union. Alternatively, this function returns a concatenated string created by evaluating a string expression over a set.
Get	EAZYBI Returns the member property value or an empty result if the property does not exist.
GetCaption	EAZYBI Returns the display name of a dimension, hierarchy, level, or member.
GetCaptionWithoutKey	EAZYBI Returns the display name of a dimension, hierarchy, level, or member without the key. This function is useful for addressing the "Issue" dimension members.
GetProperty	EAZYBI Alias for the Get function.
GetString	EAZYBI Similar to the Get function, but will always return the member property value as a string value.
GetMemberNameByKey	EAZYBI Returns the level member name with the specified key value.
GetMemberNamesByKeys	EAZYBI Returns names of level members with the specified key values.
InStr	Returns the position of the first occurrence of one string within another.
LCase	Returns a string that has been converted to lowercase.
LTrim	Returns a string containing a copy of a specified string without leading spaces.
Left	Returns a specified number of characters from the left side of a string.
Mid	Returns a specified number of characters from a string.
Name	Returns the name of a dimension, hierarchy, level, or member.
NonEmptyString	EAZYBI Returns the string if it is not empty (contains at least one character), otherwise returns an empty result.
PreviousRowValue	EAZYBI Returns the expression value of the preceding row (row above).
Properties	Returns a string (or number or date type) that contains a member property value. It is recommended that the eazyBI-specific Get function be used instead.
RTrim	Returns a string containing a copy of a specified string without trailing spaces.
Replace	Returns a string in which a specified substring has been replaced with another substring a specified number of times.
Right	Returns a string containing a specified number of characters from the right side of a string.
SetToStr	Returns an MDX-formatted string that corresponds to a specified set.
Str	Returns a string representation of a number.
Titleize	EAZYBI Returns a titleized string.
Trim	Returns a string containing a copy of a specified string without leading and trailing spaces.
TupleToStr	Returns an MDX–formatted string that corresponds to a specified tuple.
UCase	Returns a string that has been converted to uppercase.
UniqueName	Returns the unique name of a specified dimension, hierarchy, level, or member.

Tuple Functions

Function	Description
ConstantColumnValue	EAZYBI Calculates the value only once per report execution and per each column and stores those values in the cache.
ConstantValue	EAZYBI Calculates the value once per report execution and stores it as one value in the cache.
CurrentTuple	EAZYBI Returns the current tuple from a set during iteration.
DefaultContext	EAZYBI Returns the current tuple result, assuming the Default level for all other dimensions that are not specified in the tuple.
Item	Returns a tuple from a set with a specified position or name.
PreviousRowValue	EAZYBI Returns the expression value of the preceding row (row above).
StrToTuple	Returns the tuple specified by an MDX–formatted string.
Tuple expression	A tuple is a collection of members with the restriction that only one member in a tuple can be from the same dimension.

Other Functions

Function	Description
Cache	Caches the calculation result in memory for query execution. This is useful when the same calculation is repeated during the report more than once.
CatchException	EAZYBI Evaluates an expression and returns an empty or specified value if the expression fails.
SparklineData	EAZYBI Provides a JSON result that can be used with the eazyBI Sparkline formatter.