CMEMBEROFFSET¶
The CMEMBEROFFSET formula is an enhanced member function that provides the ability to create reports such as rolling forecasts or rollover a year when applied to a time dimension. This can reduce report maintenance as you move to a new year or forecast period. CMEMBEROFFSET retrieves a member by index (offset) number. This function returns the member name that is a specified number of positions following the specified member along the member’s level. A value of -1 will return the previous member and a value of 1 will return the next member, then a value of 2 or -2 would skip a member, and so on.
Syntax
CMEMBEROFFSET (Datasource, Catalog, Cube, MemberUniqueId, Offset, ReturnProperty)
Arguments¶
Datasource : string (required)¶
Specify the Analysis Services server that you want to connect to.
Catalog : string (required)¶
Specify the catalog of cubes on the datasource that you want to perform your query on.
Cube : string (required)¶
Specify the cube that you want to perform your query on.
Member Unique ID : string (required)¶
Specify the Unique ID of the member which you want to retrieve information about.
Offset : number (required)¶
Specify the index (offset) number which a member is retrieved by.
Return Property : string¶
Specify either the intrinsic member property (MDX) or the member attribute that you want to display in the cell. Member attributes should be in the form [Dimension].[Hierarchy].[Attribute Name]
Default value: MEMBER_CAPTION
Usage Scenarios¶
To display multiple members based upon a single input member. It allows a report to become dynamic, without the user being fully conversant with MDX statements.
Examples¶
Column C is using cell B10 as the anchor. Relevant formulas are illustrated in column D.
Row 9 is using cell D8 as the anchor. Sample of formula for this row is in cell G10.
Note the member presented follows the hierarchy and level the initial member is selected from. If no member is available, the error #MDX! will be displayed.
Altering the Return Property to another value (ie Unary_Operator, Member_Unique_Name, Level_Number or any other intrinsic property or member attribute) will display the new value. For example, altering to Level_Number presents:
