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)

Arguments

Datasource

(Required)

Specify the Analysis Services server that you want to connect to.

Catalog

(Required)

Specify the catalog of cubes on the datasource that you want to perform your query on.

Cube

(Required)

Specify the cube that you want to perform your query on.

Member Unique ID

(Required)

Specify the Unique ID of the member which you want to retrieve information about.

Return Property

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

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

Default value: MEMBER_CAPTION

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

Specify the CALUMO subset that applies to this CMEMBER

Behaviour

Default value: InsertAfter

AutoCalc

Will recalc the items on every refresh or calc.

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

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

Default Value: DropDown

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.

Cache State

The cache state of the cell.

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.

MultiSelectMode

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.

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

MultiSelectText

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

Default value: “”

Usage Scenarios

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

Input of value 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")