In the Selection/Formula/Cell Properties dialog box for a structural component, you can set a range of functions. These settings are applied to the structural component, irrespective of whether it is in the rows or columns.
If you have defined exception cells for selection cells, formula cells, or cell references, you can configure 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.
The Selection/Formula/Cell Properties dialog box is divided into tab pages, each of which offers different settings.
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 structural components.
Use Standard Text: If you select 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 Structural Component> / <Description of Column Structural Component>
You can enter a technical name for the structural component. This is optional.
This function is only available for structural 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 required for structural components, since structural components are handled in the same way as table fields and must be uniquely accessible. If you do not enter a technical name, the unique ID (UID) is used to uniquely identify the structural component when it is accessed 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 however, 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.
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.
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 default 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 set the factory calendar ID in Customizing. For more information, see the SAP Reference IMG under SAP Customizing Implementation Guide → SAP NetWeaver → Business Warehouse → 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:
Exception Aggregation
of Last Value (LAS) and Average (AV0)
Exception aggregation
average (AV1)
Exception aggregation
- counting
Exception aggregation
– counting (enhanced)
You can make the following settings to 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 Hide (can be shown) option is only available for structural 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.
Inverse formulas, which you need in planning calculation to perform inverse calculations of input-ready formulas, have Always Hide as the default setting. For debugging purposes, it can be useful to show these technical formulas.
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.
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.
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 figures are found, Query Designer cannot set a default value. This is indicated by Standard Value Not Found.
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 figures are found, Query Designer cannot set a default value. This is indicated by Standard Value Not Found.
Select whether +/- signs should be kept or reversed. When a sign is reversed, a positive number is given a minus sign (85 becomes -85 for example) and a negative number 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.
See also:
Priority Rule with Formatting Settings
This function is only available for structural components.
For a hierarchical arrangement of structure elements, you can define whether a node is expanded or collapsed in the structure. This property applies to the executed query and is unaffected by the status of the node in Query Designer. The node can therefore be collapsed during design for example, but expanded again when the query is executed if this is specified in this property.
This field displays the unique ID (UID) that is used for unique identification. This field is only used for information purposes. You can use the UID in URLs of the Web API to address this element for example. We recommend using technical names however, as UIDs can change when an object is deleted and then re-created.
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
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 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 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. In this case, no settings are required for formula collision.
This function is only available for selections, selection cells, 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 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 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 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 has been specified for 0CURRENCY. When you select this conversion type, From Variable is displayed in the Target Currencyfield.
For more information about currency translations, see Currency Translation in the Business Explorer.
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 variable screen appears, on which you can then 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.
The functions described below from the Planning tab are only available for selections, selection cells, and restricted key figures.
On this tab page, you can specify the various settings for BW Integrated Planning.
You can specify whether and how the data can be changed. The following options are available:
● 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
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. The following options are available:
● 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 the 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 the 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.
You can specify the type of top-down distribution here. The following options are available:
● 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) .
The functions described below from the Planning tab are only available 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 inverse calculation of the entered value to the formula’s operands. An inverse formula is required for every input-ready operand of a formula.
● 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.
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.
Set the priority for calculating a formula group.
The Note for Calculation is a property of the formula group. If you use the same operands in various input-ready formulas, the order in which the formula groups are supposed to be processed might not be obvious. You can avoid this problem by setting the priority for the formula group by entering integers in the Note for Calculation field. The lower the value entered here, the higher the formula group's priority. The same value can be entered for multiple formula groups. The values entered for various priorities do not necessarily have to be consecutive numbers.
The Note for Calculation should be understood as a relative setting rather than an absolute one. How this setting defines the order in which the formula groups are processed also depends on the context, for example on the changed, fixed or calculated cell values. Normally, it is not necessary to set the priority of the formula group in Note for Calculation.
More information: Defining Inverse Formulas.
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. The Calculate → Results As → Hide function 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. If you use two structures however – 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 value 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 deactivated, as there is no default value in this case. More information: Properties and How They Are Derived.
You can make the structural component switchable in the context of the Switch framework. To do this, enter a name in the Switch field. The structural component is thus assigned a switch in the Switch framework. More information: Switchable Query Components in the Framework.
Select the structural 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 the system displays a message to help you resolve the error. 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.