Undocumented Analysis Functions and Arguments
It’s clear that the Analysis User Guide (1.4 2013-05-30) isn’t up to date with Analysis Add-in (1.4 SP3.1), but I do remember seeing somewhere that SAP plan to revise the document soon. Hopefully my findings below are reflected in the revised user guide. In the interim, here are functions and arguments that I’ve discovered….
These functions and arguments are undocumented, but that doesn’t necessarily mean that they’re unsupported – They’re just not included in the user guide. They become evident when you convert crosstabs to formulas, and they’re visible to the Excel Function Wizard – so the Add-In is using these functions already. I’ve confirmed that they work in 1.4 SP0 and 1.4 SP3.1.
There may be more functionality that I’m not aware of, so please comment if you find any more features that aren’t in the user guide
Functions:
SAPGetUniformScaling – Returns the unit/scaling information of a dimension member comnbination
This function is available to the worksheet and VBA.
Arguments:
– Data Source – Formula Alias of Data Source.
– Measure – Name or technical name of a measure.
– Member Combination – Eg: ‘DIMENSION1=VALUE1;DIMENSION2=VALUE2’
Functions with undocumented Arguments:
SAPGetDimensionInfo
The User Guide states that this function accepts 3 arguments: Data Source, Dimension, and Property Name, and it states that Property Name can be either “NAME” or “ACTIVEHIERARCHY”.
The Excel Function Wizard shows that the function actually accepts up to 4 arguments when you’re handling Dimension Attributes:
– Data Source
– Dimension
– Property Name – Can actually be “NAME”, “ACTIVEHIERARCHY” or “ATTRIBUTE” – Seems to be case insensitive
– Attribute – Optional. When Property Name = “ATTRIBUTE”, this argument accepts the Technical name of the attribute.
SAPGetMember
The user guide alludes to to the capabilities of this function: “This function returns the dimension member or attribute.”, and while it does document how to retun Member details, it doesn’t document how to return Attribute details.
There isn’t an extra argument, but instead, the 3rd argument, “Member Display” will accept an Attribute Technical Name that is suffixed with “_Text” or “_Key”. There doesn’t seem to be a way of specifying Short, Medium or Long Text.
For example: SAPGetMember(“DS_1”, “CURRENCY1=GBP”, “CURRSYMBOL_Text”) would return the CURRSYMBOL attribute text for CURRENCY1 member GBP.
Hello Andrew -
This is very good information - many thanks for you to list down this.
However, I have an issue if I use this AO Function: SAPGETMEMBER to derive Attribute Text.
E.g. I use to derive attribute of Master Data Cost Center:
=SAPGETMEMBER("DS_1", "0COSTCENTER=100101", YXPOWNER_TEXT)
It has different behavior in different computer. In one computer, it can get the text; but in other computer, it cannot get the text.
For the one that cannot get the text, the AO SAPGETMEMBER return the Key instead of Text.
FYI, YXPOWNER is language-dependent text.
Do you know how to resolve this issue?
Kindly please ask your thought,
Many thanks,
Hi,
If they are undocumented, you can be pretty sure, they will also be unsupported 🙂 Not even documented features are properly supported by SAP...
Nevertheless, due to the very poor APIs, there seems to be no other way to do our job, than to use the undocumented features.
Oliver
Hi,
Thank you "Former Member" for sharing the information.
The SAP documentation about the possibilities of the Analysis functions is still very brief.
I'd like to show my appreciation by sharing my today's finding:
I want to get the attribute value of a dimension member dynamically by referring to a cell:
The following gives a formula error in my Excel version (or Analysis version):
=SAPGetMember("DS_1";"0SOLD_TO="A1;"0NAME")
Adding an ampersand in between (&) it works for me:
=SAPGetMember("DS_1";"0SOLD_TO="&A1;"0NAME")
For the above example it is not applicable, but in order to get the description (text) of the attribute value instead of it's key it worked for me to add _Text behind the technical name of the dimension attribute (as Former Member explains).
=SAPGetMember("DS_1";"0SOLD_TO="&A1;"0NAME_Text")
It might be that the suffix _Text is case sensitive.
Kind regards.
Kind regards.