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 |
|---|---|
| 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 |
|---|---|
| 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). |
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 | |
| 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 |
|---|---|
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 |
|---|---|
| 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 | 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 |
|---|---|
| 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 |
|---|---|
| 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 | |
| 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 |
|---|---|
| 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 | 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. |
| VisibleRowsSet | Returns the set of all members which are visible on rows where each member is representing one row. |
| 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 | 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 | 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 | Returns the member property value or empty result if the property does not exist. |
| GetCaption | Returns the display name of a dimension, hierarchy, level, or member. |
| getProperty | Alias for get. |
| getString | |
| getMemberByKey | Returns level member name with specified key value. |
| IIF | Returns one of two values determined by a logical test. |
| 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 | 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 flex.bi 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 MDX-formatted string of that corresponds to a specified set. |
| Str | Returns a string representation of a number. |
| Titleize | 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 | 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. |