Skip to content

CMEMBER

The CMEMBER formula is used to retrieve Member information from an Analysis Services cube and specify how it displays and behaves in Excel and Published Reports.

Syntax

CMEMBER (Datasource, Catalog, Cube, MemberUniqueId, ReturnProperty, AxisName, SubsetName, Behaviour, PublishLookAndFeel, CacheState, MultiSelectMode, MultiSelectText, Level)

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.

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]”.

Axis Name : string

Specify the one of the following axis that this member applies to.

Available types are:

"Row", "RowDrill", "RowDrillResizeNone", "RowDrillResizeParent", "Column", "ColumnDrill", "ColumnDrillResizeNone", "ColumnDrillResizeParent", "Slicer"

Default value: Row

Row

Inserts children above or below depending on the value set in the CMEMBER’s behaviour argument.

Use “RowDrill” and its “RowDrill” variants for the behavior to also effect the Published Report.

RowDrill

Inserts children above or below depending on the value set in the CMEMBER’s behaviour argument and will resize the column you clicked on to the size of its largest item up to 250 characters.

RowDrillResizeNone

Inserts children above or below depending on the value set in the CMEMBER’s behaviour argument and will not perform any resizing.

RowDrillResizeParent

Inserts children above or below depending on the value set in the CMEMBER’s behaviour argument and will resize the column you clicked on to the size of the cell you clicked on or the existing size, whichever is greater.

Column

Inserts children to the left or right depending on the value set in the CMEMBER’s behaviour argument.

Use “ColumnDrill” for the behaviour to also effect the Published Report.

ColumnDrill

Inserts children to the left or right depending on the value set in the CMEMBER’s behaviour argument and will resize the affected columns to the size of its largest item up to 250 characters.

ColumnDrillResizeNone

Inserts children to the left or right depending on the value set in the CMEMBER’s behaviour argument and will not perform any resizing.

ColumnDrillResizeParent

Inserts children to the left or right depending on the value set in the CMEMBER’s behaviour argument and will resize the affected columns to the size of the cell you clicked on.

Slicer

Will act as a selector where in Excel it will show Member Explorer and in Published Reports, it will show either a drop member or Member Explorer.

Subset Name : string

Specify the CALUMO subset that applies to this CMEMBER.

Behaviour : string

Available types are:

"AutoCalc", "InsertAfter", "InsertBefore"

Default value: InsertAfter

AutoCalc

Will recalculate the items on every refresh or calculation.

InsertAfter

Will either insert children above or below depending on the value set in the CMEMBER’s Axis argument.

InsertBefore

Will either insert children to the left or right depending on the value set in the CMEMBER’s Axis argument.

Publish Look and Feel : string

How the cell should render and the behaviour associated with that cell, when published.

Available types are:

"Text", "DropDown", "MemberList", "MemberListSingle", "DropDownNoDrill","DropDownNonEmpty","MemberListNonEmpty"

Default Value Condition
MemberList AxisName = Slicer and MultiSelectMode = Sum
Text AxisName is not Slicer
DropDown Otherwise
Text

Displays static text only for the selected member (this will be automatically selected when using an AxisName involving any kind of “Drill”).

Creates a drop down control for this cell so you can select members (Default).

MemberList

Allows you to double click the cell and display a popup window of the available members.

MemberListSingle

Like “MemberList” but in the popup window you can only select a single member.

Creates a drop down control that only lets you select from the current hierarchy.

The number of items that will be shown in any dropdown or list is capped by the MaxMemberCount setting in Server Configuration - which defaults to 1000.

Creates a drop down control that only lets you select from the current hierarchy. The member list is restricted with a Non-Empty clause based on the Subset Name argument. Subset Name must be a MDX expression.(Tuple, Set or Member UID)

Example

=CMEMBER("localhost","Adventure Works DW", "Adventure Works","[Account].[Accounts].&[1]","MEMBER_CAPTION","Slicer","{[Date].[Fiscal].&[2020],[Date].[Fiscal].&[2021]}","InsertAfter","DropDownNonEmpty")

MemberListNonEmpty

Allows you to double click the cell and display a popup Member explorer window of the available members. The member list is restricted with a Non Empty clause based on the Subset Name argument. Subset Name must be a MDX expression (Tuple, Set or Member UID). The two icons Manage Subset and Calculate Members, will be hidden from Member Explorer’s toolbar.

Example

=CMEMBER("localhost","Adventure Works DW", "Adventure Works","[Account].[Accounts].&[1]","MEMBER_CAPTION","Slicer","{[Date].[Fiscal].&[2020],[Date].[Fiscal].&[2021]}","InsertAfter","MemberListNonEmpty")

Cache State : string

The cache state of the cell.

Available types are: "Live", "Cached"

Default value: Live

Live

Will refresh the items after each calc and will not cache the selected values.

Cached

Will retain the selected members after a recalc or browser refresh. Only applied to Axis name slicer. Not applicable to Row or Column axis.

MultiSelectMode : string

Allows you to select multiple members from a members selector and have either an aggregated subset returned or the actual members selected. If you want to dynamically create MDX statements, use the set feature of the mutli select mode.

Available types are: "Sum", "Set", "SetWithCaption"

Default value: Sum

Sum

Will return the aggregate of the selected members as a custom subset. Return value cannot be used in calculated MDX. (Default).

Set

Will return members selected in the member explorer delimited by a comma. This resulting string can then be used in an MDX statement.

SetWithCaption

Same as Set but displays the member’s captions delimited by a comma. Up to ten captions will display, after which “…10+” is shown.

MultiSelectText : string

The text to display in the cell if MultiSelectMode is set to “Set”.

Default value: MultiSelect Member

Level : string (auto-generated)

This is an auto-generated argument that is populated with the current level of this member in the hierarchy.

Usage Scenarios

To retrieve Member information from an Analysis Services cube and dictate how it displays and behaves in Excel and Published Reports.

Inputs of Datasource, Catalog, Cube, and Member Unique ID are compulsory, while other arguments are optional.

Examples

=CMEMBER("localhost","Adventure Works DW", "Adventure Works","[Account].[Accounts].&[1]")

=CMEMBER("localhost","Adventure Works DW", "Adventure Works","[Account].[Accounts].&[1]","MEMBER_CAPTION","Slicer","_empty","InsertAfter","MemberList","Cached")

=CMEMBER("localhost","Adventure Works DW", "Adventure Works","[Account].[Accounts].&[1]","MEMBER_CAPTION","Slicer","_empty","InsertAfter","MemberList","Cached","Set","Select an Account")

Back to top