> ## Documentation Index > Fetch the complete documentation index at: https://docs.activeviam.com/llms.txt > Use this file to discover all available pages before exploring further. # List of Supported MDX Functions by Atoti ## MDX Functions This table lists all existing MDX functions and details their support level in Atoti. The following notations are used: | Symbol | Meaning | | :--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------: | --------------------- | | tick | fully supported | | plus | partially supported | | minus | not supported | | cross | will not be supported | | Function Name | Support | Documentation & Comments | | | ---------------------- | :--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------: | -------------------------------------------------------------------------------------------------------------------------------------------------------------------- | - | | AddCalculatedMembers | tick | [MSDN AddCalculatedMembers (MDX)](https://docs.microsoft.com/en-us/sql/mdx/addcalculatedmembers-mdx) | | | Aggregate | plus | [Aggregate Function (MDX)](#aggregate-function) | | | AllMembers | tick | [MSDN AllMembers (MDX)](https://docs.microsoft.com/en-us/sql/mdx/allmembers-mdx) | | | Ancestor | tick | [MSDN Ancestor (MDX)](https://docs.microsoft.com/en-us/sql/mdx/ancestor-mdx) | | | Ancestors | tick | [MSDN Ancestors (MDX)](https://docs.microsoft.com/en-us/sql/mdx/ancestors-mdx) | | | Ascendants | tick | [MSDN Ascendants (MDX)](https://docs.microsoft.com/en-us/sql/mdx/ascendants-mdx) | | | Avg | tick | [MSDN Avg (MDX)](https://docs.microsoft.com/en-us/sql/mdx/avg-mdx) | | | Axis | cross | [MSDN Axis (MDX)](https://docs.microsoft.com/en-us/sql/mdx/axis-mdx) | | | BottomCount | tick | [MSDN BottomCount (MDX)](https://docs.microsoft.com/en-us/sql/mdx/bottomcount-mdx) | | | BottomPercent | tick | [MSDN BottomPercent (MDX)](https://docs.microsoft.com/en-us/sql/mdx/bottompercent-mdx) | | | BottomSum | tick | [MSDN BottomSum (MDX)](https://docs.microsoft.com/en-us/sql/mdx/bottomsum-mdx) | | | CASE Statement | tick | [MSDN CASE Statement (MDX)](https://docs.microsoft.com/en-us/sql/mdx/case-statement-mdx) | | | Caption | tick | [Caption Function (MDX)](#caption-function) | | | CDate | tick | [MSDN Type Conversion Functions (VBA - CDate)](https://docs.microsoft.com/en-us/office/vba/language/concepts/getting-started/type-conversion-functions) | | | Children | tick | [MSDN Children (MDX)](https://docs.microsoft.com/en-us/sql/mdx/children-mdx) | | | CInt | tick | [MSDN Type Conversion Functions (VBA - CInt)](https://docs.microsoft.com/en-us/office/vba/language/concepts/getting-started/type-conversion-functions) | | | CLng | tick | [MSDN Type Conversion Functions (VBA - CLng)](https://docs.microsoft.com/en-us/office/vba/language/concepts/getting-started/type-conversion-functions) | | | ClosingPeriod | tick | [MSDN ClosingPeriod (MDX)](https://docs.microsoft.com/en-us/sql/mdx/closingperiod-mdx) | | | CoalesceEmpty | tick | [MSDN CoalesceEmpty (MDX)](https://docs.microsoft.com/en-us/sql/mdx/coalesceempty-mdx) Without type constraint | | | Correlation | tick | [MSDN Correlation (MDX)](https://docs.microsoft.com/en-us/sql/mdx/correlation-mdx) | | | Count (Dimensions) | tick | [MSDN Count (Hierarchy Levels) (MDX)](https://docs.microsoft.com/en-us/sql/mdx/count-dimension-mdx) | | | Count (Levels) | tick | [MSDN Count (Hierarchy Levels) (MDX)](https://docs.microsoft.com/en-us/sql/mdx/count-hierarchy-levels-mdx) | | | Count (Tuple) | tick | [MSDN Count (Tuple) (MDX)](https://docs.microsoft.com/en-us/sql/mdx/count-tuple-mdx) | | | Count (Set) | tick | [MSDN Count (Set) (MDX)](https://docs.microsoft.com/en-us/sql/mdx/count-set-mdx) | | | Cousin | tick | [MSDN Cousin (MDX)](https://docs.microsoft.com/en-us/sql/mdx/cousin-mdx) | | | Covariance | tick | [MSDN Covariance (MDX)](https://docs.microsoft.com/en-us/sql/mdx/covariance-mdx) | | | CovarianceN | tick | [MSDN CovarianceN (MDX)](https://docs.microsoft.com/en-us/sql/mdx/covariancen-mdx) | | | CrossJoin | tick | [MSDN CrossJoin (MDX)](https://docs.microsoft.com/en-us/sql/mdx/crossjoin-mdx) | | | CStr | tick | [MSDN Type Conversion Functions (VBA - CStr)](https://docs.microsoft.com/en-us/office/vba/language/concepts/getting-started/type-conversion-functions) | | | Current | tick | [MSDN Current (MDX)](https://docs.microsoft.com/en-us/sql/mdx/current-mdx) | | | CurrentMember | tick | [MSDN Current Member (MDX)](https://docs.microsoft.com/en-us/sql/mdx/currentmember-mdx) | | | CurrentOrdinal | plus | [MSDN Current Ordinal (MDX)](https://docs.microsoft.com/en-us/sql/mdx/currentordinal-mdx) Only works on Named sets | | | CustomData | cross | [MSDN CustomData (MDX)](https://docs.microsoft.com/en-us/sql/mdx/customdata-mdx) | | | DataMember | tick | [MSDN DataMember (MDX)](https://docs.microsoft.com/en-us/sql/mdx/datamember-mdx) | | | DateAdd | tick | [MSDN DateAdd (VBA)](https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dateadd-function) | | | DateDiff | tick | [MSDN DateDiff (VBA)](https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/datediff-function) | | | DatePart | tick | [MSDN DatePart (VBA)](https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/datepart-function) Optional arguments are not supported | | | Day | tick | [MSDN Day (DAX)](https://docs.microsoft.com/en-us/dax/day-function-dax) | | | DefaultMember | tick | [MSDN DefaultMember (MDX)](https://docs.microsoft.com/en-us/sql/mdx/defaultmember-mdx) | | | Descendants | tick | [MSDN Descendants (MDX)](https://docs.microsoft.com/en-us/sql/mdx/descendants-mdx) | | | Dimension | tick | [MSDN Dimension (MDX)](https://docs.microsoft.com/en-us/sql/mdx/dimension-mdx) | | | Dimensions | tick | [MSDN Dimensions (MDX)](https://docs.microsoft.com/en-us/sql/mdx/dimensions-mdx) | | | Distinct | tick | [MSDN Distinct (MDX)](https://docs.microsoft.com/en-us/sql/mdx/distinct-mdx) | | | DistinctCount | tick | [MSDN DistinctCount (MDX)](https://docs.microsoft.com/en-us/sql/mdx/distinctcount-mdx) | | | DrillDownLevel | tick | [MSDN DrillDownLevel (MDX)](https://docs.microsoft.com/en-us/sql/mdx/drilldownlevel-mdx) | | | DrillDownLevelBottom | plus | [MSDN DrillDownLevelBottom (MDX)](https://docs.microsoft.com/en-us/sql/mdx/drilldownlevelbottom-mdx) | | | DrillDownLevelTop | plus | [MSDN DrillDownLevelTop (MDX)](https://docs.microsoft.com/en-us/sql/mdx/drilldownleveltop-mdx) | | | DrillDownMember | plus | [MSDN DrillDownMember (MDX)](https://docs.microsoft.com/en-us/sql/mdx/drilldownmember-mdx) | | | DrillDownMemberBottom | plus | [MSDN DrillDownMemberBottom (MDX)](https://docs.microsoft.com/en-us/sql/mdx/drilldownmemberbottom-mdx) | | | DrillDownMemberTop | plus | [MSDN DrillDownMemberTop (MDX)](https://docs.microsoft.com/en-us/sql/mdx/drilldownmembertop-mdx) | | | DrillupLevel | tick | [MSDN DrillUpLevel (MDX)](https://docs.microsoft.com/en-us/sql/mdx/drilluplevel-mdx) | | | DrillupMember | tick | [MSDN DrillupMember (MDX)](https://docs.microsoft.com/en-us/sql/mdx/drillupmember-mdx) | | | Error | tick | [MSDN Error (MDX)](https://docs.microsoft.com/en-us/sql/mdx/error-mdx) | | | Except | tick | [MSDN Except (MDX)](https://docs.microsoft.com/en-us/sql/mdx/except-mdx-function) | | | Exists | tick | [MSDN Exists (MDX)](https://docs.microsoft.com/en-us/sql/mdx/exists-mdx), Signature with MeasureGroup not supported (i.e. this function is an alias for NonEmpty) | | | Extract | tick | [MSDN Extract (MDX)](https://docs.microsoft.com/en-us/sql/mdx/extract-mdx) | | | Filter | tick | [MSDN Filter (MDX)](https://docs.microsoft.com/en-us/sql/mdx/filter-mdx) | | | FirstChild | tick | [MSDN FirstChild (MDX)](https://docs.microsoft.com/en-us/sql/mdx/firstchild-mdx) | | | FirstSibling | tick | [MSDN FirstSibling (MDX)](https://docs.microsoft.com/en-us/sql/mdx/firstsibling-mdx) | | | Generate | tick | [MSDN Generate (MDX)](https://docs.microsoft.com/en-us/sql/mdx/generate-mdx) | | | Head | tick | [MSDN Head (MDX)](https://docs.microsoft.com/en-us/sql/mdx/head-mdx) | | | Hierarchize | tick | [MSDN Hierarchize (MDX)](https://docs.microsoft.com/en-us/sql/mdx/hierarchize-mdx) | | | Hierarchy | tick | [MSDN Hierarchy (MDX)](https://docs.microsoft.com/en-us/sql/mdx/hierarchy-mdx) | | | IIf | tick | [MSDN IIf (MDX)](https://docs.microsoft.com/en-us/sql/mdx/iif-mdx) | | | InStr | tick | [InStr Function (MDX)](#instr-function) | | | Intersect | tick | [MSDN Intersect (MDX)](https://docs.microsoft.com/en-us/sql/mdx/intersect-mdx), Signature with ALL is not supported | | | IsAncestor | tick | [MSDN IsAncestor (MDX)](https://docs.microsoft.com/en-us/sql/mdx/isancestor-mdx) | | | IsEmpty | tick | [MSDN IsEmpty (MDX)](https://docs.microsoft.com/en-us/sql/mdx/isempty-mdx) | | | IsGeneration | cross | [MSDN IsGeneration (MDX)](https://docs.microsoft.com/en-us/sql/mdx/isgeneration-mdx) | | | IsLeaf | tick | [MSDN IsLeaf (MDX)](https://docs.microsoft.com/en-us/sql/mdx/isleaf-mdx) | | | IsNull | tick | [MSDN IsNull (VBA)](https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/isnull-function) | | | IsSibling | minus | [MSDN IsSibling (MDX)](https://docs.microsoft.com/en-us/sql/mdx/issibling-mdx) | | | Item (Member) | plus | [MSDN Item (Member) (MDX)](https://docs.microsoft.com/en-us/sql/mdx/item-member-mdx), Signature with strings is not supported | | | Item (Tuple) | plus | [MSDN Item (Tuple) (MDX)](https://docs.microsoft.com/en-us/sql/mdx/item-tuple-mdx), Signature with strings is not supported | | | KPIGoal | tick | [MSDN KPIGoal (MDX)](https://docs.microsoft.com/en-us/sql/mdx/kpigoal-mdx) | | | KPIStatus | tick | [MSDN KPIStatus (MDX)](https://docs.microsoft.com/en-us/sql/mdx/kpistatus-mdx) | | | KPITrend | tick | [MSDN KPITrend (MDX)](https://docs.microsoft.com/en-us/sql/mdx/kpitrend-mdx) | | | KPIWeight | minus | [MSDN KPIWeight (MDX)](https://docs.microsoft.com/en-us/sql/mdx/kpiweight-mdx) | | | KPICurrentTimeMember | minus | [MSDN KPICurrentTimeMember (MDX)](https://docs.microsoft.com/en-us/sql/mdx/kpicurrenttimemember-mdx) | | | KPIValue | tick | [MSDN KPIValue (MDX)](https://docs.microsoft.com/en-us/sql/mdx/kpivalue-mdx) | | | Lag | tick | [MSDN Lag (MDX)](https://docs.microsoft.com/en-us/sql/mdx/lag-mdx) | | | LastChild | tick | [MSDN LastChild (MDX)](https://docs.microsoft.com/en-us/sql/mdx/lastchild-mdx) | | | LastPeriods | tick | [MSDN LastPeriods (MDX)](https://docs.microsoft.com/en-us/sql/mdx/lastperiods-mdx) | | | LastSibling | tick | [MSDN LastSibling (MDX)](https://docs.microsoft.com/en-us/sql/mdx/lastsibling-mdx) | | | LCase | tick | [MSDN LCase (VBA)](https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/lcase-function) | | | Lead | tick | [MSDN Lead (MDX)](https://docs.microsoft.com/en-us/sql/mdx/lead-mdx) | | | Leaves | minus | [MSDN Leaves (MDX)](https://docs.microsoft.com/en-us/sql/mdx/leaves-mdx) | | | Left | tick | [Left Function](#left-function) | | | Level | tick | [MSDN Level (MDX)](https://docs.microsoft.com/en-us/sql/mdx/level-mdx) | | | Levels | tick | [MSDN Levels (MDX)](https://docs.microsoft.com/en-us/sql/mdx/levels-mdx) | | | LinkMember | cross | [MSDN LinkMember (MDX)](https://docs.microsoft.com/en-us/sql/mdx/linkmember-mdx) | | | LinRegIntercept | tick | [MSDN LinRegIntercept (MDX)](https://docs.microsoft.com/en-us/sql/mdx/linregintercept-mdx) | | | LinRegPoint | tick | [MSDN LinRegPoint (MDX)](https://docs.microsoft.com/en-us/sql/mdx/linregpoint-mdx) | | | LinRegR2 | tick | [MSDN LinRegR2 (MDX)](https://docs.microsoft.com/en-us/sql/mdx/linregr2-mdx) | | | LinRegSlope | tick | [MSDN LinRegSlope (MDX)](https://docs.microsoft.com/en-us/sql/mdx/linregslope-mdx) | | | LinRegVariance | tick | [MSDN LinRegVariance (MDX)](https://docs.microsoft.com/en-us/sql/mdx/linregvariance-mdx) | | | LookupCube | cross | [MSDN LookupCube (MDX)](https://docs.microsoft.com/en-us/sql/mdx/lookupcube-mdx) | | | Max | tick | [MSDN Max (MDX)](https://docs.microsoft.com/en-us/sql/mdx/max-mdx) | | | MeasureGroupMeasures | cross | [MSDN MeasureGroupMeasures (MDX)](https://docs.microsoft.com/en-us/sql/mdx/measuregroupmeasures-mdx) | | | Median | tick | [MSDN Median (MDX)](https://docs.microsoft.com/en-us/sql/mdx/median-mdx) | | | member\_caption | tick | [member\_caption Function](#member-caption-function) | | | Members | tick | [MSDN Members (MDX)](https://docs.microsoft.com/en-us/sql/mdx/members-set-mdx) | | | MemberToStr | minus | [MSDN MemberToStr (MDX)](https://docs.microsoft.com/en-us/sql/mdx/membertostr-mdx) | | | MemberValue | tick | [MSDN MemberValue (MDX)](https://docs.microsoft.com/en-us/sql/mdx/membervalue-mdx) | | | Min | tick | [MSDN Min (MDX)](https://docs.microsoft.com/en-us/sql/mdx/min-mdx) | | | Month | tick | [MSDN Month (DAX)](https://docs.microsoft.com/en-us/dax/month-function-dax) | | | Mtd | tick | [MSDN Mtd (MDX)](https://docs.microsoft.com/en-us/sql/mdx/mtd-mdx) | | | Name | tick | [MSDN Name (MDX)](https://docs.microsoft.com/en-us/sql/mdx/name-mdx) | | | NameToSet | cross | [MSDN NameToSet (MDX)](https://docs.microsoft.com/en-us/sql/mdx/nametoset-mdx) | | | NextMember | tick | [MSDN NextMember (MDX)](https://docs.microsoft.com/en-us/sql/mdx/nextmember-mdx) | | | NonEmpty | tick | [MSDN NonEmpty (MDX)](https://docs.microsoft.com/en-us/sql/mdx/nonempty-mdx) | | | NonEmptyCrossjoin | cross | [MSDN NonEmptyCrossjoin (MDX)](https://docs.microsoft.com/en-us/sql/mdx/nonemptycrossjoin-mdx) | | | Now | tick | [MSDN Now (DAX)](https://docs.microsoft.com/en-us/dax/now-function-dax) | | | OpeningPeriod | tick | [MSDN Opening Period (MDX)](https://docs.microsoft.com/en-us/sql/mdx/openingperiod-mdx) | | | Order | tick | [Atoti Order Function (MDX)](#order-function), `DESC` is sorted as in `AS 2000` | | | Ordinal | tick | [MSDN Ordinal (MDX)](https://docs.microsoft.com/en-us/sql/mdx/ordinal-mdx) | | | ParallelPeriod | tick | [MSDN ParallelPeriod (MDX)](https://docs.microsoft.com/en-us/sql/mdx/parallelperiod-mdx) | | | Parent | tick | [MSDN Parent (MDX)](https://docs.microsoft.com/en-us/sql/mdx/parent-mdx) | | | PeriodsToDate | tick | [MSDN PeriodsToDate (MDX)](https://docs.microsoft.com/en-us/sql/mdx/periodstodate-mdx) | | | Predict | cross | [MSDN Predict (MDX)](https://docs.microsoft.com/en-us/sql/mdx/predict-mdx) | | | PrevMember | tick | [MSDN PrevMember (MDX)](https://docs.microsoft.com/en-us/sql/mdx/prevmember-mdx) | | | Properties | plus | [MSDN Properties (MDX)](https://docs.microsoft.com/en-us/sql/mdx/properties-mdx) | | | Qtd | tick | [MSDN Qtd (MDX)](https://docs.microsoft.com/en-us/sql/mdx/qtd-mdx) | | | Rank | tick | [MSDN Rank (MDX)](https://docs.microsoft.com/en-us/sql/mdx/rank-mdx) | | | Right | tick | [Right Function](#right-function) | | | RollupChildren | cross | [MSDN RollupChildren (MDX)](https://docs.microsoft.com/en-us/sql/mdx/rollupchildren-mdx) | | | Round | tick | [MSDN Round (VBA)](https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/round-function) | | | SetToArray | cross | [MSDN SetToArray (MDX)](https://docs.microsoft.com/en-us/sql/mdx/settoarray-mdx) | | | SetToStr | minus | [MSDN SetToStr (MDX)](https://docs.microsoft.com/en-us/sql/mdx/settostr-mdx) | | | Siblings | tick | [MSDN Siblings (MDX)](https://docs.microsoft.com/en-us/sql/mdx/siblings-mdx) | | | Stdev | tick | [MSDN Stdev (MDX)](https://docs.microsoft.com/en-us/sql/mdx/stdev-mdx) | | | StdevP | tick | [MSDN StdevP (MDX)](https://docs.microsoft.com/en-us/sql/mdx/stdevp-mdx) | | | StripCalculatedMembers | tick | [MSDN StripCalculatedMembers (MDX)](https://docs.microsoft.com/en-us/sql/mdx/stripcalculatedmembers-mdx) | | | StrToMember | plus | [MSDN StrToMember (MDX)](https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms146022\(v=sql.110\)), acts as the CONSTRAINED keyword was provided | | | StrToSet | minus | [MSDN StrToSet (MDX)](https://docs.microsoft.com/en-us/sql/mdx/strtoset-mdx) | | | StrToTuple | minus | [MSDN StrToTuple (MDX)](https://docs.microsoft.com/en-us/sql/mdx/strtotuple-mdx) | | | StrToValue | cross | [MSDN StrToValue (MDX)](https://docs.microsoft.com/en-us/sql/mdx/strtovalue-mdx) | | | Subset | tick | [MSDN Subset (MDX)](https://docs.microsoft.com/en-us/sql/mdx/subset-mdx) | | | Sum | tick | [MSDN Sum (MDX)](https://docs.microsoft.com/en-us/sql/mdx/sum-mdx) | | | Tail | plus | [MSDN Tail (MDX)](https://docs.microsoft.com/en-us/sql/mdx/tail-mdx), one missing signature | | | This | cross | [MSDN This (MDX)](https://docs.microsoft.com/en-us/sql/mdx/this-mdx) | | | ToggleDrillState | minus | [MSDN ToggleDrillState (MDX)](https://docs.microsoft.com/en-us/sql/mdx/toggledrillstate-mdx) | | | TopCount | tick | [MSDN TopCount (MDX)](https://docs.microsoft.com/en-us/sql/mdx/topcount-mdx) | | | TopPercent | tick | [MSDN TopPercent (MDX)](https://docs.microsoft.com/en-us/sql/mdx/toppercent-mdx) | | | TopSum | tick | [MSDN TopSum (MDX)](https://docs.microsoft.com/en-us/sql/mdx/topsum-mdx) | | | TupleToStr | minus | [MSDN TupleToStr (MDX)](https://docs.microsoft.com/en-us/sql/mdx/tupletostr-mdx) | | | UCase | tick | [MSDN UCase (VBA)](https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/ucase-function) | | | Union | tick | [MSDN Union (MDX)](https://docs.microsoft.com/en-us/sql/mdx/union-mdx) | | | UniqueName | tick | [MSDN UniqueName (MDX)](https://docs.microsoft.com/en-us/sql/mdx/uniquename-mdx) | | | UnknownMember | minus | [MSDN UnknownMember (MDX)](https://docs.microsoft.com/en-us/sql/mdx/unknownmember-mdx) | | | Unorder | tick | [MSDN Unorder (MDX)](https://docs.microsoft.com/en-us/sql/mdx/unorder-mdx) | | | UserName | cross | [MSDN UserName (MDX)](https://docs.microsoft.com/en-us/sql/mdx/username-mdx) | | | ValidMeasure | cross | [MSDN ValidMeasure (MDX)](https://docs.microsoft.com/en-us/sql/mdx/validmeasure-mdx) | | | Value | tick | [MSDN Value (MDX)](https://docs.microsoft.com/en-us/sql/mdx/value-mdx) | | | Var | tick | [MSDN Var (MDX)](https://docs.microsoft.com/en-us/sql/mdx/var-mdx) | | | Variance | tick | [MSDN Variance (MDX)](https://docs.microsoft.com/en-us/sql/mdx/variance-mdx) | | | VarianceP | tick | [MSDN VarianceP (MDX)](https://docs.microsoft.com/en-us/sql/mdx/variancep-mdx) | | | VarP | tick | [MSDN VaRP (MDX)](https://docs.microsoft.com/en-us/sql/mdx/varp-mdx) | | | VisualTotals | plus | [VisualTotals function (MDX)](#visualtotals-function) | | | Weekday | tick | [MSDN Weekday (DAX)](https://docs.microsoft.com/en-us/dax/weekday-function-dax) | | | Wtd | tick | [MSDN Wtd (MDX)](https://docs.microsoft.com/en-us/sql/mdx/wtd-mdx) | | | Year | tick | [MSDN Year (DAX)](https://docs.microsoft.com/en-us/dax/year-function-dax) | | | Ytd | tick | [MSDN Ytd (MDX)](https://docs.microsoft.com/en-us/sql/mdx/ytd-mdx) | | ## Functions Deviating from MDX Standards ### Aggregate Function The aggregate function in Atoti behaves mostly as described in the [MSDN Aggregate (MDX)](https://docs.microsoft.com/en-us/sql/mdx/aggregate-mdx) article. The differences are: #### Signature with Numeric Expression as Second Argument not Supported The signature with a numeric expression as second argument is not supported: * `Aggregate()` is supported * `Aggregate(, )` is not supported #### Sets with Dimensionality 1 The aggregate function can only operate on sets with dimensionality 1. #### Double Counting is Discarded Double counting is discarded. For example, if you have a Geography dimension with the following members: ``` AllMember +- France +- Paris +- Lyon ``` and you ask for: `Aggregate({[Geography].[ALL].[AllMember].[France].[Paris], [Geography].[ALL].[AllMember].[France].[Paris]})` , then *Paris* is only counted once. #### Isolated Calculated Member An aggregate can only be used as an isolated calculated member. It means ``` WITH MEMBER [Geography].[Test] AS Aggregate({[Geography].[ALL].[AllMember].[France], [Geography].[ALL].[AllMember].[USA]}) + 1 SELECT [Geography].[Test] ON COLUMNS FROM [MyCube] ``` would not work and needs to be rewritten as ``` WITH MEMBER [Geography].[Test_Intermediary] AS Aggregate({[Geography].[ALL].[AllMember].[France], [Geography].[ALL].[AllMember].[USA]}) [Geography].[Test] AS [Geography].[Test_Intermediary] + 1 SELECT [Geography].[Test] ON COLUMNS FROM [MyCube] ``` ### Caption Function This function returns the formatted name of a member. It is also the value of the Intrinsic Member Property MEMBER\_CAPTION. See [MSDN Intrinsic Member Properties (MDX)](https://docs.microsoft.com/en-us/analysis-services/multidimensional-models/mdx/mdx-member-properties-intrinsic-member-properties). #### Syntax: ``` <- .Caption ``` ### InStr Function This function looks for a string within another. It returns the index (one based) at which the sub-string starts, zero if it is not found. The lookup is case-insensitive. It is possible to specify an offset (one based) at which the search should start. See [MSDN InStr Function (VBA)](https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/instr-function). #### Syntax ``` <- InStr( [ : offset,] : value, : lookup ) ``` * *offset (optional)*: The offset at which the lookup should start in the value. This offset is one based. If the offset is not an integer, an error is returned. If the offset is less or equal to zero, an error is returned. * *value*: The string to look into. * *lookup*: The string to look up. #### Examples ``` InStr(1, "Test", "es") = 2 InStr("Test", "es") = 2 InStr("Test", "ES") = 2 InStr(0, "Test", "t") = ! ERROR ! InStr("Test", "k") = 0 InStr(10, "Test", "t") = 0 InStr(2, "Test", "t") = 4 ``` ### Left Function This function returns the sub-string of a string starting from the first character up to a specific input length. #### Syntax ``` <- Left( : value, : length ) ``` * *value*: The string to sub-string. * *length*: The length of the sub-string. If the length is not an integer, an error is returned. If the length is lower than zero, an error is returned. #### Examples ``` Left("Test", 1) = "T" Left("Test", 0) = "" Left("Test", -1) = ! ERROR ! Left("Test", 10) = "Test" ``` ### Member Caption Function The 'member\_caption Function' is an alias for the 'Caption Function'. See [Caption Function (MDX)](#caption-function) ### Order Function The MDX Order function in Atoti behaves mostly as described in the [MSDN Order (MDX)](https://docs.microsoft.com/en-us/sql/mdx/order-mdx) documentation. This section provides additional details that are not mentioned in the MSDN page. #### Nested Calls to Order and Hierarchize - Stable Sort Calling `Order(Hierarchize(set_expression))` deactivates the Hierarchize order. Calling `Hierarchize(Order(set_expression))` deactivates the Order order. Calling `Order(Order(set_expression))` performs a stable sort. This means that members that are equal according to the first (outer) ordering are sorted according to the second (inner) ordering. This functionality doesn't work if any function is inserted between the calls to 'Order'. For example, `Order(Filter(Order(set_expression))`) doesn't perform a stable sort. Results are sorted only according to the first (outer) ordering. #### Examples ``` // Always Order(Crossjoin(Hierarchize([City].Members), [Currency].Members), BASC, cityMeasure) <=> Order(Crossjoin([City].Members, [Currency].Members), BASC, cityMeasure) Hierarchize(Crossjoin(Order([City].Members, BASC, cityMeasure), [Currency].Members)) <=> Hierarchize(Crossjoin([City].Members, [Currency].Members)) // Sorts by descending currencies, breaking ties using default hierarchical order (no stable order because calls to Order do not directly follow each other) Order(Crossjoin(Order([City].Members, ASC, cityMeasure), [Currency].Members), DESC, currencyMeasure) // Stable ordering is supported when calls to Order directly follow each other // Sorts by descending currencyMeasure, breaking ties by ascending cityMeasure Order(Order(Crossjoin([City].Members, [Currency].Members), ASC, cityMeasure), DESC, currencyMeasure) ``` ### Right Function It returns the sub-string of a string starting from the last character going backwards to a specific input length. #### Syntax ``` <- Right( : value, : length ) ``` * *value*: The string to sub-string. * *length*: The length of the sub-string. If the length is not an integer, an error is returned. If the length is lower than zero, an error is returned. #### Examples ``` Right("Test", 1) = "t" Right("Test", 0) = "" Right("Test", -1) = ! ERROR ! Right("Test", 10) = "Test" ``` ### VisualTotals Function The behavior of this function is slightly different from the standard (see [MSDN](https://docs.microsoft.com/en-us/sql/mdx/visualtotals-mdx)). When you use VisualTotals on a dimension, only the members that appear in the given set and are not measures will be granted (see ISubCubeProperties in [Standard Context Values](https://activeviam.atlassian.net/wiki/display/AP5/Standard+Context+Values)). As a result, members not contained in the VisualTotals are excluded from the results. #### Syntax ``` <- VisualTotals( : set [, : pattern] ) ``` * *set*: The string to sub-string. * *pattern*: Not supported. #### Examples Assuming we have the following data: | Underlyings | contributors.COUNT | | ----------- | ------------------ | | AllMember | 7 | | EUR | 4 | | JPY | 2 | | USD | 1 | The two queries below return the same pivot table: ``` SELECT [Measures].[contributors.COUNT] ON 0, VisualTotals({[Underlyings].[ALL].[AllMember], [Underlyings].[ALL].[AllMember].[EUR], [Underlyings].[ALL].[AllMember].[USD]}) ON 1 FROM [cube] ``` ``` SELECT [Measures].[contributors.COUNT] ON 0, {VisualTotals({[Underlyings].[ALL].[AllMember], [Underlyings].[ALL].[AllMember].[EUR], [Underlyings].[ALL].[AllMember].[USD]}), [Underlyings].[ALL].[AllMember].[JPY]} ON 1 FROM [cube] ``` Result: | Underlyings | contributors.COUNT | | ----------- | ------------------ | | AllMember | 5 | | EUR | 4 | | USD | 1 | ## Custom Functions Atoti also allows the use of several non-standard functions. ### Is\_DataMember This function returns 'true' if the given member is a datamember, 'false' otherwise. #### Syntax ``` MemberExpression.Is_DataMember ``` ### Is\_UnknownMember It returns true if the given member is an unknown member, false otherwise. A member is considered unknown either if it is the UnknownMember of its parent or if it has an UnknownMember in its path. For example, the member `[dimension].[hierarchy].[level].[N/A].[A child].[A grand child]` is considered unknown because it has N/A in its path. #### Syntax ``` MemberExpression.Is_UnknownMember ``` #### ActiveMeasure For details, see [Active Measures and MDX compilation](https://activeviam.atlassian.net/wiki/display/AP5/Active+Measures+and+MDX+compilation). ### Today This function returns the date of the current day. #### Syntax ``` Today() ``` ### ReverseHierarchize `ReverseHierarchize` reorders a set so that its elements appear in **reverse hierarchical order**. The **reverse hierarchical order** is defined as follows: for each hierarchy in the set, elements are ordered such that * Parents appear before their children * Among siblings, the ordering is reversed: items that appear later in the hierarchy come first You may optionally provide a **level**. When a level is specified, only elements at that level are reversed and the other levels in all other hierarchies are sorted in ascending hierarchical order. If a level is specified, but elements of the given Set do not express a coordinate on that level, this function behaves like `Hierarchize`. #### Example Given the following set: | Hierarchy 1 | Hierarchy 2 | Hierarchy 3 | | ----------- | -------------- | ----------- | | AllMember | AllMember | AllMember | | AllMember | AllMember/A | AllMember/X | | AllMember | AllMember/A/A1 | AllMember/X | | AllMember | AllMember/A/A2 | AllMember/Y | | AllMember | AllMember/B | AllMember/Y | | AllMember/J | AllMember/C | AllMember/Y | | AllMember/K | AllMember/C/C1 | AllMember/Z | | AllMember/K | AllMember/C/C2 | AllMember/Z | Applying **ReverseHierarchize** to the full set produces: | Hierarchy 1 | Hierarchy 2 | Hierarchy 3 | | ----------- | -------------- | ----------- | | AllMember | AllMember | AllMember | | AllMember | AllMember/B | AllMember/Y | | AllMember | AllMember/A | AllMember/X | | AllMember | AllMember/A/A2 | AllMember/Y | | AllMember | AllMember/A/A1 | AllMember/X | | AllMember/K | AllMember/C/C2 | AllMember/Z | | AllMember/K | AllMember/C/C1 | AllMember/Z | | AllMember/J | AllMember/C | AllMember/Y | If the **level argument is set to Level 1 of Hierarchy 2** (A, B, and C), the result is: | Hierarchy 1 | Hierarchy 2 | Hierarchy 3 | | ----------- | -------------- | ----------- | | AllMember | AllMember | AllMember | | AllMember | AllMember/B | AllMember/Y | | AllMember | AllMember/A | AllMember/X | | AllMember | AllMember/A/A1 | AllMember/X | | AllMember | AllMember/A/A2 | AllMember/Y | | AllMember/J | AllMember/C | AllMember/Y | | AllMember/K | AllMember/C/C1 | AllMember/Z | | AllMember/K | AllMember/C/C2 | AllMember/Z | #### Syntax ``` <- ReverseHierarchize( : set [, : level] ) ```