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”).
DropDown¶
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.
DropDownNoDrill¶
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.
DropDownNonEmpty¶
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")