orkungedik/SAP-basis-dataset huggingface dataset:
## 3.13.20  SAPListOfDynamicFilters
This function returns a list of all dynamic filters of a data source. Dynamic filters are defined by the user using Filter by Member .
The formula consists of 2 parameters and is made up of the following arguments:
- •
- Data Source Enter the formula alias for the data source. You can set the alias when configuring the data source on the
Components tab in the design panel.
- Member Display
You can enter TEXT , KEY or INPUT\_STRING to define how the filtered members should be displayed in the workbook.
## Example
Cell G15: =SAPListOfDynamicFilters("DS\_1";"TEXT")
If you enter the formula in cell G15, the dynamic filters of data source DS\_1 are displayed as text.
## 3.13.21  SAPListOfEffectiveFilters
This function returns a list of all effective filters of a data source: Dynamic filters defined by the user, static filters defined in the underlying source, and filters by measure defined for the data source dimensions.
The formula consists of 2 parameters and is made up of the following arguments:
- Data Source Enter the formula alias for the data source. You can set the alias when configuring the data source on the
Components tab in the design panel.
- Member Display
You can enter TEXT , KEY or INPUT\_STRING to define how the filtered members should be displayed in the workbook. This parameter is optional. The current display selected for members is the default setting. If two options exist, the first one is applied.
## Example
Cell F20: =SAPListOfEffectiveFilters("DS\_1";"TEXT")
You have added dimension Region from data source DS\_1 to your analysis. You filter this dimension and the following members are part of the analysis: California, Arizona, Florida, Nevada. If you enter the formula in cell F20, the name of the dimension is displayed in cell F20, and the four filtered members are listed as text in cell G20.
## 3.13.22  SAPListOfMeasureFilters
This function returns a list of all measure filters including BEx conditions. The list contains two columns: the first column lists the filter type (BEX, DIMENSION, MEASURE) and the second with filter values.
The formula consist of three parameters and is made up of the following arguments:
## · Data Source
Enter the formula alias of the data source or planning object. You can set the alias when configuring the data source or planning object on the Components tab in the design panel.
## · Display
You can enter ALL, DIMENSION, MEASURE or BEX to define which measure filters should be displayed in the workbook:
- ALL returns a list of all measure filters.
- DIMENSION returns a list of filter by measure on all dimensions or a defined one.
- MEASURE returns a list of filter by measure on data cells.
- The list also returns the filter type: All Dimensions Independently, Most Detailed Dimension in Rows or Most Detailed Dimension in Columns.
- BEX returns a list of BEx conditions.
This parameter is optional. ALL is the default value. You can also combine values of this parameter.
## · Name
Enter the technical name of the dimension if you have selected DIMENSION in the display parameter. This parameter is optional. If you do not enter a dimension name, all dimensions are listed.
## Example
Cell G20: =SAPListOfMeasureFilters("DS\_1";"BEX")
Starting in cell G20, a list is displayed with all BEx conditions in data source DS\_1.
## 3.13.23  SAPListOfMembers
This functions returns a list of members for a selected dimension or prompt. Each list entry contains the key, text, and key as filter string of a member. The key as filter string differs from the key if the member is compounded to another dimension.
You cannot use this function to list the members for a dimension with hierarchies.
The formula consists of five parameters and is made up of the following arguments:
## · Data Source
Enter the formula alias of the data source or planning object. You can set the alias when configuring the data source or planning object on the Components tab in the design panel.
You have to define this parameter, irrespective of whether the variables in the workbook are merged or not.
## · Source
Enter one of the following sources:
- PROMPT to get a list of the prompt members. If you use PROMPT as source, you have to define the data source alias, irrespective of whether the variables in the workbook are merged or not.
- FILTER to get a list of the dimension members.
- FILTER\_MASTERDATA to get a list of all members of the master data for the selected dimension.
- PLAN\_PARAMETER to get a list of all members for a variable in a planning object.
## · Name
Enter the technical name of a flat dimension or prompt whose members should be listed.
## · Search
You can enter a search string to restrict the list of members. This parameter is optional.
## · Search in
Enter whether the search should be executed in the KEY or TEXT column. This parameter is optional. The default entry is KEY.
## · Count
You can specify (restrict or enlarge) the number of members that can be returned by this formula. The number specified in user option Maximum Number of Members Displayed in the Filter Dialog is used as default.
## Example
Cell G15: =SAPListOfMembers("DS\_1";"FILTER";"0CALMONTH";"12";"KEY")
Starting in cell G15, a list is displayed with all members of dimension 0CALMONTH that contain search string '12' in their technical name (KEY).
## 3.13.24  SAPListOfMessages
This function includes messages into the sheet. You can select which messages , according to their level of severity, and details should be displayed in the sheet.
The formula consists of 2 parameters and is made up of the following arguments:
## · Severity
You can enter the following severities: INFORMATION, WARNING, ERROR and CRITICAL.
INFORMATION is the least severe category, CRITICAL the most severe. If you select a severity, all messages are shown which have this severity or higher. If you select WARNING, for example, all messages with severity WARNING, ERROR and CRITICAL are displayed.
## · Show Details
If you enter FALSE, the messages are listed with severity and short text.
If you enter TRUE, the messages are listed with additional information. The details are listed in the following sequence:
1. Message ID
The message ID is a unique identifier for a message that is valid for the current messages dialog. If the same message is displayed again at a later time, it has a different message ID. The message ID can be used as identifier in the API methods SAPAddMessage and SAPSuppressMessage.
2. Short Text The message short text is displayed.
3. Message Class
If the message comes from a BW back-end system, the message class is displayed.
4. ID For Analysis messages, the notification ID is displayed. For messages from a BW back-end, a concatenation of the message class and the message number is displayed.
5. Severity
The message severity is displayed.
6. Variables: VARIABLE1-VARIABLE4 If the message comes from a BW back-end system, the message can comprise up to four variables to support dynamic message texts.
7. System ID
If the message comes from a BW back-end system, the system ID is displayed.
## Example
Cell H11: =SAPListOfMessages("ERROR";"FALSE")
In cell H11, all messages with severity ERROR and CRITICAL are displayed with severity and text.
## Related Information
Using Analysis functions [page 129] SAPAddMessage [page 101]
SAPSuppressMessage [page 122]
## 3.13.25  SAPListOfStaticFilters
This function returns a list of all static filters of a data source. Static filters are defined in the underlying source and cannot be changed by the user.
The formula consists of 2 parameters and is made up of the following arguments:
- Data Source Enter the formula alias for the data source. You can set the alias when configuring the data source on the
Components tab in the design panel.
- Member Display You can enter TEXT , KEY or INPUT\_STRING to define how the filtered members should be displayed in the workbook.
## Example
Cell G15: =SAPListOfStaticFilters("DS\_1";"TEXT")
If you enter the formula in cell G15, the static filters of data source DS\_1 are displayed as text.
## 3.13.26  SAPListOfVariables
This function returns a list of all variables of a data source or planning object.
The formula consists of 3 parameters and is made up of the following arguments:
## · Data Source
Enter the formula alias of the data source or planning object. You can set the alias when configuring the data source or planning object on the Components tab in the design panel.
Please note the following behavior for not merged / merged variables:
If the variables in the workbook are not merged, you have to define the data source alias, and the formula returns all variables of the defined data source.
If the variables in the workbook are merged, the formula returns the merged variables for all data sources. You can define a data source alias, but it will be ignored, and the formula returns the list with the merged variables. Therefore, we recommend not to define a data source alias when working with merged variables. You can leave the parameter empty ("") or enter all ("All") to specify that the fomula returns the merged variables for all data sources.
## · Member Display
You can enter TEXT , KEY or INPUT\_STRING to define how the filtered members should be displayed in the workbook.
This parameter is optional. The current display selected for members is the default setting. If two options exist, the first one is applied.
## · Display
Enter one of the following values:
- ALL to display all variables (filled and unfilled) including variables not visible on the prompts dialog.
- PROMPTS to display all variables (filled and unfilled) visible on the prompts dialog.
- ALL\_FILLED to display all filled variables including variables not visible on the prompts dialog.
- PROMPTS\_FILLED to display all filled variables visible on the prompts dialog.
- PLAN\_PARAMETER to display all variables (filled and unfilled) of a planning object.
This parameter is optional. The default value is ALL\_FILLED.
## Example
Cell F20: =SAPListOfVariables("DS\_2";"TEXT")
If you enter the formula in cell F20, all variables with values in data source DS\_2 are listed with their values in the worksheet. The first variable name is displayed in cell F20, the next in cell F21 and so on. The corresponding values are listed in G20, G21 and so on.
## 3.13.27  SAPSelectMember
This function returns a member of a dimension.
You can use the returned value to simplify the syntax for function SAPGetData.
The formula consists of five parameters and is made up of the following arguments:
## · Data Source
Enter the formula alias for the data source. You can set the alias when configuring the data source on the Components tab in the design panel.
## · Member
Enter the name, technical name or generated member (by value help) of a dimension member.
- Dimension
Enter the dimension name or technical name.
## · Member Display
You can enter TEXT, KEY or INPUT\_STRING to define how the members should be displayed in the workbook.
## · Selection Mode
With the selection mode, you can define which filter dialog (as icon) is selectable next to the cell. The options are: NONE, SIMPLE or FULL. Using NONE, no filter icon will be selectable next to the cell. Using SIMPLE, a simple version of the filter dialog will be opened where you can select other members of the dimension. Using FULL, the full version of the filter dialog will be opened where you can also search for members and change the display (from Text to Key, for example).
The default value is SIMPLE. This parameter is optional.
## Example
Cell D10: =SAPSelectMember("DS\_1";"France";"0COUNTRY";"TEXT";"SIMPLE")
The formula in cell D10 selects the member of a dimension of data source DS\_1. The name of the dimension is 0Country. The name of the member France. The value (France) that is set is selected and displayed in cell D10. With selection mode SIMPLE, a filter icon is available next to cell D10 that you can use to open the filter dialog and select another member of dimension 0COUNTRY.
## Related Information
SAPGetData [page 79]
## 3.13.28  SAPSetData
With this function, you can set planning values. You can either enter the value(s) in the formula directly or you can refer to another cell in Microsoft Excel that contains the value(s).
The formula consists of five parameters and is made up of the following arguments:
## · Data Source
Enter the formula alias for the data source. You can set the alias when configuring the data source on the Components tab in the design panel.
## · New Value
You can enter a value manually or reference a cell in Microsoft Excel that contains the value.
##  Note
If you reference a cell, the cell must not be part of a crosstab in Analysis.
## · Option
You can use options to define the behavior of the formula. If you use more than one option in a formula, use a semicolon-separated list to enter the options.
This parameter is optional. If you do not need to define an option for the formula, you can enter an empty parameter.
The following options are available:
- SetOnly
With this option set, the formula does not adopt values in a referenced cell if the values in the data cell were changed, for example after executing a planning function.
- NoScaling With this option set, the scaling factor of the data cell in the crosstab is not included for setting the
value.
Example: The scaling factor for a data cell is set to 100. The data cell shows the value 3, the 'real' value of the data cell is 300. If you want to change the 'real' value to 400, you can set the value 4 or you set the value 400 using the option NoScaling. Due to the scaling factor, the data cell will show value 4.
## · Measure
Enter the name of measure, for example "Incoming Orders" .
## · Member combination
There are three methods for entering the member combination:
- Enter one parameter as the member combination, for example "Region=France;Product=Services" .
- Enter several parameters as the member combination, for example
"Region";"France";"Product";"Services" . This method can only be entered manually. It is recommended for member combinations that use cell references.
- Enter a mixture of both: one parameter per dimension/member combination, for example "Region=France";"Product=Services" .
When a data source is initially refreshed, the formula is evaluated and the value after the formula is set in the data source. If the target value is changed, manually or in the referenced cell, the new value is set in the data source. But a recalculation is not triggered automatically and the crosstab still shows the old value. With this behavior, you can change several values and then recalculate all changes in one step by executing the recalculation manually.
## Example
Cell D10: =SAPSetData("DS\_1"; H5;;"Incoming Orders";"Region";"France";"Product";"Services")
The formula in cell D10 defines the value for a measure and member combination of data source DS\_1. The name of the measure is 'Incoming Orders'. The member combination is 'France' and 'Services'. The value that is set is taken from cell H5. Therefore, cell H5 must not be part of the crosstab.
The parameter option is not defined in the formula.
## Related Information
SAPGetData [page 79] Syntax for Entering Values [page 140]
## 3.13.29  SAPSetFilterComponent
This function creates a filter component and sets the members selected by the user as a filter. You can click the filter icon to change your filter definition in a dialog box. This dialog box is a simplified version of the standard filter dialog.
This formula consists of 4 parameters and is made up of the following arguments:
- Data Source Enter the formula alias for the data source. You can set the alias when configuring the data source on the Components tab in the design panel.
- Dimension Name
Enter the technical name of the dimension.
- Target Data Source You can enter ALL or a list of formula aliases for data sources that should be affected.
## · Selection Type
Enter one of the following selection types:
- SINGLE With this selection type, you can select only one member for filtering.
- MULTIPLE With this selection type, you can select multiple members for filtering. This is the default selection
type.
- LOWERBOUNDARY With this selection type, you can define a member as a lower boundary, for example a date.
- UPPERBOUNDARY
With this selection type, you can define a member as an upper boundary, for example a date. You can also insert two filter components in your analysis to define a period of time with a lower boundary date and an upper boundary date.
- MEMBERSELECTOR With this selection type, you can define that the standard filter dialog with individual selection is used for the filter component.
- MEMBERSELECTORBYRANGE With this selection type, you can define that the standard filter dialog with range selection is used for
the filter component.
## Example
Cell E25: =SAPSetFilterComponent("DS\_1";"0DIVISION";"ALL";"MULTIPLE")
If you enter the formula in cell E25, the members that are currently filtered are displayed in cell E25. If you select the filter icon next to cell E25, you can change your filter definition.
## Example
Cell G25: =SAPSetFilterComponent("DS\_1";"0DIVISION";"DS\_1;DS\_2";"MULTIPLE")
If you enter the formula in cell G25, the members that are currently filtered for the data sources DS\_1 and DS\_2 are displayed in cell G25. If you select the filter icon next to cell G25, you can change your filter definition and it will be applied to the data sources DS\_1 and DS\_2.
## 3.14 Working with macros
Analysis contains API methods that can be used in VBA macros that are embedded in Microsoft Office Excel workbooks. Macros are created in the Visual Basic Editor. The Visual Basic Editor can be used to write and edit a macro that is attached to a Microsoft Office Excel workbook. The macros can be connected to UI elements that are available on the Developer tab in the menu.
You can also use the Analysis functions for creating formulas in VBA macros.
Microsoft Office documentation provides information about creating and using VBA macros. The following section describes the Analysis API methods.
The following methods are available in Analysis:
- SAPAddMessage
- SAPCallMemberSelector
- SAPExecuteCommand
- SAPExecutePlanningFunction
- SAPExecutePlanningSequence
- SAPGetCellInfo
- SAPGetProperty
- SAPLogOff
- SAPLogon
- SAPMoveDimension
- SAPOpenWorkbook
- SAPSetFilter
- SAPSetPlanParameter
- SAPSetRefreshBehaviour
- SAPSetVariable
- SAPSuppressMessage
- Table Design API
- SAPSetFormat
- SAPInsertLine
- SAPFixLineSize
- SAPListOfDesignRules
- SAPDeleteDesignRule
## 3.14.1  SAPAddMessage
With this API method, you can define messages and add them to the standard message dialog.
To call the method, use Application.Run and specify the following input parameters:
- Message
Enter the message text.
- Severity
You can enter one of the following severities: INFORMATION, WARNING, ERROR or CRITICAL. This parameter is optional. The default value is INFORMATION.
- Details
You can enter a long text with additional information to the message. This parameter is optional.
- Message ID
With the message ID, you can define a specific position in the message dialog where the new message should be added. The new message is inserted before the specified message ID. If the specified message ID does not exist in the message dialog, the new message is added as last message.
The message ID is a unique identifier for a message that is valid in the current messages dialog. It can be retrieved with function SAPListOfMessages .
This parameter is optional.
The system returns one of the following output parameters for each function execution:
- 0 = execution failed.
- 1 = execution successful.
## Example
## Dim lResult As Long
lResult= Application.Run("SAPAddMessage", "This is a new error message!", "ERROR")
The message 'This is a new error message' with severity Error is displayed in the message dialog.
## Related Information
SAPListOfMessages [page 94]
SAPSuppressMessage [page 122]
## 3.14.4  SAPExecutePlanningFunction
With this API method, you can execute a planning function. Planning functions are maintained in the Components tab in the design panel.
To call the method, use Application.Run and specify the following input parameters:
- Planning Function Alias Enter the formula alias of the planning function. You can set the alias when configuring the planning function on the Components tab in the design panel.
The system returns one of the following output parameters for each function execution:
- 0 = execution failed.
- 1 = execution successful.
## Example
Dim lResult As Long lResult= Application.Run("SAPExecutePlanningFunction","PF\_1")
If you select this command, planning function PF\_1 is executed.
## 3.14.5  SAPExecutePlanningSequence
With this API method, you can execute a planning sequence. Planning sequences are maintained in the Components tab in the design panel.
To call the method, use Application.Run and specify the following input parameters:
- Planning Sequence Alias Enter the formula alias of the planning sequence. You can set the alias when configuring the planning sequence on the Components tab in the design panel.
The system returns one of the following output parameters for each function execution:
- 0 = execution failed.
- 1 = execution successful.
## Example
## Dim lResult As Long
lResult= Application.Run("SAPExecutePlanningSequence","PS\_1")
If you select this command, planning sequence PS\_1 is executed.
## 3.14.6  SAPGetCellInfo
With this API method, you can define a command to get information on a crosstab cell. You can define the commands for a selection and for a dimension.
For a Dimension call, the result contains the data source alias and the technical name of the dimension.
For a Selection call, the result contains a list of dimensions. Each list entry contains the data source alias, the technical name of the dimension and the technical name of the member. For measures, a list entry contains the data source alias, the measure structure and the technical name of the measure.
To call the method, use Application.Run and specify the following input parameters:
## · Cell
Enter the cell information for one cell of the worksheet. If the currently selected cell should be evaluated, enter ActiveCell.
If you have selected a range containing more than one cell, the method returns the cell information for the upper left cell in the range.
## · Property
Enter one of the following properties: SELECTION, DIMENSION, DATASOURCE or CROSSTAB.
## · Filter
For the SELECTION property , you can enter a list of dimensions to be evaluated, using semicolons as separator. If you do not enter any dimensions, all available dimensions are evaluated. For DIMENSION, DATASOURCE or CROSSTAB, you do not have to enter a value.
##  Tip
If you want to include a check in your program to check if the selected cell is part of the crosstab, you can use the named range of the crosstab as reference. You can find the named range in the Name box in the formula bar. You can modify it on the Components tab in the design panel.
## Example: Dimension
## Dim lResult As Variant
lResult= Application.Run("SAPGetCellInfo", ActiveCell, "DIMENSION")
If you select a dimension cell in the crosstab and execute the command, it returns the data source alias and the technical name of the dimension.
## Example: Selection
## Dim lResult As Variant
lResult= Application.Run("SAPGetCellInfo", ActiveCell, "SELECTION")
In your current analysis, you have filtered the dimensions 0MATERIAL and 0COUNTRY in rows and the measure 0SALESVOLUME in columns. If you select a data cell and execute the command, it returns a list
with three rows: One with the measure information, one with the information for dimension 0MATERIAL and one with the information for dimension 0COUNTRY.
## Related Information
The Components tab [page 148]
## 3.14.7  SAPGetProperty
You can use this API method to read properties of a data source, a crosstab or a workbook.
To call the method, use Application.Run and specify the following input parameters:
## · Property Name
Enter one of the following properties:
- IsDataSourceActive to check whether a data source is active.
- IsDataSourceEditable to check whether the data source is input-ready.
- HasChangedPlanData to check whether the workbook contains changed planning data.
- IsAutoRefresh to check whether the automatic refresh is active or paused on workbook level or for the specified data source.
- IsConnected to check whether a data source is already connected.
- LastError to get information on an error that occurred while executing Analysis API methods and functions.
- ChangedCrosstabs to get the crosstabs that have been changed in the last round trip with callback AfterRedisplay.
- ChangedDatasources to get the data sources that have been changed in the last round trip with callback AfterRedisplay.
- NumberOfNewLines to get the number of new lines for a crosstab.
To set the number of new lines for a crosstab, you can use the API method SAPExecuteCommand with command NumberOfNewLines.
## · Formula Alias
Enter the formula alias for the data source. You can set the alias when configuring the data source on the Components tab in the design panel. This parameter is optional.
If you use the NumberOfNewLines property, enter the formula alias for the crosstab. You can set the alias when configuring the crosstab on the Components tab in the design panel.
If you use the HasChangedPlanData or LastError property, you should not enter a data source alias, as this property is not related to a specific data source but to the complete workbook.
## Example: IsDataSourceEditable
```
Dim Result As Variant Result= Application.Run("SAPGetProperty", "IsDataSourceEditable", "DS_1")
```
If you execute the command, it returns the Boolean value. If the data source DS\_1 is editable, the value is True.
## Example: IsConnected
```
Result = Application.Run("SAPGetProperty", "IsConnected", "DS_1")
```
If you execute the command, it returns the Boolean value. If the data source DS\_1 is connected, the value is True.
## Example: NumberOfNewLines
```
Result = Application.Run("SAPGetProperty", "NumberOfNewLines", "Crosstab_1")
```
If you execute the command, it returns the number of new lines added to crosstab Crosstab\_1.
## Related Information
Using LastError information [page 138] SAPExecuteCommand [page 104]
## 3.14.8  SAPLogOff
You can use this API method to disconnect/log off a workbook from the server or to trigger a reconnect to the system.
You can also log off and reconnect a workbook manually in Analysis. The options are available in the context menu for workbooks on the Components tab in the design panel.
To call the method, use Application.Run and specify the following input parameters:
- Boolean Parameter for logging off and reconnecting to a system This parameter is optional. You can enter the following values:
- True: With this value, the user gets logged off from the system and will be reconnected automatically.
- False: With this value, the user gets logged off from the system.
The system returns one of the following output parameters for each command execution:
- 0 = execution failed.
- 1 = execution successful.
## Example
Dim lResult As Long lResult= Application.Run("SAPLogOff", True )
If you execute this command, the workbook gets logged off from the system and then reconnected again.
## Related Information
Managing system connections in the design panel [page 159]
## 3.14.9  SAPLogon
You can use this API method to trigger a logon to a system for a specified data source.
To call the method, use Application.Run and specify the following input parameters:
- Formula Alias
Enter the formula alias for the data source. You can set the alias when configuring the data source on the Components tab in the design panel.
- Client
Enter the system client. This parameter is only relevant for logon to an SAP system.
- User
Enter the user.
- Password
Enter the password.
- Language
Enter the logon language. This parameter is optional.
The system returns one of the following output parameters for each command execution:
- 0 = execution failed.
- 1 = execution successful.
## Example
lret = Application.Run("SAPLogon", "DS\_1", "000", "Anzeiger", "Display")
If you execute the command, the system of data source DS\_1 with client 000 will be connected for user Anzeiger with password Display.
## 3.14.10  SAPMoveDimension
With this API method, you can define the position of a dimension in the crosstab, swap the dimension with another one or position a dimension relative to another one.
Note: In a grouped crosstabs, you can only move dimensions to or within the master crosstab.
To call the method, use Application.Run and specify the following input parameters:
## · Data Source Alias
Enter the formula alias for the data source. You can set the alias when configuring the data source on the Components tab in the design panel.
- Dimension Key
Enter the technical name of the dimension.
## · PositionBy
Enter one of the following constants:
- ROWS to move a dimension to the rows.
- COLUMNS to move a dimension to the columns.
- FILTER to move a dimension to the background filter.
- AFTER to move a dimension after a relative dimension.
- BEFORE to move a dimension before a relative dimension.
- SWAP to swap a dimension with a relative dimension.
## · Identifier
If PositionBy is set to AFTER, BEFORE or SWAP, enter the technical name of the relative dimension. If PositionBy is set to ROWS, COLUMNS or FILTER, enter the position as an index. The index is 1-based and optional. If the index is not valid, the command will not be executed. If no index is specified, the dimension is added at the end.
The system returns one of the following output parameters for each function execution:
- 0 = execution failed.
- 1 = execution successful.
## Example
Dim lResult As Long lResult= Application.Run("SAPMoveDimension", "DS\_1", "0COUNTRY", "AFTER", "0CALMONTH" )
If you you execute this command, dimension 0COUNTRY will be inserted in the crosstab to the position after dimension 0CALMONTH.
## Example
## Dim lResult As Long
lResult= Application.Run("SAPMoveDimension", "DS\_1", "0COUNTRY", "ROWS", "2")
If you execute this command, dimension 0COUNTRY is added as second entry to the rows in the crosstab.
## Related Information
Grouping crosstabs [page 61]
## 3.14.12  SAPSetFilter
With this API method, you can define which members of a dimension should be filtered. You can also use this method to define the filter for a planning function.
Note: In a grouped crosstab, you can only filter the dimensions of the data source of the master crosstab.
To call the method, use Application.Run and specify the following input parameters:
- Formula Alias Enter the formula alias for the data source or planning function. You can set the alias when configuring the data source on the Components tab in the design panel.
## · Dimension
Technical name of the dimension to be filtered.
## · Member
String that represents the member filter for the dimension, for example technical names or a variable. The "ALLMEMBERS" string or an empty string clears the filter and selects all members. Note the syntax rules for entering values.
- Member Format
- Text
Single member as text.
- Key
Single member as key.
- INTERNAL\_KEY
Single member with its internal key.
- INPUT\_STRING
Complex selection of members.
- INPUT\_STRING\_AS\_ARRAY Returns the input string as string as array.
- LITERAL
Single member with its internal key.
With this value format, no immediate validation with the back-end system is executed. This improves the performance.
This format is only valid if you set the prompt value with its internal key and you use the syntax rule Equal To .
If the entered member is not valid, a message is displayed.
This parameter is optional. The default value is INPUT\_STRING.
##  Note
The KEY and INTERNAL\_KEY depend on the InfoObject modeling in SAP BW.
The system returns one of the following output parameters for each function execution:
- 0 = execution failed.
- 1 = execution successful.
## Example
## Dim lResult as long
lResult= Application.Run("SAPSetFilter", "DS\_1", "0SOLD\_TO\_\_0COUNTRY", "CA;US;DE", "INPUT\_STRING")
With this example, you set the filter for dimension 0SOLD\_TO\_\_0COUNTRY of data source DS\_1 to the countries USA, Canada and Germany using member format INPUT\_STRING.
## Related Information
Syntax for Entering Values [page 140] Grouping crosstabs [page 61]
## 3.14.13  SAPSetPlanParameter
With this API method, you can define values for input-ready variables of planning objects.
To call the method, use Application.Run and specify the following input parameters:
## · Formula Alias
Enter the formula alias for the planning object. You can set the alias when configuring the planning object on the Components tab in the design panel.
- Variable Name Name or technical name of the variable to be filtered.
## · Variable Value
String that represents the value for the variable, for example the key. Note the syntax rules for entering values.
## · Member Format
- Text
Single member as text.
- Key
Single member as key.
- INTERNAL\_KEY
Single member with its internal key.
- INPUT\_STRING Complex selection of members.
- LITERAL
Single member with its internal key.
With this value format, no immediate validation with the back-end system is executed. This improves the performance.
This format is only valid if you set the prompt value with its internal key and you use the syntax rule Equal To .
If the entered member is not valid, a message is displayed.
This parameter is optional. The default value is INPUT\_STRING.
##  Note
The KEY and INTERNAL\_KEY depend on the InfoObject modeling in SAP BW.
The system returns one of the following output parameters for each function execution:
- 0 = execution failed.
- 1 = execution successful.
## Example
## Dim lResult as long
lResult= Application.Run("SAPSetPlanParameter", "PF\_1", "0TARGET\_YEAR", "2013", "INPUT\_STRING")
With this example, you set the variable 0TARGET\_YEAR in planning function PF\_1 to the year 2013.
## Related Information
Syntax for Entering Values [page 140]
## 3.14.14  SAPSetRefreshBehaviour
With this API method, you can use different API methods one after the other without the result set being refreshed and without the message dialog being shown.
To call the method, use Application.Run and specify the following input parameters:
- Mode
Define whether the mode is On or Off .
- Message Display
Define whether the message display is On or Off . The default setting is Off .
The system returns one of the following output parameters for each function execution:
- 0 = execution failed
- 1 = execution successful
## Example
The default API call would look like this:
```
Public Sub SetGermanyAndPoduct01 Application.Run("SAPSetRefreshBehaviour","Off") Application.Run("SAPSetFilter","DS_01","0COUNTRY","DE") Application.Run("SAPSetFilter","DS_01","0PRODUCT","P01") Application.Run("SAPSetRefreshBehaviour","On") End Sub
```
## 3.14.15  SAPSetVariable
With this API method, you can define values for input-ready variables (prompts). If you want to set multiple variables, you can use the PauseVariableSubmit command with the SAPExecuteCommand method.
To call the method, use Application.Run and specify the following input parameters:
- Prompt Name
Name or technical name of the variable to be filtered.
- Prompt Value
String that represents the value for the prompt, for example the key. Note the syntax rules for entering values.
- Value Format
- Text
Single member as text.
- Key
Single member as key.
- INTERNAL\_KEY
Single member with its internal key.
- INPUT\_STRING
Complex selection of members.
- INPUT\_STRING\_AS\_ARRAY
Returns the input string as string as array.
- LITERAL
Single member with its internal key.
With this value format, no immediate validation with the back-end system is executed. This improves the performance.
This format is only valid if you set the prompt value with its internal key and you use the syntax rule Equal To .
If the entered member is not valid, a message is displayed.
##  Note
The KEY and INTERNAL\_KEY depend on the InfoObject modeling in SAP BW.
## · Formula Alias
Enter the formula alias for the data source. You can set the alias when configuring the data source on the Components tab in the design panel.
You have to define this parameter if the variables in the workbook are not merged. If the variables in the workbook are merged, you can define this parameter but you do not have to define it.
The system returns one of the following output parameters for each function execution:
- 0 = execution failed.
- 1 = execution successful.
## Example
```
Dim lResult As Long lResult=Application.Run("SAPSetVariable", "0BWVC_COUNTRY", "DE", "INPUT_STRING",
```
```
"DS_1")
```
With this example, you set the variable 0BWVC\_COUNTRY in data source DS\_1 to country Germany.
## Example: Setting multiple variables
```
Call Application.Run("SAPSetRefreshBehaviour", "Off") Call Application.Run("SAPExecuteCommand", "PauseVariableSubmit", "On") Call Application.Run("SAPSetVariable", "0BWVC_COUNTRY", "US") Call Application.Run("SAPSetVariable", "0BWVC_MATERIAL", "Hardware")
```
```
Call Application.Run("SAPExecuteCommand", "PauseVariableSubmit", "Off") Call Application.Run("SAPSetRefreshBehaviour", "On")
```
With this example, you set the variable 0BWVC\_COUNTRY to country USA and the variable 0BWVC\_MATERIAL to material Hardware. Both variables will be executed at once after setting off the command PauseVariableSubmit.
## Related Information
```
Syntax for Entering Values [page 140] SAPExecuteCommand [page 104]
```
## 3.14.16  SAPSuppressMessage
With this API method, you can suppress messages in the standard message dialog. It can only be used in the callback BeforeMessageDisplay.
To call the method, use Application.Run and specify the following input parameter:
- Message ID
The message ID is a unique identifier for a message that is valid in the current messages dialog. It can be retrieved with function SAPListOfMessages .
The system returns one of the following output parameters for each function execution:
- 0 = execution failed.
- 1 = execution successful.
## Example
```
lret = Application.Run("SAPSuppressMessage", "5")
```
The message with message ID 5 is suppressed in the message dialog.
## Related Information
Using Callbacks [page 134]
SAPListOfMessages [page 94] SAPAddMessage [page 101]
## 3.14.17  Table Design API
Table Design API enables you to edit single elements in a crosstab with API methods (for example, you can add a new line to the crosstab).
The Table Design API offers methods for the following options:
- Applying formats
- Adding new lines
- Adding texts to new lines
- Fixing the line size
- Listing Table Design API methods for a specific data source
- Deleting Table Design API methods for a specific data source
## Related Information
Editing crosstabs with Table Design [page 50]
## 3.14.17.2  SAPInsertLine
With this API method, you can insert new lines in a crosstab.
To call the method, use Application.Run and specify the following input parameters:
## · RuleID
If you define a rule ID, you can address it in other API methods, for example for deleting the rule with SAPDeleteDesignRule.
If you do not define an ID, the system generates one automatically.
## · Data Source Alias
Enter the formula alias for the data source. You can set the alias when configuring the data source on the Components tab in the design panel.
## · Position
Enter one of the following positions: Before, After, BelowHeader or BesideHeader.
- PositionBy
Enter one of the following crosstab elements: Dimension , DimensionResult , DimensionGroup , DimensionMember , HierarchyNode or Tuple .
The constant TUPLE can be used to define a combination of members of various dimensions on an axis.
## · PositionBy parameters
If PositionBy is set to Dimension , DimensionResult or DimensionGroup , enter the technical name of the relative dimension.
If PositionBy is set to DimensionMember , enter the technical name of the relative dimension and member. If PositionBy is set to HierarchyNode , enter the technical name of the relative dimension, member and node type.
If PositionBy is set to Tuple , enter one of the following member types of the relative member with the corresponding attributes:
- MEMBER with the technical names of the dimension and the member as attribute.
- VIRTUALMEMBER with the rule ID of the inserted line as attribute.
- RESULTMEMBER with the technical name of the dimension as attribute.
- HIERARCHYNODE with the technical names of the dimension, the member and the node as attribute.
If you define a tuple, you have to specify placeholders for the parameters dimension, member and node type (see example 2).
## Example: Inserting a line after a dimension
## Dim lResult As String
lResult= Application.Run("SAPInsertLine", "NewLine1", "DS\_1", "After", "Dimension", "0CALYEAR")
A new line is inserted after the dimension 0CALYEAR in the crosstab of data source DS\_1. The rule ID for the new line is NewLine1.
## Example: Inserting a line after a tuple
```
Dim lResult As String Dim tupleArray(1) As String tupleArray(0)= "Member;0CALYEAR;2014"
```
```
tupleArray(1)= "Member;0COUNTRY;FR" lResult= Application.Run("SAPInsertLine", "NewLine2", "DS_1", "After", "Tuple",,,, tupleArray)
```
A new line is inserted after the combination of the members 2014 and France (FR) in the crosstab of data source DS\_1. The rule ID for the new line is NewLine2.
The three placeholders after the PositionBy element Tuple replace the attributes dimension, member and node type that are not relevant for the element Tuple.
## Example: Inserting a line after a hierarchy node
```
Dim lResult As String lResult= Application.Run("SAPInsertLine", "NewLine3", "DS_1", "After", "HierarchyNode", "0Country", "FR", "State")
```
A new line is inserted after the member France (FR) in the crosstab of data source DS\_1. The node type is
State. If the member France occurs several times in the crosstab, a new line is inserted after each occurrence. The rule ID for the new line is NewLine3.
## 3.14.17.3  SAPFixLineSize
With this API method, you can define the size for a inserted line.
To call the method, use Application.Run and specify the following input parameters:
- RuleID
If you define a rule ID, you can address it in other API methods, for example for deleting the rule with SAPDeleteDesignRule.
If you do not define an ID, the system generates one automatically.
- Data Source Alias Enter the formula alias for the data source. You can set the alias when configuring the data source on the
Components tab in the design panel.
- ID of new line Enter the rule ID of the inserted line whose size you want to fix.
- Size
Enter an integer number. The measurement unit for the size is pixels.
## Example
```
Dim lResult As String lResult= Application.Run("SAPFixLineSize", "SizeLine1", "DS_1", "NewLine1", "20")
```
The size of the new inserted line with ID NewLine1 is set to 20 pixels. The rule ID for the new line size is SizeLine1.
## 3.14.17.4  SAPListOfDesignRules
With this API method, you can list Table Design methods defined for a specific data source.
To call the method, use Application.Run and specify the following input parameters:
- Data Source Alias Enter the formula alias for the data source. You can set the alias when configuring the data source on the Components tab in the design panel.
- API Method Type
Enter one of the following types:
- All to list all methods.
- NewLine to list the inserted lines. You can specify the new lines in more detail by using NewDimensionLine, NewHeaderLine or NewMemberLine.
- LineSize to list the defined sizes for new inserted lines.
- Format to list the defined formats.
## Example
## Dim lResult As Variant
lResult= Application.Run("SAPListOfDesignRules", "DS\_1", "NewLine")
All inserted lines defined for data source DS\_1 with method SAPInsertLine are listed.
## 3.14.17.5  SAPDeleteDesignRule
With this API method, you can delete a rule that was applied with the Table Design API.
To call the method, use Application.Run and specify the following input parameters:
- Data Source Alias
Enter the formula alias for the data source. You can set the alias when configuring the data source on the Components tab in the design panel.
## · RuleID
Enter the rule ID of the rule to be deleted. If you do not specify a rule ID, all rules defined for that data source will be deleted.
The system returns one of the following output parameters for each execution:
## Example
```
Dim lResult As Boolean lResult= Application.Run("SAPDeleteDesignRule", "DS_1")
```
All rules defined for data source DS\_1 are deleted.
## 3.14.18  Using Analysis functions
You can use the Analysis functions in VBA macros. The return value of a formula in a macro corresponds to the return value of the formula used in a Microsoft Excel sheet. Depending on the function that you use, a formula can either return a single value (single string value) or a list (array). If the list contains only one line, the returning array is one-dimensional. If the list contains two or more lines the array is two-dimensional.
If you use a formula in a macro, the function is the first parameter followed by the arguments you use to create a formula with this function.
If a formula is invalid, Microsoft Excel returns an error.
## Example: Formula returning a single string value
Dim lResult As Variant
```
lResult = Application.Run("SAPGetVariable", "DS_2", "0BW_VAR", "Value")
```
This formula returns the current value of variable 0BW\_VAR.
## Example: Formula returning an array
```
Dim lResult As Variant lResult = Application.Run("SAPListOfDimensions", "DS_1")
```
This formula returns a list with the dimensions of data source DS\_1. If the data source contains only one dimension, the returned array is one-dimensional.
To make your programming easier, you can create an additional function to ensure that the array is always two-dimensional, for example the function GetAsTwoDimArray.
```
Function GetAsTwoDimArray(value As Variant) As Variant 'value is error return the error If IsError(value) Then GetAsTwoDimArray = value 'value is array ElseIf IsArray(value) Then 'first check if the array is two-dimensional 'by requesting the upper bound of the 2nd dimension. 'if this is not the case an error occurs (Err.Number <> 0).
```
```
'ignore errors, handled locally On Error Resume Next Dim lIndex As Integer Dim lErrorCode As Integer lIndex = UBound(value,2) lErrorCode = Err.Number 'set error handling back to default On Error GoTo 0 If lErrorCode = 0 Then 'no error: array is two-dimensional GetAsTwoDimArray = value Else 'copy one-dimensional array into a two-dimensional one Dim i As Integer Dim lArray() As Variant ReDim lArray(1 To 1, 1 To UBound(value)) For i = 1 To UBound(lArray, 2) lArray(1, i) = value(i) Next GetAsTwoDimArray = lArray End If Else 'return Empty GetAsTwoDimArray = Empty End If End Function
```
In the following example, a list of all dimensions is returned from function SAPListOfDimensions. All dimensions which are neither on the rows nor on the columns axis are added to a string that is displayed in the standard message dialog with the API method SAPAddMessage. Using the function GetAsTwoDimArray, you ensure that the returned array is always two-dimensional.
```
Public Sub ShowDimensionsNotOnRowsOrColumns() Dim lList As String Dim lResult As Variant lResult = Application.Run("SAPListOfDimensions", "DS_1") lResult = GetAsTwoDimArray(lResult) For i = 1 To UBound(lResult, 1) If lResult(i, 3) <> "ROWS" And lResult(i, 3) <> "COLUMNS" Then lList = lList & " " & lResult(i, 2) End If Next i Call Application.Run("SAPAddMessage", "Dimensions:" & lList, "INFORMATION") End Sub
```
## Related Information
Working with formulas [page 74]