The Nested expansion function allows you to expand one or more dimensions next to each other.
Note
You must have a minimum of two expands to perform a nested expansion.
The Nested Row report template and the Nested Row input schedule template use the nested expansion formula.
Syntax
EvNXP(KeyRange,DataRange,Filter,RepeatBlock,expand1,expand2,expand3...expand10)
KeyRange — The beginning point and ending point for the expansion. Must be a minimum of two rows (if ExpandDown is True) or two columns (if ExpandDown is False).
DataRange — Specifies which area of the report to copy when the report is expanded. This property maintains the report structure by copying the formulas and formatting of the first row of the range, and copying it to all the new rows upon expansion. Must be a minimum of two rows (if ExpandDown is True) or two columns (if ExpandDown is False).
Filter — (Optional) Bases expansion on member properties, and specifies fixed dimension members.
RepeatBlock — (Optional) Repeats a block of cells for each member. Works similarly to DataRange, except that blocks of cells are copied.
Expand1-N — Represent the Expansions in the report or input schedule.
Example
You use the following formula: EVNXP($B$1,$B$2,A17:A40,B17:H40,,EvMembers,,FALSE,$A$2&.currentmember.properties(&C2&) = &D2&)
where $A$2 is the dimension name, &C2& is the property name, and &D2& is the property value.
If Account.Revenue = INC
, then all income accounts would be displayed in the KeyRange. If you change the Account from Revenue to CostofGoodsSold, the ACCTYPE would
change to EXP, and all expense accounts would be displayed in the key range. Cell C2 must contain the property name (that is, ACCTYPE) and cell D2 must contain an EvPRO formula that returns the property value
of the member/property combination.