Tell us what you think

We want to create the best possible experience for our learners. Your feedback helps us to improve the experience for everyone.

Creating Structures

Objective

After completing this lesson, you will be able to create a query with two structures

Structures in Queries

Structures are combinations of InfoProvider key figures and characteristics. A structure has multiple purposes. It can be used to form the basic framework of the rows or columns for a query result. Structures provide precise control of where key figures and characteristic appear in a result. A structure can also be defined as reusable so it provides a ready-made layout that benefits future query developers who need to the same layout.

Structures are built from selections and formulas.

Key Figure Structures

In the BW Query Designer in SAP HANA Studio, a Key Figure Structure appears automatically when you move a key figure from the InfoProvider section to the Rows or Columns sections.

Components are basic key figures, restricted key figures, and calculated key figures or formulas.
Key figures in rows or columns are always components of a key figure structure.
Each component of a key figure structure must contain a key figure.
You can use only one key figure structure in a query.

Characteristic Structures

You also can define Characteristic Structures: The structural elements of a characteristic structure cannot contain any key figure selections.

Use one or more characteristics and filtering to define the selections of the components of the structure - for example, single values, ranges, or hierarchy nodes.
Use variables for flexible selections.
The components of a characteristic structure cannot contain key figure selections, but they can contain formulas.

The difference between a characteristic structure and the use of characteristics on an axis is as follows:

Characteristic structure: You specify the number, sequence, and the content of structure elements in the query definition.
Characteristics on an axis: All selected characteristic values are displayed in the report.

Reusable Structures

You can save structures so that they are reusable and they can be used in other queries for the same InfoProvider. The structure then belongs to the InfoProvider and is known as an InfoProvider-level structure.

Changes made later to InfoProvider-level structures immediately affect all queries in which they are used.

Quite often a reusable structure needs adjustments for a specific query. However, if you change the reusable structure then all queries that use that structure will be affected. If you want to adjust the structure for a specific query but do not want to affect the reusable structure, you can convert the reusable structure that you have added to your query, into a query-level structure. This is done by right-clicking the reusable structure that you have added to your query to open the context menu and choosing Dereference. You can then make changes to the structure in your query without affecting the reusable structure.

Queries with Two Structures

A query can have a maximum of two structures. If you are using two structures, for example, a key figure structure in the columns and a characteristic structure in the rows, a table with fixed-cell definitions is created.

More Details about Queries with Two Structures

You can include only one key figure structure in a query.
You can define one structure in the columns and one structure in the rows, or use both structures in the columns or in the rows.
You can combine structures freely with other characteristics in the rows and columns.
You can provide your own custom descriptions for rows or columns that are represented by members of a structure. For example, you could define the first row of a structure using a selection of values from the characteristic 0PERSON, then name the row of the structure, My Team. The next row in the structure might include the remaining values from the characteristic 0PERSON and you might name this row, Employees not in my team.
You can define formulas in either structure. However, ensure that you avoid formula collisions. This is where formulas defined in a row and also a column collide at the intersecting cell.
If you cannot avoid formula collision, you can resolve it if the default behavior leads to incorrect results (see lesson Resolving Formula Collision).

How to Create a Query with a Characteristic Structure and a Key Figure Structure

Launch the following demonstration to learn about different types of structures.

DemoStart Demo

How to Work with Reusable Structures within a Query with two Characteristic Structures

In the following demonstration, you explore how to work with reusable structures and to build a business scenario using two characteristic structures.

ExerciseStart Exercise

Defining Priorities for Structure Elements

If you use two structures in a query, the system takes into account the properties, which have been specified for the structures or for specific cells.
The system takes the properties into account according to the order in which the properties are specified.
The last specification made has priority.
On the Priorities tab, you can view and modify the priority handling by explicitly specifying which property is actually taken into account.
This is a power user function.

For the Select Property field, the system displays a list of properties, for which you can set priorities. These are mostly display properties, which you can find in the definition of a structure element (selection or formula) or a cell on the General tab.

Here, you see the two structures of the query with their structure elements in the grid. If the properties of a structure element have not been changed in the query definition, the system displays the default values configured on the InfoObject maintenance screen.

However, if a property of a structure element in the query definition has been explicitly changed, the system displays the changed value.

The following figure shows an example of an explicitly changed property of a structure element included in the Columns and of explicitly defined Cell settings .

By using Apply Row Value, the property setting of the structure element included in the Rows will take place. This property setting will also override explicitly defined Cell settings when Keep Explicit Cell Values is not set.

Symbols and Meanings

Symbol	Meaning
	The value in the column structure matches the value in the row structure.
	The structure element inherits the property from the structure in the row.
	The structure element inherits the property from the structure in the column.
	Symbol for the cell definition: An explicitly set cell value has priority. Note that the Keep Explicit Cell Value field is selected by default, to save values in the event of any changes. This means that explicitly set cell values are retained, even if the default value of the row structure or column structure is to be generally applied. If you have changed an explicitly set cell value, the system adds the expression (Cell) to the new value.

Next lesson