How to create Hierarchy Node Variable prompts using BEx Query Designer
Tags:
1. Open BEx Query Designer
2. Select the OLAP cube (e.g. SAP DEMO Sales and Distribution: details) and select open
3. On the right hand side of the Query Designer, under Advance Tab check the option "Allow External Access of this Query
4. From InfoProvider, choose a dimension on which we want to create a Filter/Restrict values (i.e. Select Company Code in the filter section)
5. Right click on the Company Code and select Restrict
6. This window will open, click the drop down for Show and select Variables.Select Type as Hierarchy Node Variable
7. At the bottom of the window you will see Variable Hierarchy, select the square button
8. Select Hierarchy Variables or if you don’t want user to select the Hierarchy values, then select the Hierarchy Name from the drop down
9. If you create Hierarchy Variables, then select the square button to create a Hierarchy Variable prompt
10. Create a new Hierarchy Variable by clicking on New
11. Type in the name and technical name of the variable
12. Go to Details tab and choose the option "Variable is Mandatory"
13. Select the Default values tab if you wish to show a default hierarchy every time the query is run
14. The variable will be saved with the name provided in the above window in step11
15. The name of the Hierarchy variable will be shown in variable Hierarchy section. Here Hierarchy variable name is KBA_COUNTRY_HIER
16. Now we need to create a Hierarchy Node Variable. Just next to Type drop down, click on New to create a variable
17. Type in the Description and Technical Name for the Hierarchy Node Variable in the general tab
18. Specify if you want this prompt to be Optional or Mandatory in "Variable is" by specifying in Details tab and click OK
19. This is the name of the Hierarchy Node Variable and click on OK
20. Select the Hierarchy Node Variable KBA_COUNTRY_NODE to the right side pane under Chosen Selection click on OK
21. In Rows/Columns section drag Company Code (SAP DEMO) (Dimension) in Rows section and Open Order (Key Figures) in Columns section and save the query