XLCubed MDX Calculation Support

For some data sources that do not natively support MDX XLCubed can execute calculations on top of the source database.

Currently this is available for Fluence and Anaplan connection types.

Supported Functions

Documentation for these functions can be found in the MDX function reference.

Set functions

• + - union operator
• * - crossjoin operator
• { } - braces
  • Multiple members/tuples can be combined
  • Range operator  : is supported [1]
• Ancestors
• Children
• Crossjoin
• Except
• Filter
• Head
• Intersect
• Item
• LastPeriods
• Mtd
• PeriodsToDate
• Qtd
• Siblings
• Subset
• Tail
• Union
• Ytd

Member functions

• Ancestor
• Ascendants
• ClosingPeriod
• Cousin
• CurrentMember
• DefaultMember
• Descendants
• FirstLast
• FirstSibling
• Is (keyword)
• Item
• Lag
• LastChild
• LastSibling
• Lead
• Level
• NextMember
• NextSibling
• OpeningPeriod
• ParallelPeriod
• Parent
• PrevMember
• PrevSibling

Aggregation funtions

• Aggregate
• Avg
• Count
• Max
• Min
• Median
• Sum

Text functions

• CStr
• Format
• Instr
• LCase
• Left
• Len
• LTrim
• Mid
• Right
• RTrim
• Trim
• UCase

Numeric functions

• Standard operators
  • +, -, *, /
• Abs
• CDate
• CDbl
• CInt
• Divide
• Log
• Log10
• Mod
• Power
• Round
• Sqrt

Logical functions

• IIf
• IsAncestor
• IsEmpty
• IsLeaf
• IsSibling

Hierarchy functions

• Levels
• Members

Level functions

• Members
• Ordinal

Financial functions

Information on these functions can be found in the VBA function reference

• DDB
• FV
• MIRR
• IPmt
• IRR
• NPer
• NPV
• Pmt
• PPmt
• PV
• Rate
• SLN
• SYD

XLCubed custom functions

• XL3TagToMember
  • XL3TagToMember(hierarchy, tag)
  • e.g. XL3TagToMember([Account], "#NetIncome")
• XL3TagToSet
  • XL3TagToSet(hierarchy, tag)
  • e.g. XL3TagToSet([Account], "#MyFlag")

Sample Calculations

These templates may be useful starting poitns for your own calculations. You should replace the Dimension, Hierarchy, Level and Measure names with your own.

Item

Many functions return a set and you might want to choose a particular member from that set. A set is always a set of tuples (not members) so you might need to use the Item() function twice to get a member from the set.

For example in this calculation gets the fourth child of the current Date member. As Children returns a set we pick out the tuple, and then use Item(0) to get the only member in that tuple.

( [Measure].[Amount], [Date].CurrentMember.Children.Item(3).Item(0) )

IRR, MIRR, NPV

These functions accept a list of values as an argument, not a set of tuples and a value expression like Sum(), Avg() etc.

For these to work you should specify the member of measures to use so that the calculation does not reference itself.

This will evaulate the values for "My Set" in the context of the IRR measure which can not work.

IRR( MySet ) - wrong!

Instead reference the measure you want to calculate for and create a set with that member in.

IRR( { [Measures].[My Measure] } * MySet )