Selection/Formula/Cell Properties 

Use

In the Selection/Formula/Cell Properties dialog box, you can set a range of functions for a structural component.. These settings are applied to the structural component, irrespective of whether it is in the rows or the columns.

If you have defined exception cells for selection cells, formula cells or cell references, then you can perform the relevant settings in this dialog box. Help cells do not have any properties that you modify.

This section describes the properties of selections and formulas. Once you save a selection or a formula as a reusable object, you have a restricted key figure (selection) or a calculated key figure (formula) that you can use in other queries. The settings you can make for the properties of these objects are almost identical. Any settings that are only possible for certain objects are indicated as such.

Features

The Selection/Formula/Cell Properties dialog box is divided into tab pages, each of which offers different settings.

Tab Page: General

Description

The name of the key figure, the restricted key figure (selection), formula or cell is displayed here automatically. You can also change the description here.

Unlike characteristics, attributes and cells, the description for structural components can contain multiple lines.

The total number of all characters in all lines, including the end of line character, is restricted to 60 characters.

You can use text variables in the description of structure components.

Use Standard Text: If you make this setting, the default value will always be used. The default values are then as follows:

●      Formula: Formula <number>

●      Selection:

Selection <number> for an empty selection

<Description of key figure> for a selection with a key figure

<Description of first restricted characteristic value> for a selection with characteristic values

●      Calculated key figure: <Technical name>

●      Restricted key figure: <Technical name>

●      Cell: <Description of Row Structure Component> / <Description of Column Structure Component>

Technical Name

You can enter a technical name for the structural component. This is optional.

This function is only available for structure components and restricted and calculated key figures.

The technical name must be unique for all queries.

For access from external interfaces (such as Crystal Reports, from URLs, or using OLE DB for OLAP), a technical name is necessary for structural components, since structural components are handled as table fields and have to be uniquely accessible. If you do not enter a technical name, the unique ID (UID) is used to uniquely identify the structural component when accessing from external interfaces. As the UID has 25 characters, however, problems can arise when modifying queries.

You can also change the technical name. If you do this, a warning is displayed stating that the references might be corrupted if the structural component is used in external interfaces.

The system generates the technical name for reusable objects, but you can change this name before saving the object. Once you have saved the object, you cannot change the technical name again. The generation rule is (RKF/CKF)_<date><time>, RKF_20050818122045 for example. As long as you have not saved the object, and the generated technical name has not been changed, a warning appears prompting you to change the technical name.

Last Changed by

This function is only available for restricted and calculated key figures.

Here, you can find out the owner and the person who last changed the calculated or restricted key figure, as well as the date and time when the key figure was last changed. This information can be useful, as calculated and restricted key figures are reusable objects.

Tab Page: Aggregation

Aggregation

This function is only available for formulas, formula cells and calculated key figures.

Here, you can make settings for aggregation and calculation time (that is, for the detail level of the calculated key figure or formula). In the standard setting, the data is first aggregated to the display level; and then the formula is calculated (= standard aggregation). The exception aggregation setting allows the formula to be calculated before aggregation using a reference characteristic and then to be aggregated with the exception aggregation.

You can select the following settings in the Exception Aggregation field.

●      Use Standard Aggregation: You use this setting to specify that aggregation takes place before calculating the formula. You therefore do not use exception aggregation.

●      Total (SUM)

●      Maximum (MAX)

●      Minimum (MIN)

●      Exception If More Than One Record Occurs (NO1)

●      Exception If More Than One Value Occurs (NO2)

●      Exception If More Than One Value <> 0 Occurs (NOP)

●      Average of All Values (AVG)

●      Average of All Values < > 0 (AV0)

●      Average Weighted with Calendar Days (AV1)

●      Average Weighted with Working Days (AV2)

You can define the ID of the factory calendar in Customizing. For more information see the SAP Reference IMG  → SAP Customizing Implementation Guide → SAP NetWeaver → Business Intelligence → Settings for Reporting and Analysis → General Settings for Reporting and Analysis → Set F4 Help and Hierarchies for Time Characteristics/OLAP Settings.

●      Count All Values (CNT)

●      Count All Values <> 0 (CN0)

●      First Value (FIR)

●      Last Value (LAS)

●      Standard Deviation (STD)

●      Variance (VAR)

●      No Aggregation Along the Hierarchy (NHA)

●      No Aggregation of Posted Nodes Along the Hierarchy (NGA)

If you use exception aggregation, you need to select a characteristic from the Reference Characteristic field. The system will use this to calculate the formula before aggregation. In the Reference Characteristic field, you can select from all characteristics in the InfoProvider.

For more information about the settings for exception aggregation, see the documentation for InfoObject maintenance under Tab Page: Aggregation.

Calculate After Aggregation: This field is only displayed for calculated key figures. It is not displayed for formulas. It is used for display and stipulates that the formula of the calculated key figure is calculated after aggregation. If you use calculated key figures that you defined in SAP BW 3.5, you can use this field to specify whether the formula is calculated before or after aggregation.

Calculating before aggregation results in poor performance, as the database reads the data at the most detailed level, and the formula is calculated for every single record. In formula calculations, the individual record information is often needed for just one or two specific characteristics. The remaining data from the InfoProvider can be aggregated.

We recommend that you define the calculation level using exception aggregation with the corresponding reference characteristic. If you need multiple exception aggregations or reference characteristics, you can nest formulas and calculated key figures in one another and specify an exception aggregation for each formula or calculated key figure.

More information:

Examples of Exception Aggregation Last Value (LAS) and Average (AV0)

Example of Exception Aggregation Average (AV1)

Example of Exception Aggregation: Counting

Example of Exception Aggregation: Enhanced Counting

Tab Page: Display

Hide

You can make the following settings to show or hide the selection, formula or cell:

●      If you choose Always Show, the selection or formula is always visible.

●      If you choose Hide (Can Be Shown), the selection or formula is hidden in the executed report, but can be shown again there.

You can use this setting to ensure that only information that is really necessary is displayed in the start view for the report. You can also show additional details if required.

You can show the selection or formula in the executed report in the following ways:

○       in Web applications, under Selecting Filter Values

○       in BEx Analyzer, under Selecting Filter Values

The option Hide (can be shown) is only available for structure components.

●      If you choose Always Hide, the selection or formula is not displayed.

You can use this setting for help columns that are not visible in the executed report and that cannot be shown.

Use Default Settings: You can specify that the default values are always used. The default value is then the setting for the key figure used. If no key figure is found or if the key figure does not return this type of setting, Always Show is used as the default value.

Highlight

You can choose whether you want to highlight the selection or formula. The type of highlighting depends on the style used in BEx Analyzer or in the Web application.

Use Default Settings: You can specify that the default values are always used. The default value is then the setting for the key figure used. If no key figure is found or if the key figure does not return this type of setting, Normal Display is used as the default value.

Number of Decimal Places

You can specify the number of decimal places. Numbers can have any number of decimal places from zero up to nine decimal places (0, 0.0, 0.00... to 0.000000000).

Use Default Settings: You can specify that the default values are always used. In this case, the default value is the setting from the key figure specified in InfoObject maintenance. If no key figure is found, Query Designer cannot determine a default value. This is indicated by Standard Value Not Found.

Scaling Factor

You can specify a scaling factor between one and one billion. If you set 1000, for example, 3000 is displayed in the report as 3.

Use Default Settings: You can specify that the default values are always used. In this case, the default value is the setting from the key figure specified in InfoObject maintenance. If no key figure is found, Query Designer cannot determine a default value. This is indicated by Standard Value Not Found.

Sign Change

Select whether +/- signs should be kept or reversed. When a sign is reversed, a positive number takes a minus sign (85 becomes -85 for example) and a negative number takes a plus sign (-38 becomes +38 for example).

The reversal of plus and minus signs is purely a display function. If the key figure is added to a formula, for example, it is calculated with its correct values.

Use Default Settings: You can specify that the default values are always used. The default value is then the setting for the key figure used. If no key figure is found or if the key figure does not return this type of setting, Keep +/- Sign is used as the default value.

More Information:

Priority Rule with Formatting Settings

Status of Node

This function is only available for structure components.

For a hierarchical arrangement of structure elements, you can define whether a node is expanded or collapsed in the structure. This property is applied to the executed query and is independent of the status of the node in Query Designer. The node can therefore be collapsed during design, for example, but then be expanded when the query is executed if this is specified in this property.

Tab Page: Advanced

UID

This field displays the unique ID (UID) that is used for unique identification. This field is only used for information purposes. For example, you can use the UID in URLs of the Web API to address this element. However, we recommend that you use technical names, because UIDs can change when an object is deleted and then re-created.

Constant selection

This function is only available for selections, selection cells and restricted key figures.

You can select a selection as constant. This means that the selection cannot be changed by navigation or filtering at runtime and can therefore be used as a reference size. More information: Constant Selection

Formula Collision

This function is only available for formulas, formula cells and calculated key figures.

Whenever you define two structures that both contain formulas, it is unclear to the system how to calculate the formulas at the point where both formulas intersect.

The following example illustrates the concept of formula collision:

	Column 1	Column 2	Column 1 x Column 2
Row 1	Value A	Value B	A x B
Row 2	Value C	Value D	C x D
Row 1 + Row 2	A + C	B + D	? Formula Collision?

In this example, there are two rows and two columns with simple values. The third row is a simple summation formula, and the third column is a simple multiplication. In the cell where the row and column formulas meet, it is not clear how the data is to be calculated.

If you calculate according to the column formula in this cell, the cell contains (A+C)x(B+D). If you calculate according to the row formula in this cell, the cell contains (AxB)+(CxD). The result achieved therefore depends on the type of calculation performed.

If a formula collision occurs, as described in the example above, you can specify which formula to use in the calculation. You can make the following settings in the Eliminate Formula Collision field:

●      Nothing defined

If you do not specify a value, the formula that was last specified and saved takes priority in a formula collision.

●      Use Result of This Formula

The result of this formula has priority in a collision.

●      Use Result of Competing Formula

The result of a competing formula has priority in a collision.

Collisions always occur when multiplication/division and addition/subtraction are mixed in competing formulas. If both formulas involve multiplication/division or addition/subtraction only, both calculation directions return the same result, meaning that no settings are required for the formula collision.

Tab Page: Conversions

Currency Translation

This function is only available for selections, selection cells and restricted and calculated key figures.

You create translation types for currency translation under SAP Menu → Modeling → Object Maintenance → Currency Translation Types. More information: Creating Currency Translation Types.

In Query Designer, you can set a target currency for a structural component or for a cell.

Select a conversion type in the Conversion Type dropdown box. Depending on how the currency conversion type has been created, you have the following options:

●      Select the Target Currency When Converting

The target currency was not fixed in the conversion type, but can be determined during conversion. Select the required conversion type and enter the required currency in the Target Currency dropdown box. You can also select a currency from the dropdown box.

You can also choose the  icon to call the input help dialog box and select a variable target currency. By choosing the black arrow, you can navigate to the context menu and can then choose New Variable to create new variables or Change Variable… to change existing variables. Both options use the Variables Editor. When you execute the query, the Variables screen appears. You can then choose the target currency.

●      Fixed Target Currency

The target currency was determined in the conversion type. Select the required conversion type with the fixed target currency. In the Target Currency dropdown box, the fixed target currency for this conversion type is displayed.

●      InfoObject Determines Target Currency

The target currency is specified in the conversion type in such a way that it is determined from an InfoObject. Select the required conversion type with the target currency from the InfoObject. When you select this conversion type, From InfoObject is displayed in the Target Currencyfield.

●      Target Currency from Variable.

In the conversion type, a variable was specified for 0CURRENCY. When you select this conversion type, From Variable is displayed in the Target Currencyfield.

You can find more information about currency translations under Currency Translation in the Business Explorer.

Unit conversion

This function is only available for selections, selection cells and restricted key figures.

You create the conversion types for unit conversion under SAP Menu → Modeling → Object Maintenance → Unit Conversion Types. More information: Creating Quantity Conversion Types.

In Query Designer, you can set a target unit of measure for a structural component or for a cell.

Select a conversion type in the Conversion Type dropdown box. Depending on how the unit conversion type was created, you have the following options:

●      Select the Target Unit of Measure When Converting

The target unit of measure is not fixed in the conversion type, but can be determined during conversion. Select the required conversion type and enter the required unit in the Target Unit dropdown box.

You can also choose the  icon to call the input help dialog box and select a variable target unit of measure. By choosing the black arrow, you can navigate to the context menu and can then choose New Variable to create new variables or Change Variable… to change existing variables. Both options use the Variables Editor. When you execute the query, the Variables screen appears, where you can choose the target unit of measure.

●      Fixed Target Unit of Measure

The target unit of measure was specified in the conversion type. Select the required conversion type with the fixed target unit of measure. The fixed target unit of measure (such as Gram) for this conversion type appears in the Target Unit dropdown box.

●      InfoObject Determines Target Unit of Measure

The target unit of measure is specified in the conversion type so that it is determined from an InfoObject. Select the required conversion type with the target unit of measure from the InfoObject. The text From InfoObject appears in the Target Unit field when you select this conversion type.

●      Target Unit of Measure from Variable

A variable was specified for 0UNIT in the conversion type. The text From Variable appears in the Target Unit field when you select this conversion type.

More information: Quantity Conversion.

Tab Page: Planning

The functions described below from the Planning tab are only offered for selections, selection cells and restricted key figures.

On this tab page, you can specify the various settings for BI Integrated Planning.

Change Data

You can specify whether and how the data can be changed. You can make the following settings:

●      Not input ready (not relevant for locking)

The selections are not locked for the exclusive access of a user because many users use this data as a reference (for actual data, for example).

This is the default setting.

●      Not input ready (relevant for locking)

If you want to protect selections against manual entries, but allow changes by planning functions, you can use locks to protect this data for one particular user. This allows you to ensure that the planning function works with the displayed data only and not with data that has been changed by other users.

●      Input ready (relevant for locking)

The data is locked for a user and is input-ready for manual planning.

These default settings can be overridden by the system state, for example if no disaggregation takes place (see below).

The change setting made here for the data of a selection can be overridden when the query is started with the Start Query in Display Mode setting (see Query Properties → Tab Page: Planning). If you set the selection in such a way the data is input-ready and relevant for locking, but the Start Query in Display Mode setting is activated in the query properties, the query will not be input-ready when it is started. The user can activate input-readiness at runtime, and the settings specified here take effect for the selection.

More information: Input-Ready Query

Disaggregation

In input-ready queries, only values on the detail level for the aggregation level can normally be changed. All aggregated values, such as in results rows or inner hierarchy nodes, are not input ready.

To be able to change an aggregated value, the value must be disaggregated in all data records that are related to this aggregated value. This disaggregation is called top-down distribution.

You can specify the top-down distribution for input-ready structural components. You can make the following settings:

●      No Disaggregation

The values for the structural component are not disaggregated. Values aggregated at the aggregation level cannot be changed.

●      Disaggregate Value Entered

You can change values aggregated at the aggregation level. The newly entered value is distributed to all data records that contribute to the changed cell. The type of distribution depends on your specifications, as described below.

●      Disaggregate Difference to Value Entered

You can change values aggregated at the aggregation level. The difference between the old and the newly entered value is distributed to all data records that contribute to the changed value. The type of distribution depends on your specifications, as described below.

Disaggregation on basic key figures is only possible with aggregation type SUM. Top-down distribution is not available for key figures, key figures with exception aggregation, or local calculations.

Type of Distribution

You can specify the type of top-down distribution here. You can make the following settings:

●      Equal Distribution

The distribution is made evenly across all data records that contribute to the changed value (including data records with NULL values).

●      Analog Distribution (Self-Reference)

Distribution is analogous to all data records that result in the changed value.

●      Analog Distribution (With Reference to Following Structure Element)

You can define a structure element as a reference. Distribution is analogous to all data records that contribute to the corresponding value of this referenced structure element.

You can only select a structural component from the same structure as the reference for analog distribution.

More information: Disaggregation (Top-Down Distribution).

Tab Page: Planning

The functions described below from the Planning tab are only offered for formulas and formula cells.

You can make settings for planning on formulas here.

Planning on formulas requires definition of inverse formulas. Inverse formulas are formulas that allow recalculation of the entered value to the formula’s operands. An inverse formula is required for every input-ready operand of a formula.

Change Data

●      NOT INPUT READY!

The formula value cannot be changed by the user.

●      Input-Ready

The formula value can be changed by the user. Once the formula is flagged as input-ready, the system automatically creates the inverse formulas for this formula.

These default settings can be overwritten by the system status. If all incoming formula operands are non-input ready, for example, the formula value will also be non-input ready.

The change setting made here for the data of a formula can be overridden when the query is started with the Start Query in Display Mode setting (see Query Properties → Tab Page: Planning). If you set the formula in such a way the data is input-ready, but the Start Query in Display Mode setting is activated in the query properties, the query will not be input-ready when it is started. The user can activate input-readiness at runtime, and the settings specified here take effect for the formula.

Formula Group

All inverse formulas of a formula are known as formula groups. All  inverse formulas of a formula group are listed under Formula Group. You can change the order of the inverse formulas using the arrow keys. The system then uses this order to find out which inverse formulas for the recalculation.

For formulas and calculated key figures (but not for formula cells), the inverse formulas are also displayed in the structure as hierarchically subordinate formulas. You can sort them by dragging and dropping them. The inverse formulas are flagged in the structure with a symbol.

More information: Defining Inverse Formulas.

Tab Page: Calculations

Local Calculations

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

Calculate Results As

Calculate Single Values As

Also Apply to Results

You can also use the selected recalculation on the results rows under Calculate Results As… or Calculate Single Values As...

 

In BEx Analyzer, the functions under Calculate →Results As cannot be used for hierarchy lists, with an active display hierarchy that is, and have no effect on the characteristic with the hierarchy. Function Calculate →Results As → Hide can be used, however.

This restriction does not apply to Web applications.

Cumulated

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

You can only cumulate with characteristic values and not with the structural elements of a characteristic structure. The Cumulated function only takes effect if you have a characteristic instead of a characteristic structure in the drilldown. However, if you use two structures – the key figure structure and a characteristic structure – you can cumulate using cell-specific definitions. More information: Defining Exception Cells.

Calculation Direction

The default calculation direction is not always the expected one. You can change the calculation direction as required:

●      Use the default direction (from top to bottom and from left to right)

●      Calculate along the rows (from top to bottom)

●      Calculate along the columns (from left to right)

The Calculate Along the Columns setting is useful, for example, for all queries for which a time dimension is drilled down in the columns, and that generate a cumulated revenue. This is shown in the following example query:

Use Precalculated Value: You can specify that the default values is used for all settings for calculations.. If you make this setting, the default value is derived from either a calculated key figure or a restricted key figure from the selection. You therefore have two choices: Either you make your own settings for calculation on the selection or you use the Use Precalculated Value function to apply the settings from the calculated or restricted key figure. If the selection contains a key figure, the User Precalculated Value function is greyed out, as there is no default value in this case. More information: Properties and How They Are Derived.

Tab Page: Content

You can make the structure component switchable in the context of the Switch framework. To do so, enter a name in the Switch field. The structure component is thus assigned a switch in the Switch framework. For more information, see Switchable Query Components in the Framework.

Activities

Select the structure component or cell. In the Properties screen area, the properties dialog for the structural component or cell appears. You can also select multiple structural components/cells of the same type and set their properties simultaneously. If you enter an invalid value, a warning is displayed beside the property and a corresponding text that helps you to resolve the error is displayed in the Messages screen area. To save your settings, choose Query → Save.

If you want to change the properties of calculated or restricted key figures, select the calculated or restricted key figure in the InfoProvider screen area and choose  Display/Change in the Properties screen area. To save your settings, choose  Save.