How to create Hierarchy Node Variable prompts using BEx Query Designer

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