Selection/Formula Properties
Use
In the Selection/Formula 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.
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 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), or the formula is displayed here automatically. You can also change the description here.
Unlike characteristics and attributes, 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.
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>
Technical Name
You can enter a technical name for the structural component. This is optional.
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 can no longer change the technical name. The generation rule is (RKF/CKF)_<date><time>, for example, RKF_20050818122045. 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 since calculated and restricted key figures are reusable objects.
Tab Page: Aggregation
Aggregation
This function is only available for formulas 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 specify 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 to be calculated before or after aggregation.
Calculating before aggregation results in poor performance because the database reads the data at the most detailed level and the formula is calculated for every record. In formula calculations, often the individual record information for just one or two specific characteristics is required; the remaining data from the InfoProvider can be aggregated.
We recommend that you control 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 or formula:
● 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
● 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 to be 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 to be 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 to be 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, the value 3000 is displayed as 3 in the report.
Use Default Settings: You can specify that the default values are always to be 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 are to 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 to be 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
For a hierarchical arrangement of structure elements, you can specify whether a node is to be 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 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 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 in which 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 is to be used 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. Therefore, no settings are required for formula collision.
Tab Page: Conversions
Currency Translation
This function is only available for selections and restricted and calculated key figures.
To create
translation types for currency translation, call the SAP Menu and choose
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.
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 variable editor.
When you execute the query, the variable 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 so 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 Currency field.
You can find more
information about currency translations under
Currency Translation
in the Business Explorer.
Unit Conversion
This function is only available for selections and restricted key figures.
You create the
conversion types for unit conversion by calling the SAP Menu and choosing
Modeling ® Object Maintenance ® Unit Conversion Types.
More information:
Creating Quantity
Conversion Types.
You can set a target unit of measure for a structural component in Query Designer.
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 variable 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
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 structure components 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 structural components 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 structural component 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 structural component 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 structural component.
More information:
Input-Ready
Query
Disaggregation
In input-ready queries, only cells on the detail level for the aggregation level can be changed. Any cells that contain 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. Cells that contain values aggregated with regard to the aggregation level cannot be changed.
● Disaggregate Value Entered
You can change cells that contain values aggregated with regard to the aggregation level. The newly entered value is distributed on 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 cells that contain values aggregated with regard to the aggregation level. The difference between the old and the newly entered value is distributed on all data records that contribute to the changed cell. The type of distribution depends on your specifications, as described below.
It is only possible to disaggregate basic key figures with the 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. You can make the following settings:
● Equal Distribution
The distribution is made evenly across all data records that contribute to the changed cell (including data records with NULL values).
● Analog Distribution (Self-Reference)
Distribution is analogous to all data records that result in the changed cell.
● Analog Distribution (With Reference to Following Structure Element)
You can define a structure element as reference. Distribution is analogous to all data records that contribute to the corresponding cell 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: Calculations
Local Calculations
You use this function to recalculate results rows and single values that are displayed in the query, according to specific criteria.
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 cells 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 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.
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 direction. 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 used for all settings to calculate the relevant default value.. 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 take over 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.
Activities
Select the structural component. In the Properties screen area, the properties dialog for the structural component appears. You can also select multiple structural components 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.