OLAP Functions for Active Cells 

Use

OLAP functions are functions that you access from the cells in the filter and results areas of the query in the Business Explorer. You use either the context menu (secondary mouse click) or the OLAP Functions for Active Cells symbol from the BEx toolbar to call these functions.

Depending on the query characteristics and key figures (to which you can navigate) and the cell area you select, the system offers different functions.

Cell areas that do not belong to the query have only the MS Excel context menu.

Choose Settings ® OLAP Functions with Secondary Mouse Click to deactivate the OLAP functions in the Business Explorer. When you deactivate the OLAP functions, the functions in the MS Excel context menu are the only functions available in the cell areas of the query. The query is not detached.

See also:

Settings

Functions in Filter Cells

The selection of functions that are available in the context menu of the filter cells depends on the navigational status of the selected characteristic. The functions are:

·        Select Filter Value

·        Remove Filter Value

·        Drilldown ® Vertically

·        Drilldown ® Horizontally

·        Properties (See Query Properties)

Select Filter Value

Choose Select Filter Value to restrict a selected free characteristic to one or more characteristic values, or to restrict a structure to one or more parts of a structure (key figures). You choose one or more values from a list that is displayed containing all the characteristic values/key figures. After you refresh the query, only the data for the values you selected are displayed in the drilldown.

·         You are able to use the Select Filter Value function for every characteristic or navigation attribute in the filter area, irrespective of whether the characteristic or navigation attribute is included in the drilldown.

·         In contrast to the keep as filter value function, characteristics or navigation attributes that you want to select as filter values and that are already included in the drilldown are not removed from the drilldown when you execute this function.

See also:

Selecting Filter Values

Remove Filter Value

Choose Remove Filter Value if you want to see all the characteristic values for the characteristic again.

Drilldown

To display all the characteristic values for a characteristic along the rows axis or the columns axis of the results area, choose Drilldown ® Vertical or Drilldown ® Horizontal. If the characteristic has been filtered previously, only the filtered values are drilled down upon.

Functions for the Cells in the Results Area

The selection of functions available from the context menu of the cells in the query results area depends on the position and contents of the individual cell. The selection of functions that are available, depends on whether you have selected a cell that is related to a characteristic, a cell containing a characteristic value, for example, or whether you have selected a cell that is related to a structure element (key figure), a cell containing a key figure name, for example.

The following functions are explained under Navigation and Navigation Functions:

·        Back

·        Back to Initial View

·        Keep as Filter Value

·        Filter and Drilldown According to

·        Insert Drilldown According to

·        Swap ... with ...

·        Active Hierarchy

·        Expand hierarchy

Sorting

You use this function to sort the characteristic values or attributes for a drilldown characteristic (the rows and columns) according to various criteria (in ascending or descending order, for example). It is possible to sort according to the key, the text, a display attribute, or a displayed value column. (For information on activating display attributes, see Properties of the Query Components ® Characteristic Properties.)

Keys are usually sorted according to internal formatting. If you want to sort according to external formatting, you have to include this formatting with the InfoObject.

In hierarchy lists, the relationship between the upper-level/lower-level nodes is retained in the hierarchy structures. Lower-level nodes, however, are usually displayed in the sequence in which they appear in the sort criteria. You use the Sort According to Hierarchy function to restore the original sequence as determined in the hierarchy definition.

Sort According to Selection

If you have filtered the characteristic according to several single values, either in the query definition, or using a variable in the selection options, you use the Sort According to Selection function to sort the values accordingly. This also goes for selections in a dynamic filter (see Selecting Filter Values).

·        The system attempts to make meaningful suggestions for each value. It attempts first of all to sort according to a selection.

·        Time characteristics, characteristics without text, or characteristics that are displayed by default with either key or key and text, are sorted according to key.

·        Otherwise, the system sorts according to text. (SORT <itab>... AS TEXT..: Text fields are sorted automatically according to the language environment specified by the current user, that is, the language the current user is logged on to the system in. This means that the relative sequence of characters, one after the other, is culture-specific, and corresponds to the current text environment. The text environment is set automatically and corresponds to the logon language of the current user.

Calculate

You use this function to recalculate the results rows and single values that are displayed in the query according to particular criteria.

This function is found in the context menu of the filter cells in the results area, as well as in the query designer. In the query definition (local view or global definition), select the key figure you want to use, and choose Properties ® Calculations from the context menu (secondary mouse click).

For more information, see Calculating Results as... and Calculating Individual Values as....

Cumulated

You use this function to cumulate the individual cells in an area. The first value is added to the second value, the result is added to the third value, and so on. In the columns, the cells are cumulated from top to bottom, and in the rows, the cells are cumulated from left to right. With blocks of single values, that is, a drilldown in both the rows and the columns, the values are cumulated from top to bottom and from left to right.

This function is particularly useful with drilldowns on time characteristics (months, for example). Using the functions Single Value As ® Normalize to Results, and Sort ® Descending, on the corresponding value column, you get a cumulated list that is often described as an ABC list.

Also Apply to Results

It is also possible to apply the recalculation you have selected to the results rows.

Note: The following functions cannot be used for hierarchy lists, that is with an active presentation hierarchy, or have an effect on the characteristic with the hierarchy:

-         Calculate ® Result as

-         Calculate ® Single value as ® Ranked list / Ranked list (olympic)

-         Calculate ® Cumulated

(Characteristic) ® Suppress Hierarchy Nodes

If the characteristic has an active display hierarchy, you use this function to determine whether you want to suppress the hierarchy nodes never, always, or conditionally.

·        Never: The hierarchy is displayed along with all the hierarchy nodes.

·        Always: The overall results row is not displayed.

·        Conditionally: A hierarchy node is suppressed only if it has exactly one lower-level node. The lower-level node or hierarchy leaf is displayed instead of the suppressed hierarchy node, meaning it is given preference in the hierarchy level.

(Characteristic) ® Node Position

If the characteristic has an active display hierarchy, you position the hierarchy nodes either above or below. The lower-level nodes for a node are expanded either upwards or downwards.

(Characteristic) ® Cumulated Display

If you choose this function, the system sorts the list in ascending order according to key. All the structure elements (key figures) are subsequently aggregated to the n+1 characteristic value, one after the other, for each of the nth characteristic values. With time characteristics, the missing characteristic values are included wherever necessary.

If you subsequently choose a different sorting method for this function, the numbers become difficult to make sense of.

Do not use the cumulated display function with more than one characteristic.

It is a good idea to use the function Calculate ® Single value as ® Cumulated, since this function is more controllable, and more suitable in most cases.

This function allows you to display cumulated values for time-characteristics (cumulated sales for calendar year / month, for example).

Calendar Year 2000	Sales	Cumulated Sales
January	100	100
February	110	210 (January + February)
March	120	330 (January + February + March)

(Characteristic) ® Normalizing

You can normalize the query data from all key figures for various results for this key figure – the results are displayed as percentages.

Interim results are displayed as absolutes, not as percentages.

The normalization process refers to a particular characteristic each time, and has various independent settings for each characteristic.

Each key figure for a query has a query result. This is the result of the key figure that results from the aggregation of all characteristics for the query.

In each view, an overall result is displayed for the key figures. You can also normalize data for this result.

Finally, you also have the option of normalizing data for the results that are displayed in the results area.

The overall result and the query result are identical to one another, provided that none of the characteristics are filtered.

Goto

The Goto function in the context menu enables you to jump to documents and targets using the report-report interface.

Displaying Documents/ Documents for Navigational State

You can display documents on the Web for different objects that were created in the Administrator Workbench or in the Web application, and then create new documents. Users require the relevant authorization in order to display or edit BW objects. For more information, see Authorizations for Documents.

For more information, see Goto.

Please note that this function in the BEx Analyzer is supported by versions of MS Excel 2000 and higher.

Exceptions / Conditions

You can activate / deactivate exceptions or conditions for a query.

Currency Translation

You can translate according to a target currency or a database currency.

See also:

Translating Currencies

All Characteristics

This is where you make the following settings for all characteristics:

Display as

Characteristic values are displayed using different display formats. The display formats offered depend on the InfoObject properties that are set on the database.

You can choose from the following options:

·        No Display: The characteristic display is hidden.

·        Key and Text: The characteristic values are shown by their technical key and by their text.

·        Text: The characteristic values are shown by their text.

·        Key: The characteristic values are shown by their technical key.

·        Text and Key: The characteristic values are shown by their text, followed by their technical key.

For these display options, the key in external format and the shortest available text are used respectively.

Using the Further Settings context menu entry, you can select further display types in which you can specify the text type more precisely. You can select between short text, medium text and long text. The system always allows you to select every text that was created in InfoObject maintenance for the characteristic. See Tab Page: Master Data/Texts.

The various display options are also available for use with individual characteristics, and with attributes. Highlight the characteristic or attribute that you want to use, and choose Characteristic / attribute ® Display As from the context menu (secondary mouse click).

(For information on activating display attributes, see Properties of the Query Components ® Characteristic Properties.)

Results Rows Position

You choose whether you want the results rows to be displayed on the top/left or bottom/right of the screen.

If the characteristic has an active presentation hierarchy, then this function cannot be used to position the results rows.

The results row for a presentation hierarchy can, on the one hand, can be viewed as the results row for the associated drilldown characteristic. On the other  hand, in this case, the results row is also the top node of the presentation hierarchy. For this reason, the position conforms to the settings of the node position in the presentation hierarchy. Thus, if you want to change the position of the results row, then choose the function (Characteristic)  ® Node Position on the presentation hierarchy.

Suppress Results Rows

You determine whether the results rows are never, always or conditionally suppressed. Conditional suppression of the results rows means that the results rows are not displayed if the corresponding area contains only one characteristic value.

This function is also available for use with individual characteristics. Highlight the characteristic that you want to use, and choose (Characteristic) ® Suppress Results Rows from the context menu (secondary mouse click).

Suppress Zero Rows/Columns

You can use this setting to determine whether columns or rows containing zeros are to be displayed.

If you select Suppress Zero Rows/Columns, the rows or columns with a result of zero are not displayed. The row or column contains, for example, the values 1, -1, 1, -1 and the result is 0. Activating this setting results in the whole row or column not being displayed.

If you do not select Suppress Zero Rows/Columns, the rows or columns with zero values are displayed (=default setting).

Normalization

You are able to choose not to use normalization, or to normalize the characteristics using:

·        Query Result

·        Overall Result

·        Result

Undo

You can cancel the settings individually  for the characteristic display, for the results rows, and for normalizing single characteristics. Alternatively, you can undo all the changes made to settings for all characteristics at once.

Properties

Under Properties, you make settings affecting the display, interaction, and column width. You can find the technical information for the query or the workbook under Information.

See also:

Query Properties

Activities

Select any cell in the query and call the context menu, or choose OLAP Functions for Active Cells from the BEx toolbar.

 

See also:

 

Priority Rule with Formatting Settings