All eazyBI for Jira eazyBI for Confluence Private eazyBI

MDX function reference

eazyBI uses the Mondrian OLAP reporting engine and MDX query language implementation. In addition to Mondrian-provided MDX functions, eazyBI defines additional MDX functions that 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 a 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 the standard Mondrian OLAP engine) are marked with the EAZYBI tag.

Detailed function description pages are still a work in progress. Please reach out to eazyBI support to let us know which functions you'd like to have detailed descriptions and examples added to our documentation.

On this page:

Operators

OperatorDescription

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.

+

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

{}

Curley Brace operator constructs a set.

Date Functions

FunctionDescription
DateAddReturns 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.
DateSerialReturns 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.
NowReturns 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

FunctionDescription

CurrentHierarchy

EAZYBI Returns the dimension hierarchy of the current member.

HierarchyReturns the hierarchy that contains a specified member or level.
DimensionReturns the dimension that contains a specified member, level, or hierarchy.
DimensionsReturns a hierarchy specified by a numeric or string expression.

Level Functions

FunctionDescription
LevelReturns the level of a member.
LevelsReturns 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

FunctionDescription
AnyDateInPeriod
EAZYBI Returns if any date from comma separated string is between time period start and end dates.
Case

Lets you conditionally return specific values from multiple comparisons.

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

FunctionDescription
AncestorReturns the ancestor of a member at a specified level or distance.
ClosingPeriodReturns the last sibling among the descendants of a member at a specified level.
CurrentDateMember
EAZYBI Returns the Time dimension hierarchy level member which contains the current date.
CurrentHierarchyMember

EAZYBI Returns the current dimension hierarchy member.

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

DataMemberReturns the system-generated data member that is associated with a non-leaf member of a dimension.
DateMember

EAZYBI Return Time dimension level member that matches the provided date.

DateMembersBetween

EAZYBI Returns Time dimension level members between two dates.

DefaultMemberReturns the default member of a dimension or hierarchy.
FirstChildReturns the first child of a member.
FirstSiblingReturns the first child of the parent of a member.
FuturePeriods

EAZYBI Returns a set of future members from Time dimension for calculation of 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 matches 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.

ItemReturns a member from a specified tuple.
Key

Returns the key of the selected member.

LagReturns the member that is a specified number of positions before a specified member along the member's dimension.
LastChildReturns the last child of a specified member.
LastSiblingReturns the last child of the parent of a specified member.
LeadReturns the member that is a specified number of positions following a specified member along the member's dimension.
MembersReturns a member specified by a string expression.
NextMemberReturns the next member in the level that contains a specified member.
OpeningPeriodReturns the first sibling among the descendants of a specified level, optionally at a specified member.
ParallelPeriodReturns a member from a prior period in the same relative position as a specified member.
ParentReturns the parent of a member.
PrevMemberReturns 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.
StrToMemberReturns the member specified by an MDX–formatted string.

Numeric Functions

FunctionDescription
AbsReturns the absolute value of a number.
AggregateReturns a calculated value using the appropriate aggregate function, based on the context of the query.
AvgReturns the average value of a numeric expression evaluated.
CoalesceEmptyCoalesces 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

CorrelationReturns the correlation coefficient of two series evaluated over a set.
CountReturns the number of tuples in a set.
CovarianceReturns the population covariance of two series evaluated over a set, using the biased population formula.
CovarianceNReturns 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 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.

DateDiffWorkHours

EAZYBI Returns difference in workhours from first date to second date.

FirstQReturns the 1st quartile value of a numeric expression evaluated over a set.
FixReturns the integer portion of a number. If negative, returns the negative number greater than or equal to the number.
GetNumber
EAZYBI Similar to Get function but will always return the member property value as a numeric value.
IntReturns the next lowest integer
LenReturns the number of characters in a string
LinRegInterceptCalculates the linear regression of a set and returns the value of b in the regression line y = ax + b.
LinRegPointCalculates the linear regression of a set and returns the value of y in the regression line y = ax + b.
LinRegR2Calculates the linear regression of a set and returns R2 (the coefficient of determination).
LinRegSlopeCalculates the linear regression of a set and returns the variance associated with the regression line y = ax + b.
MaxReturns the maximum value of a numeric expression evaluated over a set.
MedianReturns the median value of a numeric expression evaluated over a set.
MinReturns the minimum value of a numeric expression evaluated over a set.
NonZero
EAZYBI Returns numeric value if non-zero.
OrdinalReturns the zero-based ordinal value associated with a level.
PercentileReturns the value of the tuple that is at a given percentile of a set.
PowerPerforms an arithmetic operation that raises one number by another number.
PreviousRowValue

EAZYBI Returns the expression value of the preceding row (row above).

RankReturns the one-based rank of a specified tuple in a specified set.
RoundRounds the values in its first argument to the specified number of decimal places.
SparklineData

EAZYBI Provides a JSON result that can be used with eazyBI Sparkline formatter.

StddevAlias for Stdev.
StddevPAlias for StdevP.
StdevReturns the sample standard deviation of a numeric expression evaluated over a set, using the unbiased population formula.
StdevPReturns the population standard deviation of a numeric expression evaluated over a set, using the biased population formula.
SumReturns the sum of a numeric expression evaluated over a set.
ThirdQReturns the 3rd quartile value of a numeric expression evaluated over a set.
ValReturns the numbers contained in a string as a numeric value of appropriate type.
ValueReturns the value of a measure.
VarReturns the sample variance of a numeric expression evaluated over a set, using the unbiased population formula.
VarPReturns the population variance of a numeric expression evaluated over a set, using the biased population formula.
VarianceAlias for Var.
VariancePAlias for VarP.

Set Functions

FunctionDescription
AddCalculatedMembersReturns a set generated by adding calculated members to a specified set.
AllMembersReturns a set that contains all members, including calculated members, of the specified dimension, hierarchy, or level.
AscendantsReturns the set of the ascendants of a specified member, including the member itself.
BottomCountReturns a specified number of items from the bottom of a set, optionally ordering the set first.
BottomPercentSorts a set and returns the bottom N elements whose cumulative total is at least a specified percentage.
BottomSumSorts 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 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.
CalculatedChildrenSet

EAZYBI Returns  children set of aggregate calculated member (set argument of Aggregate function) and just for calculated member only.

ChildrenReturns the children of a specified member.
ChildrenSet
EAZYBI Returns children set of aggregate calculated member (set argument of Aggregate function).
CrossjoinReturns the cross product of two sets.
DescendantsReturns 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 Aggregate function) on a specified level.

DistinctReturns a set, removing duplicate tuples from a specified set.
DrilldownLevelDrills down the members of a set, at a specified level, to one level below. Alternatively, drills down on a specified dimension in the set.
DrilldownLevelBottomDrills down the bottommost members of a set, at a specified level, to one level below.
DrilldownLevelTopDrills down the topmost members of a set, at a specified level, to one level below.
DrilldownMemberDrills down the members in a set that are present in a second specified set.
ExceptFinds the difference between two sets, optionally retaining duplicates.
ExistsReturns the the set of tuples of the first set that exist with one or more tuples of the second set.
ExtractReturns a set of tuples from extracted dimension elements. The opposite of Crossjoin.
FilterReturns the set resulting from filtering a set based on a search condition.
GenerateApplies 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.
HeadReturns the first specified number of elements in a set.
HierarchizeOrders the members of a set in a hierarchy.
IntersectReturns the intersection of two input sets, optionally retaining duplicates.
LastPeriodsReturns a set of members prior to and including a specified member.
MembersReturns 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.

NonEmptyCrossJoinReturns the cross product of two sets, excluding empty tuples and tuples without associated fact table data.
OrderArranges members of a set, optionally preserving or breaking the hierarchy.
PeriodsToDateReturns 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.

SiblingsReturns the siblings of a specified member, including the member itself.
StrToSetReturns the set specified by an MDX–formatted string.
StripCalculatedMembersReturns a set generated by removing calculated members from a specified set.
SubsetReturns a subset of tuples from a specified set.
TailReturns a subset from the end of a set.
ToggleDrillStateToggles the drill state of members.
TopCountReturns a specified number of items from the top of a set, optionally ordering the set first.
TopPercentSorts a set and returns the top N elements whose cumulative total is at least a specified percentage.
TopSumSorts a set and returns the top N elements whose cumulative total is at least a specified value.
UnionReturns the union of two sets, optionally retaining duplicates.
UnorderRemoves 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.

TotalRowsSet

EAZYBI Returns the set of all members from rows. Used for standard calculations to calculate the % of total

VisualTotalsDynamically 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

FunctionDescription
AllProperties
EAZYBI Returns all property names and values for a member.
CoalesceEmptyCoalesces 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 string fragment using regular expression.
FormatFormats a number or date to a string.
GenerateApplies 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.

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 Get function.

GetString
EAZYBI Similar to Get function but will always return the member property value as a string value.
GetMemberNameByKey
EAZYBI Returns level member name with the specified key value.
GetMemberNamesByKeys

EAZYBI Returns names of level members with the specified key values.

IIFReturns one of two values determined by a logical test.
InStrReturns the position of the first occurrence of one string within another.
LCaseReturns a string that has been converted to lowercase.
LTrimReturns a string containing a copy of a specified string without leading spaces.
LeftReturns a specified number of characters from the left side of a string.
MidReturns a specified number of characters from a string.
NameReturns 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 to use the eazyBI specific Get function instead.

RTrimReturns a string containing a copy of a specified string without trailing spaces.
ReplaceReturns a string in which a specified substring has been replaced with another substring a specified number of times.
RightReturns a string containing a specified number of characters from the right side of a string.
SetToStrReturns an MDX-formatted string of that corresponds to a specified set.
StrReturns a string representation of a number.
Titleize

EAZYBI Returns titleized string.

TrimReturns a string containing a copy of a specified string without leading and trailing spaces.
TupleToStrReturns an MDX–formatted string that corresponds to a specified tuple.
UCaseReturns a string that has been converted to uppercase.
UniqueNameReturns the unique name of a specified dimension, hierarchy, level, or member.

Tuple Functions

FunctionDescription
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 current tuple result assuming Default level for all other dimensions that are not specified in tuple.
ItemReturns a tuple from a set with a specified position or name.
PreviousRowValue

EAZYBI Returns the expression value of the preceding row (row above).

StrToTupleReturns the tuple specified by an MDX–formatted string.
Tuple expressionA tuple is a collection of member(s) with the restriction that only one member in a tuple can be from the same dimension.