How To Create Kentico Hierarchical Transformations

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. Check this box!

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. Fill the columns!

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. Create new 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. Adding a transformation! Parent transformation setup Child Transformation setup!

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.

Troubleshooting

  1. 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.

  2. In your query, the parent column of the top-level must all be the same. It can be NULL or a specified number. If it is not the same your transformation will show the data for the first top-level element.

  3. 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)

  4. 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)

  5. Make sure that the ClassName field is included in the query, and that the value matches the DocumentTypes field in the hierarchical transformation for that level. (Added 3-13-2015)

Conclusion

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.

Written on May 16th, 2014 by Dan Walker

Want to read more?

If you’ve enjoyed what you’ve seen so far, you might like some of my other posts. Visit the archive to see all of my past writings.

Blog Archive »
 

About the Author

Dan Walker is a programmer from Grand Rapids, MI. He works at Gordon Food Service.

More About Dan »

Get in touch