SAP BW Reporting With WebFOCUS

In this section:

 


Top of page

x
Overview of SAP BW Reporting Concepts

In a multi-dimensional data source (cube), dimensions (called characteristics in SAP BW) are categories of data, such as Region or Time, that you use to analyze and compare business performance. Dimensions consist of data elements that are called members. For example, a Region dimension could have members England and France.

Dimension members are usually organized into hierarchies. Hierarchies can be viewed as tree-like graphs where members are the nodes.

For example, the Region dimension may have the element World at its top level (the root node). The World element may have children nodes (members) representing continents. Continents, in turn, can have children nodes that represent countries, and countries can have children nodes representing states or cities. Nodes with no children are called leaf nodes.

Measures (also called key figures in SAP BW) are numerical values, such as Sales Volume or Net Income, that are used to quantify how your business is performing.

A multi-dimensional cube consists of data derived from facts, which are records about individual business transactions. For example, an individual fact record reflects a sales transaction of a certain number of items of a certain product at a certain price, which occurred in a certain store at a certain moment of time. The cube contains summarized fact values for all combinations of measures and members of different dimensions.

For example, the following combination (tuple) contains the total volume of sales of pumps in all stores in England in 2005:

{Sales Volume, Pumps, England, 2005}

The point in the cube that contains this summarized value is called a cell. A cell is addressed by a combination of members of different dimensions and a measure. In this example Sales Volume is a measure and Pumps, England, and 2005 are members of the Product, Region, and Time dimensions respectively.

Individual fact records are usually tied to the leaf nodes of each hierarchy in the cube. The fact values get included in cells addressed by these leaf nodes and added to all cells addressed by all combinations of ascendants of these leaf nodes along each hierarchy of the cube.

The operation used to summarize facts for some measures can be a simple sum or a more complex aggregation function such as an average.

It often happens that some combinations of hierarchy nodes do not have any fact records tied to them. The cells addressed by these combinations are empty cells.

As illustrated in the previous example, a tuple is a combination of members from different dimensions of a cube. The previous tuple contains members from all dimensions and, therefore, addresses a single cell. If a tuple contains only members from some dimensions, it addresses not just one cell but a whole slice of cells in the cube. For example, the following tuple does not include either Region or Time dimension members:

{Sales Volume, Pumps}

It addresses as many cells in the cube as there are members of the Region dimension times the number of members of the Time dimension.

The number of cells in the cube addressed by a single dimension member is a product of cardinalities of all other dimensions.

When all cells addressed by a member are empty, the member is called an empty member. When all cells addressed by a tuple are empty, the tuple is called an empty tuple.

By default, empty cells do not appear on report output. You can issue the following command if you want them on the report output:

ENGINE BWBAPI SET EMPTY ON

BW cubes can also include variables, which are parameters used in data selection. Variables cannot be displayed or used for sorting; they can only be used in selection criteria. A variable can be defined as mandatory or optional. If a variable is mandatory, the request must contain a WHERE test using that variable. The WHERE criteria must provide the type of restriction required based on the variable selection type, for example, a single value (using an equality test) or an interval (using a FROM/TO or GE/LE test).

When the adapter accesses a multi-dimensional cube, it uses two types of metadata elements about dimensions:

Using the hierarchy fields, the adapter can recreate the hierarchy and locate portions of the hierarchy needed to satisfy a request.


Top of page

x
Understanding Columnar and Hierarchical Reporting

Reference:

Two types of hierarchy can be represented in a synonym: level and parent/child.

A synonym describes a level hierarchy by using a separate field for each level. To report on a level hierarchy, you use columnar reporting in which you specify the field name for each level you want to display.

A synonym describes a parent/child hierarchy using a set of fields that define the hierarchical structure and the relationships between the hierarchy members. The adapter has special hierarchical reporting syntax for reporting on parent/child hierarchies.

Note: When you create a synonym, you can choose whether to describe the parent/child hierarchies in this way or as level hierarchies, with a separate field for each level.

Hierarchical reporting enables you to sort and select members of parent/child hierarchies without knowing specific level numbers.

A hierarchical reporting request goes through several phases before output is displayed.

Hierarchical Sorting and Member Selection

The first phase selects hierarchy members to display. The hierarchical reporting phrase BY or ON HIERARCHY automatically sorts and formats a hierarchy with appropriate indentations that show the parent/child relationships. If you do not want to see the entire hierarchy, you can use the WHEN phrase to select hierarchy members for display. The expression in this WHEN phrase must reference only hierarchy fields, not dimension properties or measures.

Dimension properties and measures are linked to the leaf nodes of the dimension and, therefore, cannot be used in selecting hierarchy levels for display.

Screening Dimension Data

Once hierarchy members are selected, you can screen the retrieved dimension data by applying WHERE tests to the selected members.

WHERE criteria are applied to the leaf nodes and are processed after the phase of the request that selects hierarchy members. Therefore, dimension properties can be used in WHERE tests.

These tests can also reference hierarchy fields. However, since the selection criteria are always applied to the values at the leaf nodes, they cannot select data based on values that occur at higher levels. For example, in a dimension with Continents, Countries, and Cities, your request will not display any rows if you use WHERE to select at the Country level, but it may if you use it to select at the City level.

Screening Based on Aggregated Values

Measures, being summarized values, can be referenced in WHERE TOTAL tests and COMPUTE commands because those commands are processed after the hierarchy selection and aggregation phases of the request. When screening with WHERE TOTAL, the aggregation phase of the report processing is over, so totals on the report are not recalculated to account for the data that is screened out, the rows are just removed.



x
Reference: Prerequisites for Hierarchical Reporting

Hierarchical reporting uses special metadata attributes and reporting syntax. You must:

For more information on creating synonyms, see Creating Synonyms. For more information on hierarchical reporting, see Hierarchical Reporting.


Top of page

x
Representing Hierarchies in a Synonym

Dimensions are organized into sets of hierarchies. For example, in a Time dimension, years, quarters, and months can form a hierarchical or parent/child relationship. This means that the measures for each month are aggregated into values for quarters, and the quarters are aggregated into values for years. Each point in the hierarchy is called a node. Nodes at the bottom of the hierarchy (with no children) are called leaf nodes.

According to the OLAP model, each dimension has one hierarchy called the flat hierarchy. This hierarchy contains all leaf node members of the dimension as the children of the root node named ALL (which is not assigned a field name in the synonym). In a synonym, the flat hierarchy is assigned the same name as the dimension.

In a synonym, hierarchies can be described in one of two ways:

Dimension properties apply to all hierarchies in the dimension and are listed in the synonym following all of the dimension's hierarchies. Most of the examples in this topic use synonyms named ZOPT and ZLEVEL created using the cube 0SD_C01/ZTSCQ31CQ1.



Example: Dimension Declaration

Each dimension begins with a dimension record that defines the dimension and its hierarchies. The dimension itself is level zero. In this example, only the Material Class hierarchy was selected to be part of the synonym. However, there is always a flat hierarchy with the same name as the dimension. The flat hierarchy contains a member list of leaf nodes and is always defined as a level hierarchy, so two hierarchies are listed in the [0MATERIAL] dimension.

Dimension:

DIMENSION=[0MATERIAL], CAPTION='Material', $

Flat hierarchy, same caption as the dimension:

HIERARCHY=[0MATERIAL], CAPTION='Material'... $

Material Class hierarchy:

HIERARCHY=[0MATERIAL                  001], CAPTION='Material class' ...


Example: Describing a Level Hierarchy

Each level of the hierarchy is assigned a field name consisting of the hierarchy name (for example, MATERIAL_CLASS) with the level number appended. Each field declaration also specifies the field name of its parent with the WITHIN attribute. The value stored in this field is the member's caption (title).

For the level 1 field MATERIAL_CLASS_LEVEL_01, the parent is the MATERIAL_CLASS dimension.

For the level 2 field MATERIAL_CLASS_LEVEL_02, the parent is MATERIAL_CLASS_LEVEL_01.

If a new level appears in the data, the synonym must be recreated to define this new level. The cardinality for each level is its number of members.

The following field describes level three. Its parent is level 2:

$ [0MATERIAL                     001].[LEVEL03] Cardinality 86
 FIELDNAME=MATERIAL_CLASS_LEVEL_03, ALIAS=LEVEL03, USAGE=A40, ACTUAL=A40,
      MISSING=ON,
      TITLE='Material class',
      WITHIN=MATERIAL_CLASS_LEVEL_02,
      PROPERTY=CAPTION,  $

The following field represents the flat hierarchy. There are two fields associated with this hierarchy. The first contains the member caption and the second contains the member name:

FIELDNAME=MATERIAL_LEVEL_01, ALIAS=LEVEL01, USAGE=A40, ACTUAL=A40,
      MISSING=ON,
      TITLE='Material Member Caption',
      WITHIN='*[0MATERIAL]',
      PROPERTY=CAPTION,  $
    FIELDNAME=MATERIAL_NAME, ALIAS=MEMBER_NAME, USAGE=A18, ACTUAL=A18,
      MISSING=ON,
      TITLE='Material Member Name',
      REFERENCE=MATERIAL_LEVEL_01, PROPERTY=NAME,  $


Example: Describing a Parent/Child Hierarchy

Several fields are used to define a parent/child hierarchy. Each has a PROPERTY attribute that describes which hierarchy property it represents. The hierarchy field names are formed by appending a suffix to the hierarchy name.

For example, the caption of a hierarchy named MATERIAL_CLASS is stored in a field whose name is MATERIAL_CLASS_CAPTION and whose property attribute is PROPERTY=CAPTION.

The following table describes the hierarchy fields:

Description of Data

PROPERTY=

Field Suffix

Member Unique ID (unique within the cube)

UID

none

Member Name (unique within the hierarchy)

NAME

_NAME

Member Level Number

LEVEL_NUMBER

_LVLNO

Member Parent

PARENT_OF

_PARENT

Parent Level Number

PARENT_LEVEL_NUMBER

_PARENT_LVLNO

Number of Children

CHILDREN_CARDINALITY

_CHILDREN_CARD

Member Caption (title on reports)

CAPTION

_CAPTION

The following declaration for the MATERIAL_CLASS hierarchy describes the field that contains the unique ID of a member (PROPERTY=UID):

 FIELDNAME=MATERIAL_CLASS, USAGE=A143, ACTUAL=A143,
   MISSING=ON,
   TITLE='Material class',
   WITHIN='*[0MATERIAL                     001]',
   REFERENCE=[0MATERIAL], PROPERTY=UID,  $

The following declaration for the MATERIAL_CLASS hierarchy defines the field that contains a member title (PROPERTY=CAPTION):

 FIELDNAME=MATERIAL_CLASS_CAPTION, USAGE=A60, ACTUAL=A60,
   MISSING=ON,
   TITLE='Material class CAPTION',
   REFERENCE=MATERIAL_CLASS, PROPERTY=CAPTION,  $

Each parent/child hierarchy has three additional fields defined to make reports easier to read:

For example, for the MATERIAL_CLASS hierarchy, the DEFINE fields are named:



Example: Dimension Properties

Following all of a dimension hierarchies, the dimension properties (called attributes in SAP BW) are described. Each of these has PROPERTY=UDA (User Defined Attribute) in the synonym. For example the following field represents a member's net weight property:

FIELDNAME=NET_WEIGHT__KEY_, ALIAS='20NET_WEIGHT', USAGE=A9, ACTUAL=A9,
      MISSING=ON,
      TITLE='Net weight (Key)',
      REFERENCE=[0MATERIAL], PROPERTY=UDA,  $


Example: Sample Request Using a Level Hierarchy

A report request against a level hierarchy must specify the field name for each level of the hierarchy required in the report. For example, the following request displays the sales volume measure for levels one through three of the Material Class hierarchy:

TABLE FILE ZLEVEL
SUM SALES_VOLUME
BY MATERIAL_CLASS_LEVEL_01 AS 'LEVEL1'
BY MATERIAL_CLASS_LEVEL_02 AS 'LEVEL2'
BY MATERIAL_CLASS_LEVEL_03 AS 'LEVEL3'
ON TABLE COLUMN-TOTAL
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
GRID=OFF, $
END

The output is:

In order to get a total for the entire hierarchy, you have to use an ON TABLE COLUMN-TOTAL command in the request or add another SUM command without a BY phrase (and this would add another column to the report output).



Example: Sample Request Using a Parent/Child Hierarchy

A report request against a parent/child hierarchy can use the BY HIERARCHY phrase to report against the entire hierarchy. The output is automatically formatted with appropriate indentations to show the hierarchy levels and relationships. For example, the following request shows the sales volume measure for three levels of the Material Class hierarchy:

TABLE FILE ZOPT
SUM SALES_VOLUME 
BY MATERIAL_CLASS_CAPTION HIERARCHY 
SHOW TO DOWN 3
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
GRID=OFF, $
END

The output is:

The report request does not have to reference specific hierarchy levels. The BY HIERARCHY phrase recreates and formats the hierarchy for display. You can also use a WHEN phrase to select a portion of the hierarchy and a SHOW phrase to specify how many levels above and below the selected portion of the hierarchy you want to display. This display format clearly shows the parent/child relationships between the hierarchy members.

In addition, you can specify whether you want the measure values for each parent to represent aggregates for all of its children (full total) or only those selected for display (visual total).

For more information, see Hierarchical Reporting.


Top of page

x
Hierarchical Reporting

How to:

Reference:

When you issue a request against a cube, some of the requirements and features available depend on the type of hierarchy defined in the synonym.

With a parent/child hierarchy, you can specify whether the measure values displayed for each parent should show the sum of all of its descendants (full total) or the sum of its displayed descendants (visual total). For level hierarchies, the report always displays full totals, which are the values actually stored in the cube.

When a synonym defines parent/child hierarchies, you can use the BY HIERARCHY phrase to sort and format the hierarchy. You can also limit the portion of the hierarchy selected for display using the WHEN phrase.

When a hierarchical request is processed, the first step is to build the hierarchy and mark which nodes should be included, which should be excluded, and which are needed for context.

The next stage fills the hierarchy with measure values. This stage applies WHERE criteria at the leaf nodes to further qualify the members selected for the report. Dimension properties cannot be used in the initial selection phase of the request, but can be used to screen the selected rows based on dimension data.

Measure values also cannot be used to select hierarchy levels for reporting. After the hierarchy rows have been selected, screened, and aggregated, WHERE TOTAL tests can limit the rows displayed based on measure values.



x
Syntax: How to Display Parent/Child Hierarchies

The following syntax can be used to generate hierarchical reports when the synonym defines parent/child hierarchies:

SUM [FROLL.]measure_field ...
BY hierarchy_field [HIERARCHY [WHEN expression_using_hierarchy_fields;]
[SHOW [TOP|UP n] [TO {BOTTOM|DOWN m}] [byoption [WHEN condition] ...] ]
.
.
.
[WHERE expression_using_dimension_data]
.
.
.
[ON hierarchy_field HIERARCHY [WHEN expression_using_hierarchy_fields;]
[SHOW [TOP|UP n] [TO BOTTOM|DOWN m] [byoption [WHEN condition] ...]]

where:

FROLL

Specifies a full roll-up of the measure. With a full roll-up, the value displayed is the value found in the cube. This value may not reflect the sum of its displayed descendants if some descendants are eliminated from the output based on the WHEN and SHOW options. When FROLL is not specified, the value displayed is a visual total, which means it is the total of the values for its displayed descendants.

Note: If a request uses WHERE criteria to screen out some data, FROLL will not display the value found in the cube. It will display the roll-up of the selected data.

measure_field

Is the field name of a measure.

BY hierarchy_field HIERARCHY

Identifies the hierarchy used for sorting. The field must be a hierarchy field.

ON hierarchy_field HIERARCHY

Identifies the hierarchy used for sorting. The field must be a hierarchy field. The request must include either a BY phrase or a BY HIERARCHY phrase for this field name.

WHEN expression_using_hierarchy_fields;

Selects hierarchy members. The WHEN phrase must immediately follow the word HIERARCHY to distinguish it from a WHEN phrase associated with a BY option (such as SUBFOOT). Any expression using only hierarchy fields is supported. The WHEN phrase can be on the BY HIERARCHY command or the ON HIERARCHY command, but not both.

SHOW

Specifies which levels to show on the report output relative to the levels selected by the WHEN phrase. If there is no WHEN phrase, the SHOW option is applied to the root node of the hierarchy. The SHOW option can be specified on the BY HIERARCHY phrase or the ON HIERARCHY phrase, but not both.

n

Is the number of ascendants above the set of selected members that will have measure values. All ascendants appear on the report to show the hierarchical context of the selected members. However, ascendants that are not included in the SHOW phrase appear on the report with missing data symbols in the report columns that display measures. The default for n is 0.

TOP

Specifies that ascendant levels to the root node of the hierarchy will be populated with measure values.

TO

Is required when specifying a SHOW option for descendant levels.

BOTTOM

Specifies all descendants to the leaf nodes of the hierarchy will be populated with measure values. This is the default value.

m

Is the number of descendants of each selected level that will display. The default for m is BOTTOM, which displays all descendants.

byoption

Is one of the following sort-based options: PAGE-BREAK, REPAGE, RECAP, RECOMPUTE, SKIP-LINE, SUBFOOT, SUBHEAD, SUBTOTAL, SUB-TOTAL, SUMMARIZE, UNDER-LINE. If you specify SUBHEAD or SUBFOOT, you must place the WHEN phrase on the line following the heading or footing text.

condition

Is a logical expression.

expression_using_dimension_data

Screens the rows selected in the BY/ON HIERARCHY and WHEN phrases based on dimension data. The expression can use dimension properties and hierarchy fields. However, the selection criteria are always applied to the values at the leaf nodes. Therefore, you cannot use WHERE to select rows based on hierarchy field values that occur at higher levels. For example, in a dimension with Continents, Countries, and Cities, your request will not display any rows if you use WHERE to select a Country name, but it may if you use it to select a City name.

The following examples illustrate hierarchical reporting using the BY HIERARCHY phrase. The zopt Master File was created with parent/child hierarchies by selecting optimized as the hierarchy type when creating the synonym, based on the cube 0SD_C01/ZTSCQ31CQ1.



Example: Reporting on a Whole Hierarchy

The following request produces visual totals (SALES_VOLUME) and full totals (FROLL.SALES_VOLUME). The BY HIERARCHY phrase specifies hierarchical reporting. There is no WHEN phrase to limit the portion of the hierarchy displayed. Also note that the full roll-up is displayed with the prefix FSUM added to the field name. The full total is the same as the visual total for a report on the entire hierarchy:

TABLE FILE ZOPT
WRITE SALES_VOLUME FROLL.SALES_VOLUME  
BY MATERIAL_CLASS_CAPTION HIERARCHY 
ON TABLE SET PAGE NOPAGE
ON TABLE SET SCREEN PAPER
ON TABLE SET LINES 88
ON TABLE SUBHEAD
"Reporting on a Whole Hierarchy"
ON TABLE SET STYLE *
TYPE=REPORT, SIZE=8,$
TYPE=REPORT, GRID=OFF, WRAP=OFF, $
TYPE=TABHEADING, SIZE=10, COLOR=RED, JUSTIFY=CENTER, $
END

The output is:



Example: Selecting a Hierarchy Member

The following request produces a visual total and full total for the MATERIAL_CLASS hierarchy of the MATERIAL dimension, but limits the members selected for display with the WHEN phrase. Note that the hierarchy member selected is displayed in its context (all ancestors to the root of the hierarchy). However, the ancestors are not requested in the report and are, therefore, displayed with missing data symbols. All descendants of the selected members appear in the report output because the default SHOW option for descendants is BOTTOM:

TABLE FILE ZOPT
WRITE SALES_VOLUME FROLL.SALES_VOLUME 
BY MATERIAL_CLASS_CAPTION HIERARCHY 
WHEN MATERIAL_CLASS_CAPTION EQ 'Computer hardware'; 
ON TABLE SET PAGE NOPAGE
ON TABLE SUBHEAD
"Selecting a Hierarchy Member"
ON TABLE SET STYLE *
TYPE=REPORT, SIZE=8,$
TYPE=REPORT, GRID=OFF, WRAP=OFF, $
TYPE=TABHEADING, SIZE=10, COLOR=RED, JUSTIFY=CENTER, $
END

The output is:



Example: Selecting a Member and Adding a Parent

In the following request, the SHOW option UP 1 TO DOWN 0 added to the WHEN phrase adds the parent (Computer systems) of the selected member (Computer hardware). This parent now contains values for the measures rather than missing data symbols. However, the full total column for the parent contains the sum of all of its descendants, not just the selected Computer hardware member, while the visual total shows the total only for the Computer hardware member:

TABLE FILE ZOPT
WRITE SALES_VOLUME FROLL.SALES_VOLUME 
BY MATERIAL_CLASS_CAPTION HIERARCHY
WHEN MATERIAL_CLASS_CAPTION EQ 'Computer hardware'; 
SHOW UP 1 TO DOWN 0 
ON TABLE SET PAGE NOPAGE
ON TABLE SUBHEAD
"Selecting a Member and Adding a Parent"
ON TABLE SET STYLE *
TYPE=REPORT, SIZE=8,$
TYPE=REPORT, GRID=OFF, WRAP=OFF, $
TYPE=TABHEADING, SIZE=10, COLOR=RED, JUSTIFY=CENTER, $
END

The output is:



Example: Selecting a Member and Adding Children

In the following request, the SHOW option UP 0 TO DOWN 1 added to the WHEN phrase adds the children (Computer and Computer components) of the selected member (Computer hardware). Because no children of the selected member are excluded, and no higher level members are in the SHOW set, the full and visual totals are the same:

TABLE FILE ZOPT
WRITE SALES_VOLUME FROLL.SALES_VOLUME 
BY MATERIAL_CLASS_CAPTION HIERARCHY
WHEN MATERIAL_CLASS_CAPTION EQ 'Computer hardware'; 
SHOW UP 0 TO DOWN 1 
ON TABLE SET PAGE NOPAGE
ON TABLE SUBHEAD
"Selecting a Member and Adding Children"
ON TABLE SET STYLE *
TYPE=REPORT, SIZE=8,$
TYPE=REPORT, GRID=OFF, WRAP=OFF, $
TYPE=TABHEADING, SIZE=10, COLOR=RED, JUSTIFY=CENTER, $
END

The output is:



Example: Selecting a Member and Showing All Ascendants

In the following request, the SHOW option TOP TO DOWN 0 added to the WHEN phrase adds all ascendants but no descendants of the selected member (Computer hardware). These parents now contain values for the measures rather than missing data symbols. However, the full total column for the ascendants contains the sum of all of their descendants, not just the specified Computer hardware member, while the visual total shows the total for only the Computer hardware member:

TABLE FILE ZOPT
WRITE SALES_VOLUME FROLL.SALES_VOLUME 
BY MATERIAL_CLASS_CAPTION HIERARCHY
WHEN MATERIAL_CLASS_CAPTION EQ 'Computer hardware'; 
SHOW TOP TO DOWN 0 
ON TABLE SET PAGE NOPAGE
ON TABLE SUBHEAD
"Selecting a Member and Adding All Ascendants"
ON TABLE SET STYLE *
TYPE=REPORT, SIZE=8,$
TYPE=REPORT, GRID=OFF, WRAP=OFF, $
TYPE=TABHEADING, SIZE=10, COLOR=RED, JUSTIFY=CENTER, $
END

The output is:



Example: Selecting a Member and Showing All Descendants

In the following request, the absence of a SHOW phrase is equivalent to SHOW UP 0 TO BOTTOM. The WHEN phrase selects the member Computer hardware. Because no children of the selected member are excluded and no higher level members are in the SHOW set, the full and visual totals are the same, and the ascendant levels display missing data symbols:

TABLE FILE ZOPT
WRITE SALES_VOLUME FROLL.SALES_VOLUME 
BY MATERIAL_CLASS_CAPTION HIERARCHY
WHEN MATERIAL_CLASS_CAPTION EQ 'Computer hardware';
ON TABLE SET PAGE NOPAGE
ON TABLE SUBHEAD
"Selecting a Member and Showing All Descendants"
ON TABLE SET STYLE *
TYPE=REPORT, SIZE=8,$
TYPE=REPORT, GRID=OFF, WRAP=OFF, $
TYPE=TABHEADING, SIZE=10, COLOR=RED, JUSTIFY=CENTER, $
END

The output is:



Example: Selecting a Member and Showing All Ascendants and Descendants

In the following request, the SHOW option TOP added to the WHEN phrase adds all ascendants and descendants (since TO BOTTOM is the default) of the selected member (Computer hardware). These parents are now in the SHOW set and contain values for the measures rather than missing data symbols. However, the full total column for the ascendants contains the sums of all of their descendants, not just the specified Computer hardware member, while the visual total shows the total for only the Computer hardware member:

TABLE FILE ZOPT
WRITE SALES_VOLUME FROLL.SALES_VOLUME 
BY MATERIAL_CLASS_CAPTION HIERARCHY
WHEN MATERIAL_CLASS_CAPTION EQ 'Computer hardware'; 
SHOW TOP 
ON TABLE SET PAGE NOPAGE
ON TABLE SUBHEAD
"Selecting a Member and Adding All Ascendants and Descendants"
ON TABLE SET STYLE *
TYPE=REPORT, SIZE=8,$
TYPE=REPORT, GRID=OFF, WRAP=OFF, $
TYPE=TABHEADING, SIZE=10, COLOR=RED, JUSTIFY=CENTER, $
END

The output is:



Example: Displaying Members of a Range of Hierarchy Levels

The following request uses a WHEN phrase on the MATERIAL_CLASS_LVLNO field to display members of levels 0 through 3 of the hierarchy. The SHOW option TO DOWN 0 eliminates printing of any descendant levels not selected by the WHEN phrase. The full and visual totals are the same because no children were excluded when calculating the visual totals:

TABLE FILE ZOPT
WRITE SALES_VOLUME FROLL.SALES_VOLUME
BY MATERIAL_CLASS_CAPTION HIERARCHY 
WHEN MATERIAL_CLASS_LVLNO LE 3; 
SHOW TO DOWN 0
ON TABLE SET PAGE NOPAGE
ON TABLE SUBHEAD
"Displaying Members of a Range of Hierarchy Levels"
ON TABLE SET STYLE *
TYPE=REPORT, SIZE=8,$
TYPE=REPORT, GRID=OFF, WRAP=OFF, $
TYPE=TABHEADING, SIZE=10, COLOR=RED, JUSTIFY=CENTER, $
END

The output is:



Example: Selecting Members for Display and Screening on Member Values

You use the WHEN phrase to select hierarchy members to display on the report. Each of the selected members is displayed in its context. The ascendants of selected members display missing data symbols. When a request is processed, the first step is to build the hierarchy and mark which nodes should be included, which should be excluded, and which are needed for context but should display missing data symbols according to the WHEN phrase.

The next stage fills the hierarchy with measure values. This stage applies WHERE criteria (which must select on the lowest level of the hierarchy or the report will be empty).

If a WHERE is specified without a WHEN, no nodes are marked as excluded, so no nodes display missing values.

The following request selects the portion of the hierarchy whose members contain the values Computer systems or Pumps. It shows the parents of these members and all levels of descendants. Note that the dimension property MATERIAL_TYPE__MEDIUM_NAME_ is displayed in the request and that only the leaf members have values for this property:

TABLE FILE ZOPT
WRITE MATERIAL_TYPE__MEDIUM_NAME_ AS 'MEDIUM NAME' SALES_VOLUME
BY MATERIAL_CLASS_CAPTION HIERARCHY 
WHEN MATERIAL_CLASS_CAPTION EQ 'Computer systems' OR 'Pumps'; 
SHOW UP 1
ON TABLE SET PAGE NOPAGE
ON TABLE SET SCREEN PAPER
ON TABLE SET LINES 88
ON TABLE SUBHEAD
"Selecting Hierarchy Members"
ON TABLE SET STYLE *
TYPE=REPORT, SIZE=8,$
TYPE=REPORT, GRID=OFF, WRAP=OFF, $
TYPE=TABHEADING, SIZE=10, COLOR=RED, JUSTIFY=CENTER, $
END

The output is:

The following request selects the portion of the hierarchy whose members contain the values Computer systems or Pumps. It then screens on the dimension property MATERIAL_TYPE__MEDIUM_NAME_ equal to Finished product. This WHERE screening condition does not affect the portion of the hierarchy that was selected by WHEN, but it does not display or include in the totals those rows that had no data meeting the WHERE condition:

TABLE FILE ZOPT
WRITE MATERIAL_TYPE__MEDIUM_NAME_ AS 'MEDIUM NAME' SALES_VOLUME
BY MATERIAL_CLASS_CAPTION HIERARCHY
WHEN MATERIAL_CLASS_CAPTION EQ 'Computer systems' OR 'Pumps';
SHOW UP 1 
WHERE MATERIAL_TYPE__MEDIUM_NAME_ EQ 'Finished product'; 
ON TABLE SET PAGE NOPAGE
ON TABLE SUBHEAD
"Selecting Members and Screening Data Values"
ON TABLE SET STYLE *
TYPE=REPORT, SIZE=8,$
TYPE=REPORT, GRID=OFF, WRAP=OFF, $
TYPE=TABHEADING, SIZE=10, COLOR=RED, JUSTIFY=CENTER, $
END

The output is:



Example: Comparing Member Selection With Data Screening

The following request selects hierarchy levels less than or equal to 3 and screens values at the lowest level of the hierarchy for captions containing Flatscreen or Harddisk. The SHOW option TO DOWN 0 prevents the display of descendants of the selected members:

TABLE FILE ZOPT
WRITE SALES_VOLUME FROLL.SALES_VOLUME 
BY MATERIAL_CLASS_CAPTION HIERARCHY 
WHEN MATERIAL_CLASS_LVLNO LE 3;
SHOW TO DOWN 0
WHERE MATERIAL_CLASS_CAPTION CONTAINS 'Flatscreen' OR 'Harddisk'; 
ON TABLE SET PAGE NOPAGE
ON TABLE SUBHEAD
"Comparing Member Selection With Data Screening"
ON TABLE SET STYLE *
TYPE=REPORT, SIZE=8,$
TYPE=REPORT, GRID=OFF, WRAP=OFF, $
TYPE=TABHEADING, SIZE=10, COLOR=RED, JUSTIFY=CENTER, $
END

The output is:



Example: Using SHOW Without WHEN

The following request does not contain a WHEN phrase, so the SHOW option DOWN TO 0 applies to the root node (as if there had been a WHEN phrase that selected only the root node). Therefore, the root level is the only one shown on the report output:

TABLE FILE ZOPT
WRITE SALES_VOLUME FROLL.SALES_VOLUME
BY MATERIAL_CLASS_CAPTION HIERARCHY 
SHOW TO DOWN 0 
ON TABLE SET PAGE NOPAGE
ON TABLE SUBHEAD
"SHOW Without WHEN"
ON TABLE SET STYLE *
TYPE=REPORT, SIZE=8,$
TYPE=REPORT, GRID=OFF, WRAP=OFF, $
TYPE=TABHEADING, SIZE=10, COLOR=RED, JUSTIFY=CENTER, $
END

The output is:



Example: Using SKIP-LINE

The following request adds the BY option SKIP-LINE to the BY HIERARCHY phrase:

TABLE FILE ZOPT
WRITE SALES_VOLUME FROLL.SALES_VOLUME
BY MATERIAL_CLASS_CAPTION HIERARCHY
WHEN MATERIAL_CLASS_CAPTION EQ 'Computer hardware';
SHOW UP 2 TO DOWN 2 SKIP-LINE 
ON TABLE SET PAGE NOPAGE
ON TABLE SUBHEAD
"Using SKIP-LINE"
ON TABLE SET STYLE *
TYPE=REPORT, SIZE=8,$
TYPE=REPORT, GRID=OFF, WRAP=OFF, $
TYPE=TABHEADING, SIZE=10, COLOR=RED, JUSTIFY=CENTER, $
END

The output is:



Example: Using Sort Options Conditional on a Measure

The following request has a BY HIERARCHY command with a WHEN phrase to select members as well as a WHEN phrase to control the UNDER-LINE option.

The SUBFOOT and PAGE-BREAK options are in two ON phrases that reference the same hierarchy field (all of the BY options could have been on the BY HIERARCHY phrase).

Each BY option has its own WHEN phrase. Note that the WHEN phrases for the BY options use a measure field in their expressions. In this example, all of the BY options are activated when the sales volume value is zero:

TABLE FILE ZOPT
WRITE SALES_VOLUME FROLL.SALES_VOLUME
BY MATERIAL_CLASS_CAPTION HIERARCHY
WHEN MATERIAL_CLASS_CAPTION EQ 'Computer hardware';
SHOW TOP UNDER-LINE WHEN SALES_VOLUME EQ 0; 
ON MATERIAL_CLASS_CAPTION SUBFOOT
" "
"The Sum is zero"
" "
WHEN SALES_VOLUME EQ 0; 
ON MATERIAL_CLASS_CAPTION PAGE-BREAK WHEN SALES_VOLUME EQ 0; 
ON TABLE SUBHEAD
"Using BY Options With WHEN on a Measure"
ON TABLE SET STYLE *
TYPE=REPORT, SIZE=8,$
TYPE=REPORT, GRID=OFF, WRAP=OFF, $
TYPE=TABHEADING, SIZE=10, COLOR=RED, JUSTIFY=CENTER, $
END

The output is:



Example: Using Sort Options Conditional on a Dimension Property

The following request has a BY HIERARCHY command with a WHEN phrase to select members. The ON phrase has a PAGE-BREAK option that is activated when MATERIAL_TYPE__MEDIUM_NAME_ equals 'Finished product':

TABLE FILE ZOPT
WRITE MATERIAL_TYPE__MEDIUM_NAME_ AS 'MEDIUM NAME'
 SALES_VOLUME 
BY MATERIAL_CLASS_CAPTION HIERARCHY
WHEN MATERIAL_CLASS_CAPTION EQ 'Computer hardware';
SHOW UP 2
ON MATERIAL_CLASS_CAPTION PAGE-BREAK
WHEN MATERIAL_TYPE__MEDIUM_NAME_ EQ 'Finished product'; 
ON TABLE SUBHEAD
"Using PAGE-BREAK With WHEN on a Dimension Property"
ON TABLE SET STYLE *
TYPE=REPORT, SIZE=8,$
TYPE=REPORT, GRID=OFF, WRAP=OFF, $
TYPE=TABHEADING, SIZE=10, COLOR=RED, JUSTIFY=CENTER, $
END

The output is:



Example: Using Sort Options Conditional on a Hierarchy Field

The following request has a BY HIERARCHY command with a WHEN phrase to select members. The ON phrase has a PAGE-BREAK option that is activated when MATERIAL_CLASS_CAPTION contains 'Harddisk':

TABLE FILE ZOPT
WRITE SALES_VOLUME FROLL.SALES_VOLUME
BY MATERIAL_CLASS_CAPTION HIERARCHY
WHEN MATERIAL_CLASS_CAPTION EQ 'Computer hardware';
SHOW UP 2
ON MATERIAL_CLASS_CAPTION PAGE-BREAK
WHEN MATERIAL_CLASS_CAPTION CONTAINS 'Harddisk'; 
ON TABLE SUBHEAD
"Using PAGE-BREAK With WHEN on a Hierarchy Field"
ON TABLE SET STYLE *
TYPE=REPORT, SIZE=8,$
TYPE=REPORT, GRID=OFF, WRAP=OFF, $
TYPE=TABHEADING, SIZE=10, COLOR=RED, JUSTIFY=CENTER, $
END

The output is:



Example: Using WHERE TOTAL to Screen on a Measure

The following request has a BY HIERARCHY phrase with a WHEN phrase to select members. The WHERE TOTAL phrase selects rows in which the sales volume is not zero:

TABLE FILE ZOPT
WRITE SALES_VOLUME FROLL.SALES_VOLUME
BY MATERIAL_CLASS_CAPTION HIERARCHY
WHEN MATERIAL_CLASS_CAPTION EQ 'Computer hardware';
SHOW TOP 
WHERE TOTAL SALES_VOLUME NE 0 
ON TABLE SET PAGE NOPAGE
ON TABLE SUBHEAD
"Using WHERE TOTAL to Screen on a Measure"
ON TABLE SET STYLE *
TYPE=REPORT, SIZE=8,$
TYPE=REPORT, GRID=OFF, WRAP=OFF, $
TYPE=TABHEADING, SIZE=10, COLOR=RED, JUSTIFY=CENTER, $
END

The output is:



Example: Using Two BY HIERARCHY Phrases

The following request has a BY HIERARCHY phrase for the Material Class hierarchy and a second BY HIERARCHY phrase for the World/Continents/Countries hierarchy (which is from a different dimension, as required). All selected members for the World/Continents/Countries hierarchy are repeated for each selected member of the Material Class hierarchy. The WHERE TOTAL phrase omits rows in which the sales volume is not zero:

TABLE FILE ZOPT
WRITE SALES_VOLUME 
BY MATERIAL_CLASS_CAPTION HIERARCHY 
SHOW TO DOWN 2 AS 'Material Class' 
BY WORLD___CONTINENTS___COUNTRIES_CAPTION HIERARCHY 
WHEN WORLD___CONTINENTS___COUNTRIES_CAPTION OMITS 'TEST'
AND WORLD___CONTINENTS___COUNTRIES_LVLNO LE 2;
SHOW UP 0 TO DOWN 0 AS 'Region'
WHERE TOTAL SALES_VOLUME NE 0
ON TABLE SET PAGE NOPAGE
ON TABLE SUBHEAD
"Using Two BY HIERARCHY Phrases"
ON TABLE SET STYLE *
TYPE=REPORT, SIZE=8,$
TYPE=REPORT, GRID=OFF, WRAP=OFF, $
TYPE=TABHEADING, SIZE=10, COLOR=RED, JUSTIFY=CENTER, $
END

The output is:



Example: Using BY HIERARCHY and BY Phrases

The following request has a BY phrase for the World/Continents/Countries hierarchy and a BY HIERARCHY phrase for the Material Class hierarchy. All selected members for the Material Class hierarchy are repeated for each selected member of the World/Continents/Countries hierarchy (which is not displayed with hierarchical indentations when referenced in a BY phrase). A BY on a unique field is required. Because the captions are not unique for the World/Continents/Countries hierarchy, there is one BY on a unique field with the NOPRINT option and another BY on the caption field:

TABLE FILE ZOPT
WRITE SALES_VOLUME 
BY WORLD___CONTINENTS___COUNTRIES NOPRINT
BY WORLD___CONTINENTS___COUNTRIES_CAPTION
BY MATERIAL_CLASS_CAPTION HIERARCHY  
SHOW TO DOWN 2
WHERE WORLD___CONTINENTS___COUNTRIES_CAPTION OMITS 'TEST'
AND WORLD___CONTINENTS___COUNTRIES_LVLNO EQ 2;
WHERE TOTAL SALES_VOLUME NE 0
ON TABLE SET PAGE NOPAGE
ON TABLE SUBHEAD
"Using BY HIERARCHY and BY Phrases"
ON TABLE SET STYLE *
TYPE=REPORT, SIZE=8,$
TYPE=REPORT, GRID=OFF, WRAP=OFF, $
TYPE=TABHEADING, SIZE=10, COLOR=RED, JUSTIFY=CENTER, $
END

The output is:

The following is the same request with the BY HIERARCHY phrase first and the BY phrase second. All selected members for the World/Continents/Countries hierarchy are repeated for each selected member of the Material Class hierarchy:

TABLE FILE ZOPT
WRITE SALES_VOLUME 
BY MATERIAL_CLASS_CAPTION HIERARCHY 
SHOW TO DOWN 2 
BY WORLD___CONTINENTS___COUNTRIES NOPRINT
BY WORLD___CONTINENTS___COUNTRIES_CAPTION 
WHERE WORLD___CONTINENTS___COUNTRIES_CAPTION OMITS 'TEST'
AND WORLD___CONTINENTS___COUNTRIES_LVLNO EQ 2;
WHERE TOTAL SALES_VOLUME NE 0
ON TABLE SET PAGE NOPAGE
ON TABLE SUBHEAD
"Using BY HIERARCHY and BY Phrases"
ON TABLE SET STYLE *
TYPE=REPORT, SIZE=8,$
TYPE=REPORT, GRID=OFF, WRAP=OFF, $
TYPE=TABHEADING, SIZE=10, COLOR=RED, JUSTIFY=CENTER, $
END

The output is:



Example: Using ON HIERARCHY Without BY HIERARCHY

The following request has a BY phrase and an ON HIERARCHY phrase for the Material Class hierarchy. All hierarchy options and BY options are supported on the ON HIERARCHY phrase. This request also has a WHERE clause that selects rows based on the value of a hierarchy field (MATERIAL_CLASS_CAPTION). The WHERE test selects rows based on values at the leaf nodes (Harddisk is a value found on leaf nodes):

TABLE FILE ZOPT
WRITE SALES_VOLUME 
BY MATERIAL_CLASS_CAPTION
ON MATERIAL_CLASS_CAPTION HIERARCHY 
WHEN MATERIAL_CLASS_CAPTION CONTAINS 'Computer'; 
WHERE MATERIAL_CLASS_CAPTION CONTAINS 'Harddisk' 
ON TABLE SET PAGE NOPAGE
ON TABLE SUBHEAD
"Using BY and ON HIERARCHY"
ON TABLE SET STYLE *
TYPE=REPORT, SIZE=8,$
TYPE=REPORT, GRID=OFF, WRAP=OFF, $
TYPE=TABHEADING, SIZE=10, COLOR=RED, JUSTIFY=CENTER, $
END

The output is:



x
Reference: Hierarchical Reports With Multiple Display Commands

In a request with more than one display command, each command must repeat all of the sort phrases from the previous command in the same order after which additional sort phrases can be added. In a hierarchical reporting request, the following rules must be followed:



Example: Using Multiple Display Commands in a Hierarchical Report

The following request has two WRITE commands. The first WRITE command has a BY HIERARCHY sort phrase for the MATERIAL CLASS hierarchy and a BY HIERARCHY phrase for the WORLD_CONTINENTS_COUNTRIES hierarchy. The second WRITE command repeats these phrases and adds a BY phrase for the SALESORG dimension:

TABLE FILE ZOPT
WRITE SALES_VOLUME 
BY MATERIAL_CLASS_CAPTION HIERARCHY 
WHEN MATERIAL_CLASS_CAPTION EQ 'Computer hardware'; AS 'Material Class'
SHOW TO DOWN 2
  
BY WORLD___CONTINENTS___COUNTRIES_CAPTION HIERARCHY 
WHEN WORLD___CONTINENTS___COUNTRIES_CAPTION OMITS 'TEST'
AND WORLD___CONTINENTS___COUNTRIES_LVLNO EQ 2; AS 'Region'
 
WRITE SALES_VOLUME SALES_ORDER_ITEM 
BY MATERIAL_CLASS_CAPTION HIERARCHY 
BY WORLD___CONTINENTS___COUNTRIES_CAPTION HIERARCHY 
  
BY SALES_ORGANIZATION_LEVEL_01 AS 'Sales Organization'
 
ON TABLE SET PAGE NOPAGE
ON TABLE SUBHEAD
"Using Multiple Display Commands"
ON TABLE SET STYLE *
TYPE=REPORT, SIZE=8,$
TYPE=REPORT, GRID=OFF, WRAP=OFF, $
TYPE=TABHEADING, SIZE=10, COLOR=RED, JUSTIFY=CENTER, $
END

The BY HIERARCHY phrase for the MATERIAL CLASS hierarchy has a WHEN phrase that selects members whose caption is Computer hardware. The SHOW option (TO DOWN 2) displays two levels of descendants below the Computer hardware level. Ascendants of the selected members display for context, but with missing data symbols instead of values.

For the WORLD_CONTINENTS_COUNTRIES hierarchy, the selected members have level number two and do not have the characters TEST in their captions. The SHOW option displays two levels of descendants on the report output. Ascendants display for context, with missing data symbols. All selected members of the WORLD_CLASS_COUNTRIES hierarchy display for each selected member of the MATERIAL CLASS hierarchy.

The output is:


Top of page

x
Columnar Reporting

Reference:

If you run a standard TABLE or FML report against a cube, you are running what is known as a Slice report. This two-dimensional report resembles a slice of a larger multi-dimensional cube. A Slice report flattens out the multi-dimensional structure of a cube to show information from one angle.

To create a valid Slice report, you must understand the cube data structure and the logic and assumptions that the Adapter for SAP BW uses when slicing a cube.



x
Reference: Valid and Invalid Slices-Cube Slicing Logic

The Adapter for SAP BW has built-in logic that enables it to determine the rows and cells to extract from rollups in order to deliver Slice reports. TABLE uses the lowest level BY phrase for each dimension to determine at which rollup to locate the lookup for aggregations. It then rolls the result up from there, performing aggregations on the data as needed to ensure the consistency of the results.

If you do not specify a BY field in your TABLE request, you may receive invalid data or a message. This happens because without a BY field to determine the level of rollup at which to perform the data lookup, TABLE does not know where to source the result.



Example: Displaying Single Dimensions in a Slice Report
TABLE FILE MYCUBE
SUM STORE_COST STORE_SALES
BY COUNTRY_LEVEL_01 ON TABLE COLUMN_TOTAL
END

This report displays aggregate data sorted against one dimension in the source. The standard measures such as STORE_COST and STORE_SALES are referenced with SUM. Column totals are requested in the report.



Example: Displaying Multiple Dimensions in a Slice Report
TABLE FILE MYCUBE
SUM STORE_COST STORE_SALES
PRINT PROFIT
BY COUNTRY_LEVEL_01 BY STATE_PROVINCE_LEVEL BY CITY_LEVEL_01
END

This report displays the same data as the previous one, except that it sorts against both dimension hierarchies in the cube. The order of the sort fields follows the logical order of the dimension hierarchy.


Top of page

x
Reporting With Variables

A BW query uses variables to supply values at execution time. A variable can have an Entry_Type of either Mandatory/Required or Optional. All mandatory variable values must be supplied in the TABLE or SQL request. A WHERE or IF statement in the request provides the run-time value(s). For example, if the variable COMPANY_CODE is mandatory, it will supply values for company code 430 at run time:

WHERE COMPANY_CODE_N EQ '430'

All variables passed to BW have a format of 124 alpha bytes. The Master File synonym breaks variables into two parts: variable_C and variable_N.

SAP BW expects the literals in SAP VARIABLES clauses to contain only the uncompounded part of the member name. Although the Adapter for SAP BW communicates with the user in terms of member names and captions (for variables of MEMBER type), it automatically detects compounding and generates a literal based on the uncompounded part of the supplied member name. In order to obtain meaningful reports when using compounded variables the user has to be familiar with the SAP OSS Note 605208 and follow its directions. Effectively it mandates either creation of a separate variable on the compounding characteristic or a single value hard filter on it. Please see the OSS Note 605208 for further information.

In the previous example, the name (_N) supplies the variable value. You can use either the caption or the name for screening, but you must supply appropriate values for each:

company_code_n : WHERE COMPANY_CODE_N EQ 430 
company_code_c : WHERE COMPANY_CODE_N EQ 'IDES USA'

Tip: If the associated dimension of this variable is available in the query, do not build the selection on that field, but, rather, use the name. In other words, WHERE COMPANY_CODE_LEVEL_01 EQ 430 will not populate the variable, while COMPANY_CODE_N will. Additional non-variable selections are available, but they are less efficient. The non-variable conditions are applied after the BW answer set has been extracted.

Variables cannot be displayed in a request. They should be used only for applying selection criteria. You can display the dimensions or measures with which they are associated.

You cannot display a variable as a BY field. Variables can have a selection type of single, interval, or complex.

To supply a range, use two WHERE statements or combine them into one statement using AND. For example, this report needs to specify two years:

WHERE FISCAL_YEAR_N EQ '2001'
        OR FISCAL_YEAR_N EQ '2002'
WHERE FISCAL_YEAR_N GE '2001'
WHERE FISCAL_YEAR_N LE '2002'
WHERE FISCAL_YEAR_N GE '2001'
      AND FISCAL_YEAR_N LE '2002'
WHERE FISCAL_YEAR_N IN ('2001', '2002')
WHERE FISCAL_YEAR_N FROM '2001' TO '2002'

Selections that use an OR connector, like the first sample above, can only test against the same field or related hierarchy. You cannot combine variable selection and non-variable selection in the same WHERE statement (screening predicate).


Top of page

x
Full and Partial Aggregation

Reference:

Cube requests are requests that reference at least one measure and possibly more than one hierarchy. Variables may also be referenced.

For cube requests, the adapter reads cells with fully or partially aggregated data from the cube and applies certain restrictions on the contents of the TABLE request to ensure consistency of reports.

There are two modes in which cube requests are processed: Full Aggregation and Partial Aggregation.

Full aggregation mode is more efficient because no aggregation is needed beyond what is already stored in the cube.



x
Reference: Support for Full Aggregation Mode

The following table describes support for full aggregation mode:

Command

Dimension Properties

Measures

Variables

WRITE/ SUM/ADD

Supported, converted to FST.

Supported, converted to an operation that matches the measure aggregator.

Not supported.

COMPUTE

Supported.

Supported.

Not supported.

PRINT

Supported.

Supported for compatibility. Not supported with BY HIERARCHY.

Not supported.

WHERE TOTAL

Supported.

Supported.

Not supported.

WHERE

Supported.

Not supported.

Required for mandatory variables; must provide required restriction for its variable selection type. Restricted to a group of fields that represent a single variable.

WHEN

Supported.

Not supported.

Not supported.

DEFINE

Supported.

Not supported.

Not supported.

BY

Supported. Requires BY on a unique field of a parent/ child hierarchy (except when referenced only in a WHERE). BY HIERARCHY adds BY internally.

Not supported.

Not supported.

BY HIERARCHY

Supported.

Not supported.

Not supported.

ON HIERARCHY

Supported. Requires a corresponding BY or BY HIERARCHY phrase.

Not supported.

Not supported.



x
Reference: Support for Partial Aggregation Mode

In partial aggregation mode, there are additional restrictions on the use of measures. Note that in requests with multiple display commands, the partial aggregation restrictions apply to all display commands except for the ones associated with the lowest BY phrase:

The following factors turn on partial aggregation mode:


Top of page

x
Support for Time Dependent Hierarchies

The contents of a hierarchy may change over time. As changes occur, multiple versions of the hierarchy for different time periods accumulate. Results of a report from the cube depend on which of the hierarchy versions is selected at report time.

A cube can be created that has multiple time-dependent hierarchies, each having different versions for different time periods.

When you create a report against such a cube, you cannot arbitrarily choose the versions of time-dependent hierarchies because only certain combinations of versions make business sense. The way to choose versions of time-dependent hierarchies is by indicating what is called the key date. When you specify a key date you are saying you want to see the contents of the cube as of that effective date. The BW system automatically chooses the proper combination of versions of the time-dependent hierarchies valid for this key date.

Time-dependent hierarchies sometimes may also have multiple concurrent versions of the same hierarchy. The number of these versions changes with time and is reflected in the cube metadata. Depending on the key date, the BW system exposes a varying number of these concurrent versions, each as a separate distinctly named hierarchy. Thus, not only the contents of a hierarchy can change with time, but also the number of its concurrent (simultaneously existing) versions can change.

When you create a synonym against a cube with time dependent hierarchies, you can specify a key date and produce a Master File that contains all versions of the hierarchies (time dependent and not) that existed for the time period associated with the specified key date. In the synonym, different concurrent versions of the same hierarchy have different field names that let you distinguish between the versions. Specifying the key date at the time of synonym creation makes sense mostly in the presence of a time-dependent hierarchy that has or has had multiple concurrent versions. The key date should be specified for a period of time when the hierarchy had the maximum number of concurrent versions.

If you do not specify a key date when creating a synonym, the current date is used as the key date. If all the time-dependent hierarchies in the cube have only time-changing contents but do not have varying numbers of concurrent versions, specification of the key date at create synonym time makes sense only when simultaneously requesting member sampling. Otherwise, the resulting synonym will be identical no matter what key date has been specified.

When you report against a time-dependent hierarchy, you use the ENGINE BWBAPI SET KEY_DATE command to set a key date for subsequent TABLE requests. If you do not specify the key date, the current date is used as the key date. The contents of all time-dependent hierarchies in the cube will be exposed in the report as they were at the specified key date. To report against a specific concurrent version of the hierarchy, reference the field names associated with that version of the hierarchy in your request. This has to be coordinated with the key date that you specify. If you do not specify a correct key date (the one within the time period associated with the specified concurrent version of the hierarchy) your report will fail with an error message indicating absence of the specified hierarchy.



Example: Time Dependent Hierarchies in a Master File

The following portion of a Master File was produced using the key date 20081031 against the $0SD_C01 cube. Time-dependent hierarchies are defined for the 0MATERIAL dimension. The concurrent versions of the time dependent hierarchy are indicated in bold:

 DIMENSION=[0MATERIAL], CAPTION='Material', $
  HIERARCHY=[0MATERIAL], CAPTION='Material', HRY_DIMENSION=[0MATERIAL], 
HRY_STRUCTURE=STANDARD, $
  HIERARCHY=[0MATERIAL                     001], CAPTION='Material 
class', HRY_DIMENSION=[0MATERIAL], HRY_STRUCTURE=RECURSIVE, $ 
  HIERARCHY=[0MATERIAL                     MAT_CLASS_TD                  
V1], CAPTION='Material Class Time Dep', HRY_DIMENSION=[0MATERIAL], 
HRY_STRUCTURE=RECURSIVE, $
  HIERARCHY=[0MATERIAL                     MAT_CLASS_TD                  
V2], CAPTION='Material Class Time Dep', HRY_DIMENSION=[0MATERIAL], 
HRY_STRUCTURE=RECURSIVE, $ 
  HIERARCHY=[0MATERIAL                     PRDHA], CAPTION='Product 
Hierarchy for material MARA', HRY_DIMENSION=[0MATERIAL], 
HRY_STRUCTURE=RECURSIVE, $
  HIERARCHY=[0MATERIAL                     RCV], CAPTION='Retail category 
view', HRY_DIMENSION=[0MATERIAL], HRY_STRUCTURE=RECURSIVE, $

The following portion of the Master File shows the fields listed for the two concurrent versions of the time dependent hierarchy. Note that the fields listed for the first version have the characters TIME_DEP in their names, and the fields listed for the second version have the characters TIME_DEP1 in their names:

Fields for Version 1 of the time dependent hierarchy:

    FIELDNAME=MATERIAL_CLASS_TIME_DEP, USAGE=A143, ACTUAL=A143,
      MISSING=ON,
      TITLE='Material Class Time Dep Member Unique Name',
      WITHIN='*[0MATERIAL               MAT_CLASS_TD                V1]',
      PROPERTY=UID,  $
    FIELDNAME=MATERIAL_CLASS_TIME_DEP_NAME, USAGE=A64, ACTUAL=A64,
      MISSING=ON,
      TITLE='Material Class Time Dep Member Name',
      REFERENCE=MATERIAL_CLASS_TIME_DEP, PROPERTY=NAME,  $
    FIELDNAME=MATERIAL_CLASS_TIME_DEP_LVLNO, USAGE=I2L, ACTUAL=I4,
      MISSING=ON,
      TITLE='Material Class Time Dep Member Level Number',
      REFERENCE=MATERIAL_CLASS_TIME_DEP, PROPERTY=LEVEL_NUMBER,  $
    FIELDNAME=MATERIAL_CLASS_TIME_DEP_PARENT, USAGE=A143, ACTUAL=A143,
      MISSING=ON,
      TITLE='Material Class Time Dep Parent Unique Name',
      REFERENCE=MATERIAL_CLASS_TIME_DEP, PROPERTY=PARENT_OF,  $
    FIELDNAME=MATERIAL_CLASS_TIME_DEP_PARENT_LVLNO, USAGE=I2L, ACTUAL=I4,
      MISSING=ON,
      TITLE='Material Class Time Dep Parent Level',
      REFERENCE=MATERIAL_CLASS_TIME_DEP, PROPERTY=PARENT_LEVEL_NUMBER,  $
    FIELDNAME=MATERIAL_CLASS_TIME_DEP_CHILDREN_CARD, USAGE=I9, ACTUAL=I4,
      MISSING=ON,
      TITLE='Material Class Time Dep Member Children Cardinality',
      REFERENCE=MATERIAL_CLASS_TIME_DEP, PROPERTY=CHILDREN_CARDINALITY, $
    FIELDNAME=MATERIAL_CLASS_TIME_DEP_CAPTION, USAGE=A60, ACTUAL=A60,
      MISSING=ON,
      TITLE='Material Class Time Dep Member Caption',
      REFERENCE=MATERIAL_CLASS_TIME_DEP, PROPERTY=CAPTION,  $

Fields for Version 2 of the time dependent hierarchy:

    FIELDNAME=MATERIAL_CLASS_TIME_DEP1, USAGE=A143, ACTUAL=A143,
      MISSING=ON,
      TITLE='Material Class Time Dep Member Unique Name',
      WITHIN='*[0MATERIAL                MAT_CLASS_TD               V2]',
      PROPERTY=UID,  $
    FIELDNAME=MATERIAL_CLASS_TIME_DEP1_NAME, USAGE=A64, ACTUAL=A64,
      MISSING=ON,
      TITLE='Material Class Time Dep Member Name',
      REFERENCE=MATERIAL_CLASS_TIME_DEP1, PROPERTY=NAME,  $
    FIELDNAME=MATERIAL_CLASS_TIME_DEP1_LVLNO, USAGE=I2L, ACTUAL=I4,
      MISSING=ON,
      TITLE='Material Class Time Dep Member Level Number',
      REFERENCE=MATERIAL_CLASS_TIME_DEP1, PROPERTY=LEVEL_NUMBER,  $
    FIELDNAME=MATERIAL_CLASS_TIME_DEP1_PARENT, USAGE=A143, ACTUAL=A143,
      MISSING=ON,
      TITLE='Material Class Time Dep Parent Unique Name',
      REFERENCE=MATERIAL_CLASS_TIME_DEP1, PROPERTY=PARENT_OF,  $
    FIELDNAME=MATERIAL_CLASS_TIME_DEP1_PARENT_LVLNO, USAGE=I2L,ACTUAL=I4,
      MISSING=ON,
      TITLE='Material Class Time Dep Parent Level',
      REFERENCE=MATERIAL_CLASS_TIME_DEP1, PROPERTY=PARENT_LEVEL_NUMBER, $
    FIELDNAME=MATERIAL_CLASS_TIME_DEP1_CHILDREN_CARD, USAGE=I9,ACTUAL=I4,
      MISSING=ON,
      TITLE='Material Class Time Dep Member Children Cardinality',
      REFERENCE=MATERIAL_CLASS_TIME_DEP1, PROPERTY=CHILDREN_CARDINALITY,$
    FIELDNAME=MATERIAL_CLASS_TIME_DEP1_CAPTION, USAGE=A60, ACTUAL=A60,
      MISSING=ON,
      TITLE='Material Class Time Dep Member Caption',
      REFERENCE=MATERIAL_CLASS_TIME_DEP1, PROPERTY=CAPTION,  $


Example: Reporting Against Time Dependent Hierarchies

The following request reports against a synonym named TIMEDEP that was created using the key date 20081031. It lists the net value of sales volume for the first version of the time dependent 0MATERIAL hierarchy. The request displays three levels of the hierarchy and only non-empty cells:

ENGINE BWBAPI SET KEY_DATE 20081031 
TABLE FILE TIMEDEP
SUM NET_VALUE_OF_SALES_VOLUME 
BY MATERIAL_CLASS_TIME_DEP_CAPTION HIERARCHY  
SHOW TO DOWN 3
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
GRID=OFF, $
END

The output is:

The following request uses the second version of the time dependent hierarchy:

 
ENGINE BWBAPI SET KEY_DATE 20081031 
TABLE FILE TIMEDEP
SUM NET_VALUE_OF_SALES_VOLUME 
BY MATERIAL_CLASS_TIME_DEP1_CAPTION HIERARCHY  
SHOW TO DOWN 3
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
GRID=OFF, $
END

The output is:

Following is the same request against the version of the hierarchy that is not time dependent. The results will be the same for any key date when using this version of the hierarchy:

TABLE FILE TIMEDEP
SUM NET_VALUE_OF_SALES_VOLUME 
BY MATERIAL_CLASS_CAPTION HIERARCHY  
SHOW TO DOWN 3
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
GRID=OFF, $
END

The output is:


Top of page

x
Reversing the Sign When Displaying a Measure

How to:

In a BEX query, one of the options available for a measure is to display its values with reversed plus and minus signs. If you know that the BEX query has specified that a measure should be displayed with reversed signs, you can use the DMC to create a synonym that enables display of that measure on WebFOCUS reports with reversed signs. This is done by adding the REVERSE_SIGN=ON attribute to the field declaration for that measure.

The signs are reversed whenever this attribute is present, whether the BEX query specified a reverse sign or not, so you must know the data and the query definition before applying this attribute. This attribute has no effect on the stored values of the measure, just their display.



x
Syntax: How to Display a Measure With Reversed Signs

Add the following attribute to the FIELD declaration for the measure to be displayed with reverse signs:

REVERSE_SIGN=ON


Example: Displaying a Measure With Reversed Signs

In the following partial listing of the ZMINUS synonym, the measure CM_1 has the REVERSE_SIGN=ON attribute. This attribute has been added because the BEX query specifies reversed signs for measure CM_1:

FILENAME=ZMINUS, SUFFIX=BWBAPI  , $ 
  SEGMENT=ZMINUS, SEGTYPE=S0, $ 
$ MEASURES FOR CUBE 0SD_C01/ZMINUS    
  FIELDNAME=CM_1, ALIAS=4KN324NNXDL1055YDEO17L0L8, USAGE=D18.2,
    ACTUAL=D8, MISSING=ON,  TITLE='CM 1',     
      REFERENCE=SUM, PROPERTY=MEASURE, REVERSE_SIGN=ON, $  
  FIELDNAME=COST_OF_SALES____, ALIAS=4KN324VCGC6QIRPEJ8QDHMZB0,
    USAGE=D18.5, ACTUAL=D8, MISSING=ON,  TITLE='Cost of Sales (%)', 
      REFERENCE=UNKNOWN, PROPERTY=MEASURE,  $ 
 DIMENSION=[0MATERIAL], CAPTION='Material', $   
  HIERARCHY=[0MATERIAL], CAPTION='Material', HRY_DIMENSION=[0MATERIAL],
    HRY_STRUCTURE=STANDARD, $
  HIERARCHY=[0MATERIAL                   001], CAPTION='Material class', 
    HRY_DIMENSION=[0MATERIAL], HRY_STRUCTURE=RECURSIVE, $

The following request against the ZMINUS synonym prints the measures CM_1 and Cost_of_Sales:

TABLE FILE ZMINUS
PRINT CM_1 COST_OF_SALES
BY MATERIAL_NAME
END

The partial output follows. The measure CM_1 is displayed with reverse signs:

Removing the REVERSE_SIGN=ON attribute from the synonym produces the following output for the same request:


Top of page

x
Reporting Rules

Reference:

The reporting rules and features vary depending on the types of hierarchies defined in the synonym. These factors are summarized in the following sections.



x
Reference: Display Command and Prefix Operator Support

When BY HIERARCHY is not used in the request, the display command can be PRINT, even for aggregated data. It is internally converted to the appropriate command.

Note: To emphasize that the summary values are already in the cube and do not necessarily require any additional aggregation, the examples use the WRITE command, which is a synonym for SUM.

The measure operation is also automatically supplied internally. You do not have to specify a prefix operator in the request. However, if you do specify a prefix operator, it should match the measure aggregator.

For example, assume there is a measure called ASALES and another measure called SALES, where SALES is a simple sum and ASALES is an average. A request referencing SALES and ASALES can use the following display command:

WRITE SALES ASALES

The operation for the ASALES measure will automatically be converted internally to AVE.ASALES.

You can also specify the summarizing operation in the command:

WRITE SALES AVE.ASALES

In the synonym, the REFERENCE= attribute specifies the measure aggregator. For example, REFERENCE=SUM.

If you do specify prefix operators in the request, you can prevent the adapter from checking whether your operation matches the measure aggregator by issuing the following command:

ENGINE BWBABI SET CHECKAGGR OFF


x
Reference: Reporting Rules for All Hierarchies

Variables. SAP BW expects a query to contain values for variables. Values for all mandatory variables must be supplied. The method for specifying variable values is to include a WHERE or IF phrase in the TABLE request.

For example, the following WHERE phrase supplies the value 430 for the COMPANY_CODE variable:

WHERE COMPANY_CODE_N EQ '430'

BY. Lexicographic sort is available on any of the member properties as well as expressions (DEFINEs) based on properties, but the request must include a sort (possibly with the NOPRINT option) on a property that is guaranteed to uniquely identify a hierarchy member, either PROPERTY=UID or PROPERTY=NAME. BY HIERARCHY automatically adds a BY one of these properties. In a report referencing a level hierarchy, the request must specify one.

Hierarchies. Only one hierarchy from the same dimension can be represented on the report.

Cardinality. Cardinality is the number of members in a dimension, hierarchy, or hierarchy level. Because SAP BW is a multi-dimensional database, the volume of the report may grow as a product of cardinalities of the referenced dimensions unless you include a WHERE test to restrict the number of returned dimension members

Measures. Filtering on totals is done using WHERE TOTAL tests. Calculations on totals are done using COMPUTE.

Screening. Screening on member properties is available with and without sorting.



x
Reference: Reporting Rules for Parent/Child Hierarchies

BY HIERARCHY. Requires the command SUM, WRITE, or ADD. PRINT is not supported.

Hierarchical Context. The set of members selected for the report using a WHEN test is augmented so that every member of the hierarchy is shown within its hierarchical context. The displayed subtree of hierarchy members is a contiguous tree whose root is the root of the whole hierarchy. The added context members are shown without totals.

Report Compression. By default, hierarchy members with no fact data do not participate in the report. Hierarchy members for which no data passes screening do not participate in the report. In a multi-hierarchy report, the inner hierarchies are not shown for those members of outer hierarchies that appear on the report output only to show the context of selected members.

Full and Visual Totals. Visual totals for each node are composed from the totals of its hierarchical children shown on the same report. These are compatible with the totals shown for the same members in a multi-verb level-wise report. Visual totals are the default. The report can specify full totals by specifying the prefix operator FROLL on a measure field. Full totals are accumulated for each displayed hierarchy member individually. These are compatible with the totals shown for the same members in a non-hierarchical report on a parent-child hierarchy.


Top of page

x
General Tips for Reporting

When creating a report request:

For example, if the hierarchy for a dimension is

Country > State_Province > City

sorting by City first, then State_Province results in an inefficient request. Also, because of the hierarchical structure of OLAP, you retrieve only one BY field for each SUM or PRINT field in the report.


Top of page

x
SAP BW Support for Hierarchies

Reference:

In SAP BW release 3.0A and earlier, one characteristic of the query can be expanded in a hierarchical display.



x
Reference: Creating Dynamic Hierarchies

The Adapter for SAP BW generates each level of a hierarchy with a field name corresponding to that level. For a dimension called Business Number and a hierarchy named States, level 1 will be States and level 2 will be the Business Number. You can create a report referencing all the fields of a hierarchy at once, or create it as an OLAP trigger report, referencing the top level of the hierarchy and enabling interactive drill down.

In SAP BW 3.0B and higher, multiple characteristics of the query can be expanded in a hierarchical display.


Top of page

x
Creating Dynamic Dimensions

SAP BW, you can:



Example: Creating a Dimension

The following request creates a dimension called Daily Sales, which concatenates the company name, the current date (picked up from an the &DATE variable), and the Sales figures for that date.

DEFINE Daily Sales/A124= company|&date|'Sales'
END
TABLE FILE MYQUERY
SUM Retail_Amount
BY Daily Sales
END

The output is:

Daily Sales                        Retail_Amount
ACME   11/15/2002                         10,000
SMCE   11/15/2002                         15,000

iWay Software