The CGETMDX formula is used to retrieve a value based on the MDX statement and the intersection of the row and column.
CGETMDX (Datasource, Catalog, MDX, RowIndex, ColumnIndex)
Specify the Analysis Services server that you want to connect to.
Specify the catalog of cubes on the datasource that you want to perform your query on.
Specify the MDX statement to query on.
Specify the value of the row index.
Specify the value of the column index.
To retrieve a value based on the MDX statement and the intersection of the row and column.
All the arguments are compulsory.
The easiest way to generate an example of CGETMDX is to use the ‘Send as MDX’ command from the Data -> View window. First, create a simple view from one of your cubes and then select ‘Send as MDX’ (an option within the XLS icon). In the following example a very simple view from an Adventure Works Finance cube has been setup and is being sent as MDX to Excel.
This view should now appear in Excel as demonstrated below. Note that relevant cells will have CGETMDX formulas like cell C29 in the image below.
If you ungroup all columns and rows you will see how the MDX statement is setup and the row and columns referenced. Refer below.