MDX Function Reference
eazyBI uses Mondrian OLAP reporting engine and MDX query language implementation. In addition to Mondrian provided MDX functions eazyBI defines additional MDX functions which can be used in calculated member formulas. Please start with 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). eazyBI specific additional MDX functions (which are not available in standard Mondrian OLAP engine) are marked with EAZYBI.
Detailed function description pages are still work in progress. Please contact eazyBI support and let us know which function detailed description and examples you would like to be added to this documentation.
On this page:
Date Functions
Function  Description 

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
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 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 

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.

IsEmpty  Determines if an expression evaluates to the empty cell value. 
DateAfterPeriodEnd 
EAZYBI Returns if date is after time period end date.

DateBeforePeriodEnd 
EAZYBI
Returns if date is before time period end date.

DateBetween 
EAZYBI
Returns if date is between other two dates.

DateInPeriod 
EAZYBI Returns if date is between time period start and end dates.

getBoolean 
EAZYBI Similar to
get function but will always return the member property value as a boolean
true or
false value
.

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 which contains 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) which is currently using eazyBI

CurrentUserEmail 
EAZYBI
Returns an email of the user which is currently using eazyBI

CurrentUserName 
EAZYBI Returns a name of the user which is currently using eazyBI

DataMember  Returns the systemgenerated data member that is associated with a nonleaf member of a dimension. 
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. 
GetLinkedMember 
EAZYBI Returns member with a specified key value in the same dimension and level. 
GetLinkedMembers 
EAZYBI Returns members with a specified keys in the same dimension and level. 
GetLinkedMemberName 
EAZYBI Returns name of linked member with a specified key value in the same dimension and level. 
GetLinkedMemberNames 
EAZYBI Returns names of members with a specified keys in the same dimension and level. 
GetMemberByKey 
EAZYBI Returns level member with specified key value.

GetMembersByKeys 
EAZYBI Returns level members with 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 
EAZYBI
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 

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. 
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. 
DateCompare 
EAZYBI 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 
EAZYBI Returns difference in days from first date to second date.

DateDiffWorkdays 
EAZYBI Returns difference in workdays from first date to second date.

DateDiffHours 
EAZYBI
Returns difference in hours from first date to second date.

DateDiffMinutes 
EAZYBI
Returns difference in minutes from first date to second date.

FirstQ  Returns the 1st quartile value of a numeric expression evaluated over a set. 
getNumber 
EAZYBI
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. 
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 
EAZYBI Returns numeric value if nonzero.

Ordinal  Returns the zerobased ordinal value associated with a level. 
Percentile  Returns the value of the tuple that is at a given percentile of a set. 
Rank  Returns the onebased 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 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 same calculation is repeated during the report more than once. 
CascadingChildrenSet 
EAZYBI 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 
EAZYBI Returns children set of aggregate calculated member (set argument of 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. 
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 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. 
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.

CoalesceEmpty  Coalesces an empty cell value to a number or string and returns the coalesced value. 
ExtractString 
EAZYBI Extracts string fragment using 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 empty result if the property does not exist.

getProperty 
EAZYBI Alias for get.

getString  
getMemberByKey 
EAZYBI Returns level member name with specified key value.

IIF  Returns one of two values determined by a logical test. 
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.

Properties  Returns a string (or number or date type) that contains a member property value. It is recommended to use eazyBI specific get function 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 MDXformatted string of that corresponds to a specified set. 
Str  Returns a string representation of a number. 
Titleize 
EAZYBI Returns 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 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 

Current  Returns the current tuple from a set during iteration. 
DefaultContext 
EAZYBI Returns current tuple result assuming Default level for all other dimensions that are not specified in tuple.

Item  Returns a tuple from a set. 
StrToTuple  Returns the tuple specified by an MDX–formatted string. 