The CGETLIST formula is a variant of the CGET formula and is similarly used to retrieve a data point value based on the hierarchy information passed into the formula. Where a hierarchy from the targeted cube is not specified, the default member for that hierarchy is used.
This formula behaves differently when it is published by giving the user a drop-down list on the cell.
CGETLIST (Datasource, Catalog, Cube, OptionsRange, Hier1, Hier2…Hier20)
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 cube that you want to perform your query on.
The two dimensional range of cells that contains the list of values to use. The first column in the range will be used as the display values in the list and the second column in the range will be the value that is written back to the cube.
For example, the following will show a list with Brisbane, Melbourne and Sydney as selectable items, but the values 1, 2, or 3 are what is written back to the data point depending on the user’s selection:
Specify the name of a valid member in the cube’s first hierarchy.
Specify the name of a valid member in the cube’s nth hierarchy. Up to 20 hierarchies are allowed to be specified.
When you are building a published report for user input and you have data points where you are storing values that are from a standard set, for example, a Yes/No answer, or a list of regions, the CGETLIST formula should be used to give the user a friendly list-style interface to select a value from.