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.
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.
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.
Scroll down...
For the Lookup attribute, click the option button to open the Lookup Attribute dialog.
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.
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.
Click the OK button to save.
Next, go to the Validation Method attribute. Click the option button to open the Validation Method Attribute dialog.
Select Hierarchy from the Method dropdown menu.
Click OK to save.
That's it!
Verification
Now, let's verify it.
Save the data model.
Run the Query Builder (you can also do it from the main application menu
).
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.
Click the search button for the "Group Hierarchy" you've set in the filter area.
The search window opens, and you can see the hierarchical structure you set earlier.
Select <ALL> and close it.
In the selection area at the bottom, right-click and select Output All Fields to set all fields to the output area.
Try executing the query using the Preview button.
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.
Click OK to confirm.
Try running the query again with the Preview button. You will see data for Essex and Lancashire.
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!