How to Use Hierarchy in Bex Query Designer

and last updated on Posted on .

SAP BW HierarchyWelcome to the tutorial about SAP BW Hierarchy. This tutorial is part of SAP BW course. After completing this tutorial, you will learn basics of BW Hierarchies, how to use them in queries, and together with key figures.

SAP BW Hierarchy is one of the most crucial reporting objects in SAP BW. Hence, they are widely used. If this is the first time you're trying to use SAP BW hierarchies, then this is the right tutorial for you. This tutorial explains the basics of SAP BW hierarchies and the usage of hierarchies in BEx Query Designer. This tutorial also shows how end users can turn off SAP BW hierarchies. As this topic is very confusing, this tutorial elucidates the steps involved in this process.

For the sake of simplicity, this tutorial is divided into the following four sections:

  • Basics of SAP BW Hierarchy
  • Adding Hierarchies to Characteristics in BEx Queries
  • Turning off BW Hierarchies
  • Structuring Key Figures to form Hierarchies

Basics of SAP BW Hierarchy

SAP BW hierarchy specifies a parent-child relationship which contains numerous nodes and leaves. A node can be assigned as top-level node. There can be only one top level node, which is also called as root. A hierarchy level consists of all nodes at the same level. Hierarchies in SAP BW have the following properties:

  • They are created for characteristics that comprise master data. For instance, Material (0MATERIAL) and Division (0DIVISION) are characteristics that can have hierarchies.
  • They are stored in master data tables.
  • You can define many hierarchies on a single characteristic.
  • A hierarchy can have up to 98 levels and not more than that.
  • Hierarchies can be sourced from SAP ERP system or from flat files.

SAP BW Hierarchies are primarily used for 2 purposes:

  • The first purpose is to have a tree display in a presentation hierarchy.
  • The second purpose is in the variable selection, where users get an option to select the characteristic values as hierarchy nodes.

Make sure that you create hierarchies only for those characteristics that do not reference other characteristics.

Adding SAP BW Hierarchies to Characteristics in BEx Queries

You can structure your queries by adding hierarchies. You can attach a hierarchy to characteristics. As part of this tutorial, let's take "ZSD_DEL_Q001": Deliveries Overview Report (see the previous tutorial for more information about this report). Make sure that you open this query in Query Designer so that we can add hierarchies. The Query looks as follows:

Query: Query Designer
Query: Query Designer

In the Query Designer, let us add SAP BW hierarchy to the 'Ship-To Party' characteristic. To perform this action, just click on the characteristic and the system opens the 'Properties' pane for you. Screenshot is given below.

Properties of Ship-To Party Characteristic
Properties of Ship-To Party Characteristic

Now, go to 'Hierarchy' tab. This tab contains multiple options on selecting a hierarchy and expanding to a level, etc. The screenshot is given below.

'Hierarchy' tab of Ship-To Party Characteristic
'Hierarchy' tab of Ship-To Party Characteristic

The above screenshot shows that there are currently no active hierarchies. As our objective is to add a hierarchy to 'Ship-To Party' characteristic, let's click on the button highlighted below to select the hierarchy.

Selecting the Hierarchy
Selecting the Hierarchy

The following dialog box will appear.

Selecting SAP BW Hierarchy
Selecting SAP BW Hierarchy

A hierarchy can be selected by clicking on the drop-down menu, which basically displays all active hierarchies that are available. Let us now choose the hierarchy name from the drop down, and click "OK" to proceed further.

Selecting SAP BW Hierarchy
Selecting SAP BW Hierarchy

The properties pane will now display that the Active Hierarchy Display checkbox gets checked.

Selecting SAP BW Hierarchy
Selecting SAP BW Hierarchy

You can also see that the characteristic icon gets changed. Screenshot is given below.

Selecting SAP BW Hierarchy
Selecting SAP BW Hierarchy

Now, click on "Save" button and the report gets saved.

Saving the Query
Saving the Query
Saved Query
Saved Query

Now, execute the query using BEx Analyzer. Select values for variables and execute the query using the 'OK' button.

Select Values for Variables in BEx Analyzer
Select Values for Variables in BEx Analyzer

The report output is given below.

Report Output
Report Output

The above output shows that the 'Ship-To Party' field is now structured around the 'US Data Generation Customers' hierarchy. You can further drill down by expanding the levels through the 'Triangle' icon highlighted in the below screen.

Expanding Hierarchy Levels
Expanding Hierarchy Levels

The following is the report output:

Report Output
Report Output

If you want to navigate to the lowest level, it might be time consuming to click manually. An alternative way to do this efficiently is to just right click on the level and navigate to the lowest level through the menu options. Right clicking on the level displays the following screen.

Expanding Hierarchy Levels
Expanding Hierarchy Levels

Now, select 'Not Complete' and it shows up the following sub-menu. Let's select the lowest level to expand the hierarchy all the way down.

Expanding Hierarchy Levels
Expanding Hierarchy Levels

The following is the report output:

Report Output
Report Output

You can observe from the above screen that each level of the hierarchy displays a subtotal of lower levels.

Turning off SAP BW Hierarchies

End-users can turn off SAP BW hierarchy as per his/her need. For instance, as a designer of the query, you made the hierarchy active for 'Ship-To Party' characteristic.

Now, execute the query using BEx Analyzer. Select values for variables and execute the query using the 'OK' button.

Select Values for Variables in BEx Analyzer
Select Values for Variables in BEx Analyzer

The following is the report output:

Report Output
Report Output

Let us assume that end-users of this report don't want to see this hierarchy. To turn off the hierarchy, you can go to the properties of the 'Ship-To Party' field and make the necessary changes in the settings. Right click on 'Ship-To Party' field available in Table and click on "Properties…" highlighted in the below screenshot:

Query Properties
Query Properties

The following screen appears:

Properties of Ship-To Party Characteristic
Properties of Ship-To Party Characteristic

Now, go to 'Hierarchy' tab and the following screen appears:

Properties of Ship-To Party Characteristic
Properties of Ship-To Party Characteristic

Now, deactivate the hierarchy by clicking on the checkbox highlighted in the above screen and click on "OK" to proceed further:

Properties of Ship-To Party Characteristic
Properties of Ship-To Party Characteristic

Below is shown the report output. You can see that there is no hierarchy on 'Ship-To Party' field.

Report Output
Report Output

Structuring Key Figures to Form SAP BW Hierarchy

You can structure key figures to form a hierarchy. Let us breakdown the 'Volume delivered' field by 'Delivery net volume' field. In order to achieve this, you have to first drag 'Delivery net volume' onto 'Volume delivered' field. The following screen appears:

Structuring Key Figures to Form a Hierarchy
Structuring Key Figures to Form a Hierarchy

It can be seen from the above screen that a hierarchy has been formed. Now, click on "Save" button and the report gets saved.

Saving the Query
Saving the Query
Saved Query
Saved Query

Now, execute the query using BEx Analyzer. Select values for variables and execute the query using the 'OK' button.

Select Values for Variables in BEx Analyzer
Select Values for Variables in BEx Analyzer

The report output is given below:

Report Output
Report Output

It can be seen from the above screen that 'Volume delivered' along with 'Delivery net volume' have been shown in the report output. This is very helpful, if you have multiple key figures which breakdown the main key figure further.

You can unlock information in SAP BW by using hierarchies. Hence, it is of utmost importance to become familiar with the purpose and functionalities of Hierarchies.

Did you like this tutorial? Have any questions or comments? We would love to hear your feedback in the comments section below. It'd be a big help for us, and hopefully it's something we can address for you in improvement of our free SAP BW tutorials.

How to Use Hierarchy in Bex Query Designer

Source: https://erproof.com/bi/sap-bw-training/sap-bw-hierarchy/

0 Response to "How to Use Hierarchy in Bex Query Designer"

Post a Comment

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel