How to Use Hierarchy in Bex Query Designer
and last updated on Posted on .
Welcome 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:
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.
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.
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.
The following dialog box will appear.
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.
The properties pane will now display that the Active Hierarchy Display checkbox gets checked.
You can also see that the characteristic icon gets changed. Screenshot is given below.
Now, click on "Save" button and the report gets saved.
Now, execute the query using BEx Analyzer. Select values for variables and execute the query using the 'OK' button.
The report output is given below.
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.
The following is the 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.
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.
The following is the 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.
The following is the 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:
The following screen appears:
Now, go to 'Hierarchy' tab and the following screen appears:
Now, deactivate the hierarchy by clicking on the checkbox highlighted in the above screen and click on "OK" to proceed further:
Below is shown the report output. You can see that there is no hierarchy on 'Ship-To Party' field.
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:
It can be seen from the above screen that a hierarchy has been formed. Now, click on "Save" button and the report gets saved.
Now, execute the query using BEx Analyzer. Select values for variables and execute the query using the 'OK' button.
The report output is given below:
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