【DataModel】Setting up the Hierarchy Lookup Dialog 

This time, I will introduce a method for displaying data hierarchically in the Query Builder's filter lookup.

### Preparing a Hierarchical Data Set ###
Suppose you have a field called `Group` in a table, and the data has a hierarchical structure. For example, an address would be a good example.
Example: Planet/North America/USA ...
You can process such data into a dataset in the following format using SELECT statements or other methods. You can create a view for this or even create a new table.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/96a148c1-60ba-1d35-f534-bacc7f8ec259.png)
Since the base data is typically dynamic data, a view would be a good choice. Once the view is created, define a new view in the target data model and make it accessible to the Sharperlight engine.

### Setting Hierarchy for Filter Attributes ###
Launch the **Studio** from the Sharperlight application menu and open the target data model.
Next, refer to the table definition that has the field for which you want to set up hierarchical search, namely the `Group` field.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/112eaad4-2932-1ada-2b25-d97c3de2862d.png)
As you'd like to keep the original `Group` field as it is, first create a clone of it. Select the `Group` field, right-click, and choose `Copy`. Then right-click again and select `Paste` to insert the cloned field.
Select the cloned field and display the list of attributes on the right side, and update them.
Enter `GroupHierarchy` for the Code attribute.
Enter `Group Hierarchy` for the Description attribute.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/28fecc0f-1de3-c509-1da0-6130b137f1d6.png)
Scroll down...
For the Lookup attribute, click the option button to open the Lookup Attribute dialog.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/edf8c737-ecfa-034e-4934-86ca88cdff07.png)
Select `Hierarchy` for the Type.
Click the `Select The Hierarchy Table` button and specify the view or table you prepared earlier, which returns the hierarchical data set.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3050775f-1fe7-6d63-b6f3-7be80516c1ca.png)
The field list is displayed in the selection area. Move the fields to the right side in the order you choose, starting with the field that has the hierarchy path, followed by a unique ID or code, a field representing the level of the hierarchy, and then summary fields in order.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1865f401-2fd3-83ce-a8c7-0548c0d82834.png)
Click the OK button to save.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4f681a12-1bba-c5b6-2f81-78c925843aa2.png)
Next, go to the `Validation Method` attribute. Click the option button to open the Validation Method Attribute dialog.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/53a95851-c04a-7560-3e3a-0c6a0dc03539.png)
Select `Hierarchy` from the Method dropdown menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2558f641-8078-78c3-3571-50db3f4cea74.png)
Click OK to save.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8fbeea1a-9be4-06aa-092a-1201f4ee6246.png)
That's it!

### Verification ###
Now, let's verify it.
Save the data model.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5756315d-0b56-17d1-7d53-0ded077be78d.png)
Run the Query Builder (you can also do it from the main application menu:thumbsup_tone1:).
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0878f3b3-5ef7-ec85-9d50-9175999940e2.png)
The Query Builder will launch, and select the data model you've made changes to, which is the target data model for this session.
Select the table you updated in the table section.
You can see the `Group Hierarchy` field that you added earlier in the selection area at the bottom.
Drag and drop it into the filter area.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/41c80787-7486-5a35-4de1-c72894875ef5.png)
Click the search button for the "Group Hierarchy" you've set in the filter area.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/42cba43f-7a64-81a0-229d-7f9c1e57261a.png)
The search window opens, and you can see the hierarchical structure you set earlier.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d7ba325e-6278-5f37-c8b8-7c26b88da7a9.png)
Select `<ALL>` and close it.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d0bde727-676e-d269-4373-1ff7479970b1.png)
In the selection area at the bottom, right-click and select `Output All Fields` to set all fields to the output area.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/47a16850-47d1-fb49-c6bc-63946f1d9057.png)
Try executing the query using the `Preview` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/55b83e6e-7a19-66bf-28d2-6a09ac94d99f.png)
You will get all the data.
Click the `Back to Query` button to go back to the query definition.
Now, select a specific value with the `Group Hierarchy` filter and check if the hierarchical search is working correctly.
Uncheck `<ALL>` and check `England`. You will see the hierarchical path `\Planet\Eurasia\Europe\England\%` in the text field above.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/93b612f4-7615-bdae-095c-beaf531a165e.png)
Click OK to confirm.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/98e15e88-ac17-f2df-5f4b-74a03a09c7ec.png)
Try running the query again with the `Preview` button. You will see data for Essex and Lancashire.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1a0bb127-e863-767c-69aa-d184105c3f03.png)
The SQL SELECT statement dynamically created by the Sharperlight engine looks like this:
```
SELECT
J001.[ID]
,J001.[Group]
,J001.[Group]
,J001.[GroupDescription]
,J001.[Amount]
FROM
[dbo].[SampleTrasaction] J001
WHERE J001.[Group] IN (
    SELECT
        J001.[UniqueID]
    FROM
        [dbo].[SampleHierachy] J001
    WHERE
        J001.[Path] LIKE N'\Planet\Eurasia\Europe\England\%'
    )
ORDER BY
1,2,3
```
How was it? Please try it out and see for yourself!