SAPGetCellInfo limitations and add-in crashes

[Former Member]

The Analysis function SAPGetCellInfo is useful for determining the DATASOURCE, CROSSTAB, SELECTION or DIMENSION, but there are a few shortcomings, as of 1.4 SP3.1....

1. When requesting the SELECTION for a cell that has a selection with multiple dimensions, the order that the Dimensions are returned is not necessarily the same as the order specified/displayed by the crosstab. This can be problematic if you're trying to determine nesting levels, or cell consistency across refreshes.

2. When requesting the SELECTION for a cell that results in only 1 dimension, the array is one dimensional, and so developers must be able to handle one and two dimensional arrays. This adds complexity and performance degradation to any robust VBA solution.

3. When requesting the SELECTION for a totals cell, the dimensions that are totals are not included in the selection array. When the cell is a total across all dimensions, the function doesn't return any dimensions in the selection array, and instead returns Error 2015 (which is an Excel #VALUE! error). Developers should be aware that Error 2015 implies that all dimensions are totals, and that the cell therefore does have a selection, while all other error values, such as Error 2042 and Error 2023, imply that an error actually occurred.

4. When requesting the SELECTION or a totals cell when "Compact data on Rows" is active AND measures is not the first dimension on rows, Analysis will crash.

5. When requesting the DIMENSION of a member attribute title/header cell, when "Compact Data on Rows" is active AND measues is not the first dimension on rows, Analysis will crash.

6. There isn't any easy way of determining if a cell/column is a Scaling Factor cell/column.

7. If the cell represents the member text or key, there isn't an easy way of determining whether it is the key or the text that is being displayed

8. If the cell represents a member attribute text or key, there isn't an easy way of determining which attribute is being represented, or whether it is the key or the text that is being displayed.

As points 7 and 8 relate to member attributes that might be displayed in the crosstab, there might be a use for a function such as SAPListOfDimensionAttributes that would accept 2 arguments: Data Source Alias and Dimension Technical Name, and that would return an array of attribute texts and keys.