SAPSetFormat

With this API method, you can apply defined cell styles as format to single cells and cell ranges. You can use and selelct standard Microsoft Excel cell styles, SAP cell styles and user-defined cell styles.

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, e.g. 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.
Style
Enter the style that should be applied.
First Line Type and attributes
With the first and second line type and their atttributes, you can define the anchor for the area in the crosstab the selected format should be applied to. The operation anchor, or operation context, is defined as a crossing point of two lines. Each line is defined through two parameters: line type and line context (attributes). Depending on line type different context information is expected.
You can enter one of the following constant types and their relevant attributes:
- DIMENSION
  
  To define a dimension, enter the constant DIMENSION and the technical name of the dimension as attribute.
- MEMBER
  
  To define a member, enter the constant MEMBER and the technical names of the dimension and the member as attributes.
- RESULTMEMBER
  
  To define a result member, enter the constant RESULTMEMBER and the technical name of the dimension as attribute.
- HIERARCHYNODE
  
  To define a hierarchy node, enter the constant HIERARCHYNODE and the technical names of the dimension, the member and the node as attributes.
- VIRTUALHEADER
  
  To define a new inserted header line, enter VIRTUALHEADER and the rule ID of the inserted line.
- VIRTUALDIMENSION
  
  To define a new inserted dimension line, enter VIRTUALDIMENSION and the rule ID of the inserted line.
- VIRTUALMEMBER
  
  To define a member of a new inserted dimension line, enter VIRTUALMEMBER and the rule ID of the inserted line.
- TUPLE
  
  The constant TUPLE can be used to define a combination of members of various dimensions on an axis.
  
  To define a TUPLE, enter one of the following member types with the corresponding attributes:
  - MEMBER with the technical names of the dimension and the member as attributes.
  - 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 attributes.
Second Line Type and attributes
You can enter the same constants and attributes as for the first line type. If you enter a second line type, you can specify the anchor more precisely.
Scope Area
This parameter is optional. You can enter one of the following values:
- CONTEXT
  
  The parameter CONTEXT is the anchor defined with first and second line type. Context is the default value for the scope area parameter.
- FIRST
  
  The parameter FIRST defines the area on the left or top from the anchor as scope area.
- SECOND
  
  The parameter SECOND defines the area on the right or bottom from the anchor as scope area.
- BOTH
  
  The parameterBOTH defines the areas of the paramters FIRST and SECOND as scope area.
Scope Axis
This parameter is optional. You can enter one of the following values:
- PRIMARY
  
  PRIMARY applies style to the primary axis depending on the context.
- SECONDARY
  
  SECONDARY applies style to the secondary axis depending on the context.
- ROW
  
  ROW applies the style to the specified area on the row axis.
- COLUMN
  
  COLUMN applies style to the specified context on the column axis.

Example

Apply a style to a member

Dim lResult As String

lResult= Application.Run("SAPSetFormat", "Style1", "DS_1", "Green", "MEMBER", "0CALYEAR;2014")

The style Green is applied to member 2014 of the dimension 0CALYEAR. If the member 2014 appears several times in the crosstab, the style is applied to each occurrence. The rule ID for the applied style is Style1.

Example

Apply a style to the data cells of a member for one measure

Dim lResult As String

lResult= Application.Run("SAPSetFormat", "Style2", "DS_1", "Green", "TUPLE", "MEMBER;0CALYEAR;2014", "TUPLE", "MEMBER;Measure;Sales")

The style Green is applied to the data cell for measure Sales of member 2014 of the dimension 0CALYEAR. If the member 2014 appears several times in the crosstab with data cells for measures Sales, the style is applied to each corresponding data cell. The rule ID for the applied style is Style2.

Example

Apply a style to a member and to the data cells of the member for several measures

Dim lResult As String

lResult= Application.Run("SAPSetFormat", "Style3", "DS_1", "Green", "TUPLE", "MEMBER;0CALYEAR;2014", "TUPLE", "MEMBER;Measure;Sales","Both")

The style Green is applied to member 2014 of the dimension 0CALYEAR and to the corresponding data cell of measure Sales. If there are data cells for other measures in the same row, the style is also applied to these cells. If the member 2014 appears several times in the crosstab, the style is applied to each occurrence and the corresponding data cells. The rule ID for the applied style is Style3.