Skip to main content

SAP BW

This section provides information on how to connect SAP BW queries or CDS views. Before you can create a Function, make sure, that you have already created a respective System within a System Line.

Connect to SAP BW queries or CDS views​

In order to connect to SAP BW, create a new Function in the menu 'Functions, Models and Code-Files' and directly assign it to a Category.
Next, select the relevant Technical Handler 'BW'.
All other configuration (e. g. a descriptive Short Name and Description) has to be made as usual.

For the Technical Parameters, please specify the following options. The options in BOLD are REQUIRED:

  • INFOPROVIDER: Technical name of the InfoProvider.
  • KEYFIGURE_CAPTION: Optional caption to replace the default key figure value column name.
  • QUERY: Technical name of the Query.
warning

The value of KEYFIGURE_CAPTION must not match any existing characteristic name in the query.

Parameters​

You can specify Parameters for querying SAP BW. These Parameters can be divided in Parameters that manage how the query is displayed and Parameters that manage the rows to be retrieved.

note

You need the technical names of the respective characteristics or the Enterprise IDs of the structures for both the Parameter definition and the Parameter values. You can find these technical names in various ways:

  • Analysis for Office and Properties of characteristics
  • Transaction rsa1
  • Query Designer or BW Modelling Tools in Eclipse

Parameters to change the query display​

  • EXPAND_ROW: Define characteristics or structures to be added or removed from the drilldown in a query. Technical name(s) of the characteristic(s) or Enterprise ID(s) of the structure(s) to be entered.
    • Cardinality: List
    • Data Type: String
note

Removing characteristic(s) is needed when you are not interested in the default displayed characteristic(s).

  • SET_PRESENTATION: Define the presentation type for a characteristic. Requires the definition of the characteristic.

    • Cardinality: Single Value
    • Data Type: String
    • Additional Properties:
      • IOBJECT: The technical name of the characteristic to which the presentation type is applied.
    • Possible values:
    ValueDescription
    NONEnot displayed
    KEYKey only
    TEXTText only
    TEXT_MDMedium length text
    TEXT_LGLong text
    KEY_TEXTKey and text
    TEXT_KEYFirst text and then key
    KEY_TEXT_MDKey and medium length text
    KEY_TEXT_LGKey and long text
note

Defining the presentation of a characteristic only makes sense if the characteristic is contained in the drilldown of a query either by default or via the EXPAND_ROW Parameter.

warning

Not all possible values must be available for a characteristic. Therefore, if you try to select a value that is not available, an error is thrown.

  • SET_ATTRIBUTE: Define the attribute(s) to be shown for a characteristic. Requires the definition of the characteristic and the presentation type. Technical name(s) of the attribute(s) to be entered.
    • Cardinality: List
    • Data Type: String
    • Additional Properties:
      • IOBJECT: The technical name of the characteristic to which the attribute selection is applied.
      • ACTIVE: Activate or deactivate the selection of the attribute(s). Possible values are YES or NO, default YES selected.
      • PRESENTATION: Define the presentation type for the attribute(s). Possible values are KEY, TEXT, TEXT_MD, TEXT_LG, KEY_TEXT or TEXT_KEY, the definition can be seen in SET_PRESENTATION.
warning

Defining the attribute(s) to be shown for a characteristic requires the characteristic to be in the drilldown of the query, either by default or via the EXPAND_ROW Parameter.

  • SET_HIERARCHY: Define the assignment of a display hierarchy for a characteristic. Requires the definition of the characteristic. Technical name of the desired display hierarchy to be entered.
    • Cardinality: Single Value
    • Data Type: String
    • Additional Properties:
      • IOBJECT: The technical name of the characteristic to which the display hierarchy is assigned.
      • VERSION: The version of the hierarchy (optional).
      • PRESENTATION: The key date for the hierarchy (optional).
note

To change a default display hierarchy to flat presentation that only shows leaves, you must use the technical name of the characteristic as the value input.

warning

There are restrictions if you assign a display hierarchy for a characteristic and at the same time try to filter the same characteristic with the FILTER_HIER_NODE Parameter. Please see FILTER_HIER_NODE for more information.

  • SET_LEVEL: Define a hierarchy level the display hierarchy of a characteristic is expanded to (1 is the root level). Requires the definition of the characteristic and an respective activated display hierarchy.
    • Cardinality: Single Value
    • Data Type: Integer
    • Additional Properties:
      • IOBJECT: The technical name of the characteristic to which the hierarchy level is applied. A display hierarchy needs to be assigned to this characteristic.
note

The corresponding characteristic must be included in the drilldown either by default or via EXPAND_ROW and a display hierarchy must be assigned to this characteristic either by default or via SET_HIERARCHY.

  • LANGUAGE: Language parameter. If not set, the user's default language will be used.
    • Cardinality: Single Value
    • Data Type: Integer
    • Possible values: e. g. EN
warning

As the default language of the users in the source system can be different, possible subsequent problems can occur in the Model. It is therefore recommended to use a static filter for this Parameter and thus filter the LANGUAGE Parameter by default in the Parameter configuration. You can even deactivate the 'Exposed to user' property so that the default value cannot be overwritten by a user.

  • SET_ZERO_SUPPRESSION: Define the type of zero suppression in the query.

    • Cardinality: Single Value
    • Data Type: String
    • Additional Properties:
      • STRUCTURE: The first structure to be included in the zero suppression (optional). Enterprise ID of the structure to be entered.
      • STRUCTURE2: The second structure to be included in the zero suppression (optional). Enterprise ID of the structure to be entered.
    • Possible values:
    ValueDescription
    NONEdo not hide any zeros
    COLSonly hide columns which have zeros in the cross-classified table
    OVERALL_COLonly hide overall results columns which contain zeros
    ROWSonly hide rows which have zeros in the cross-classified table
    CROSSonly hide rows and columns which have zeros in the cross-classified table
    OVERALL_ROWonly hide overall results rows which contain zeros
    ALLhide overall results columns and rows which contain zero values
note

Zero suppression can be defined by default in the query. You can overwrite the default setting with the value NONE or any other listed value.

  • SET_COMPACTVIEW_ON_AXIS: Deactivate or activate the compact view in a query. Requires the definition of the axis.

    • Cardinality: Single Value
    • Data Type: String
    • Additional Properties:
      • AXIS: The axis to activate or deactivate the compact view. Possible values are ROWS or COLUMNS.
    • Possible values:
    ValueDescription
    YEScompact view activated
    NOcompact view deactivated
note

It is not recommended to use this Parameter to activate the compact view in a query. Instead, the Parameter should be used to deactivate the compact view in a query if it is activated by default.

Parameters to filter rows​

  • VARIABLE_VALUE: Restricting the query by filtering a variable (for hierarchy nodes use VARIABLE_HIER_NODE). Requires the definition of the variable. Value key to be entered.
    • Cardinality: all possible, depends on definition of the variable in the query.
    • Data Type: all possible, depends on definition of the variable in the query.
    • Additional Properties:
      • VARIABLE: The technical name of the variable.
note

The configuration of the Parameter has to match the definition of a variable in the BW query. If, for example, a variable is only defined as a single value, you must also select the corresponding cardinality Single Value when configuring the Parameter.

  • VARIABLE_HIER_NODE:Restricting the query by filtering nodes of a hierarchy node variable. Requires the definition of the variable and the node characteristic. Value key to be entered.
    • Cardinality: List
    • Data Type: all possible, depends on definition of the variable in the query.
    • Additional Properties:
      • VARIABLE: The technical name of the variable.
      • IOBJECT: The technical name of the node characteristic. Can be either a characteristic node or a text node (0HIER_NODE).
note

The hierarchy to be restricted with this Parameter cannot be changed with the SET_HIERARCHY Parameter. To change the selected hierarchy, a hierarchy variable must be defined in the query and a corresponding VARIABLE_VALUE Parameter must be configured.

note

If the unassigned node is to be selected with REST_H, we change IOBJECT to 1HIER_REST.

note

To restrict both nodes and leaves, a Parameter must also be created for both variable options. Use VARIABLE_HIER_NODE for the restriction of nodes and VARIABLE_VALUE for leaves.

  • FILTER_VALUE: Restricting the query by filtering a characteristic (for hierarchy nodes use FILTER_HIER_NODE). Requires the definition of the characteristic and the value format (internal or external). Value key to be entered.

    • Cardinality: all possible.
    • Data Type: all possible, depends on definition of the characteristic in the query.
    • Additional Properties:
      • IOBJECT: The technical name of the characteristic or Enterprise ID of the structure.
      • PRESENTATION: Internal (INT) or external presentation (EXT). Internal is required for structures and should be used for dates.

  • FILTER_HIER_NODE: Restricting the query by filtering nodes of a characteristic (cannot be combined with SET_HIERARCHY). Requires the definition of the characteristic, the node characteristic and the value format (internal or external). Value key to be entered.

    • Cardinality: List
    • Data Type: all possible, depends on definition of the characteristic in the query.
    • Additional Properties:
      • IOBJECT: The technical name of the characteristic (structures not valid here).
      • NODE_IOBJNM: In case of text nodes, enter '0HIER_NODE'. Leave empty in case of characteristic nodes.
      • PRESENTATION: Internal (INT) or external presentation (EXT). Internal should be used for dates.
note

This Parameter can not be used for structures. Do not confuse display hierarchies with structure groupings. In this context, it is important that all nodes of structure groupings in the query definition are defined as expanded and not collapsed. If a structure node is collapsed, it is not possible to filter this node and the leaves below it in parallel using FILTER_VALUE.

note

If the unassigned node is to be selected with REST_H, we change NODE_IOBJECT to 1HIER_REST.

note

To restrict both nodes and leaves, a Parameter must also be created for both filter options. Use FILTER_HIER_NODE for filtering nodes and FILTER_VALUE for leaves.

warning

In general, due to the limitations of combining SET_HIERARCHY with FILTER_HIER_NODE, it is recommended to define a hierarchy variable and a corresponding hierarchy node variable in the query. With this approach, users can change assigned hierarchies with a VARIABLE_VALUE Parameter and filter on the corresponding nodes with a VARIABLE_HIER_NODE Parameter.