Recursive hierarchy grouping by string delimiting in SSRS 2016?

Question

I have a SQL Server database as my data source for which my rows contain a path delimited by \

For example:

"\Foo\Bar"

is a folder containing

"\Foo\Bar\Baz".

My database contains a column storing an absolute path, and I would like to have a recursive hierarchy table based on ownership of folders to child folders.

My data is stored like this:

+--------------+
| PATH         |
+--------------+
| \Foo         |
+--------------+
| \Foo\Bar     |
+--------------+
| \Foo\Bar\Baz |
+--------------+
| \Foo\Bar\Qux |
+--------------+
| \Foo2        |
+--------------+

and I would like to represent it like this

+----------+
| PATH     |
+----------+
| \Foo     |
+----------+
|   \Bar   |
+----------+
|     \Baz |
+----------+
|     \Qux |
+----------+
| \Foo2    |
+----------+

I have already written some helpful CLR stored procedures for this process:

One allow me to get the children of a given parent path (e.g. passing in "\Foo\Bar" returns rows "\Foo\Bar\Baz" and "\Foo\Bar\Qux")

Another allows me to extract the bottom-most level name of a path (e.g. extracting "\Baz" from "\Foo\Bar\Baz").

Is it possible to have a table generated in this fashion using the SSRS Recursive Hierarchy Grouping Structure with the way my data is being stored? Or will this task require a modification to my database schema?

Answer 1

To be able to use the recursive grouping feature, you will need a parent-child hierarchy that can be used to join a parent with it's children. For example, you can use a query like this, based on your table, that will retrieve also the last folder name and the path to the parent Folder (flen is the length of the folder name):

SELECT [PATH]
  , RIGHT([PATH], p.flen) AS FOLDER
  , LEFT([PATH], LEN([PATH]) - p.flen) AS [PARENT]
FROM FolderTable
  CROSS APPLY (VALUES(CHARINDEX('\',REVERSE([PATH])))) p (flen);

The data returned will look like this:

You can then create a report, add the Data Source and a Dataset based on the query above. You can then add a table to the report, it only needs to have one column. Display the field FOLDER of the Dataset in that column.

Done so, change the properties of the Details group (under Row Groups):

• Under General properties, add the PATH field as the Group expression
• Under Advanced properties, add the PARENT field as the Recursive parent

If we look at the result, it doesn't look nicely yet:

To display the children a little bit indented, we can use the Level function in an expression for the Padding Left property of the FOLDER TextBox (taken from an example by Microsoft):

=CStr(2 + (Level()*10)) + "pt"

Now, I guess we arrived where we wanted to go: