May 16th, 2014
Hierarchical transformations are used to display grouped data within Kentico. This makes them a very powerful feature in Kentico, but they are not always straightforward to set up, and can cause a lot of headaches over something that feels like it should just work. I use them just often enough to forget the details of how to set them up, and troubleshooting a hierarchical transformation can be a time sink if you don’t know what you’re looking for. This post should help guide new and seasoned Kentico developers through the basics of creating a hierarchical transformation.
Writing the SQL Query
To display hierarchical data, first you need some data to load. We’ll need to get every level of data you want to display in one query. Below is a minimal query you can use to load data for a two-level hierarchical transformation. In addition to the basic framework below, you can add any extra fields you need to the query.
SELECT (G.ItemID + 10000) AS ObjectID , NULL AS ParentObjectID , 0 AS NodeLevel , G.ItemID AS DocumentID , NULL AS NodeID , G.CategoryName AS Name , 'CODENAME.ParentGroup' AS ClassName , 'CODENAME.ParentGroup'AS NodeAliasPath , G.ItemOrder AS ItemOrder FROM CODENAME_ParentGroup G INNER JOIN CODENAME_ChildItem C ON G.ItemID = C.ParentCategory UNION SELECT (ItemID + 1000) AS ObjectID , (ItemID + 10000) AS ParentObjectID , 1 AS NodeLevel , ItemID AS DocumentID , NULL AS NodeID , CategoryName AS Name ,'CODENAME.ChildItem' AS ClassName ,'CODENAME.ChildItem' AS NodeAliasPath , ItemOrder FROM CODENAME_ChildItem ORDER BY ##ORDERBY##
Creating & Configuring The Web Part
To display the data from our query, we are going to use the Universal Viewer with Custom Query. There are other web parts that can display hierarchical data, but this web part allows us to use the query that we have already written instead of taking it from the tree or a data source.
Once you create the web part, the first thing you will want to do is scroll down to the extended settings and make sure you check
Load Hierarchical Data. Without this box checked, no data will be displayed by the transformation. This is an easy step to overlook, and has burned me more than once.
Once the web part is set up to load the data properly, we need to make sure it is looking in the right columns. Scroll down to the hierarchical settings and enter the proper column names from your query, as shown in the screenshot below.
Setting up the Transformation
Instead of scrolling down to the regular transformation boxes, use the section above it to create a hierarchical transformation. For this example, we only have two levels of items, so we will need to make two item transformations within this hierarchical transformation.
The key settings to remember are that each level you want to display within your group needs a transformation. The following screenshots will show you how to properly set up the transformations for your parent and child objects. Setting up the transformation is very similar to the parent - the biggest difference to note is the level it will be displayed for, and of course the different transformation.
If you notice that only one level is displaying in your results, make sure that you have the Node Level and Document Type fields properly set for each of the levels you want to display. If one of these is wrong for any level, that level will not be shown.
Make sure to check Load Hierarchical Data setting inside the Universal Viewer. Without this setting, the universal viewer will not use the hierarchical transformation you set up and no data will display.
In your query, the parent column of the top-level must all be the same. It can be
NULLor a specified number. If it is not the same your transformation will show the data for the first top-level element.
Start your NodeLevel at 0. I’ve had issues where the top level wouldn’t show when I was using 1 as the highest level. The universal viewer seems to reset the top level node to a 0 based level on the backend, so your transformation will need to start at level 0 regardless of what is in your query. (Added 7-17-2014)
You must include the
##ORDERBY##macro in your query. Kentico will automatically put the Node Level column into the query, and the page will throw an exception without it. (Added 11-11-2014)
Make sure that the
ClassNamefield is included in the query, and that the value matches the
DocumentTypesfield in the hierarchical transformation for that level. (Added 3-13-2015)
The hierarchical transformation gives you more control over the display of your data than a regular transformation. You can use them to create a mega menu, set up a categorized list of data, or many other uses. Once you get started using the hierarchical transformations, you will find they become another extremely useful tool in your Kentico arsenal.