EvENE 
The EvENE (Nested expansion) function is an enhanced version of EvEXP. It supports sorting and ranking to allow more flexibility in building your expansion formulas. EvENE directly supports expansions across a single dimension or across multiple dimensions. Unlike EvEXP, with EvENE you supply your own MDX Set expression by defining the dynamic list of members. Fully qualified dimensions must be referenced, as the current view is not considered in the resulting values.
Syntax
EvENE(AppName, KeyRange, DataRange, SetExpression, ExpandDown, RepeatBlock)
Parameter |
Description |
|---|---|
AppName |
The current application name. |
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).
The KeyRange must contain the same number of rows (or columns) as dimensions in the SetExpression. End of the note. |
DataRange |
Specifies what 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). |
SetExpression |
The set expression is equivalent to the filter in an EvEXP function. It tells Planning and Consolidation what members to return and in what fashion to return them. The set expression can be an EvSET formula (see EvSET) or an MDX expression (see MDX Expressions for sorting and ranking). |
ExpandDown (Optional) |
TRUE = expand members by row (default). FALSE = expand members by column. |
RepeatBlock (Optional) |
Repeats a block of cells for each member. Works similar to DataRange, except blocks of cells are copied. |
Example
In the following example, the application name is Finance, the KeyRange is A31:A60, and the DataRange is B31:G60. The Set expression is as follows:
EVSET("WorldWide1","SELF_AND_BEFORE",99,TRUE,EVPXR("ENTITY","SalesUS","CURRENCY") & "=""USD""").
=EVENE("Finance",A31:A60,B31:G60,EvSET("WorldWide1","SELF_AND_BEFORE",99, TRUE,EVPXR("ENTITY","SalesUS","Currency") & "=""USD"""))
Notice that the Set expression formula contains two Ev functions, EvSET and EvPXR. The functions can be nested within EvENE and EvLST functions to help create the expansion formula. In the example above, the EvSET and EvPXR formulas are contained within the EvENE formula. You can also break these functions into separate cells and reference only those cells in your EvENE formula, so that maintaining "pieces" of the formula is easier.
Note
You should never use EvNXP for a nested expansion if you are using EvENE. For a nested expansion with EvENE, you include the nested expansion expression with the CrossJoin, NonEmptyCrossJoin, and so on, for defining the multidimension expansion. Then you must ensure that the KeyRange includes the same number of columns (for row expansion) or rows (for column expansion) as the dimensions you are expanding across.
For example, if your MDX expression is Crossjoin({[Revenue],[CostofSales],[GM]}, {[SalesItaly],[SalesFrance],[SalesUK]}) and you are expanding rows, then your KeyRange must be two columns wide.