A few posts ago, I showed you how to create a nice hierarchical listing of all your SSRS reports within SSRS. While the hierarchical listing is nice, I think it’s even better to have each folder in a separate column (which makes it really easy to dump to Excel and then filter by column).
I’ll show you how to do it in this post.
At the end of the post, I’ve included a link to download the source code. Also note, I’ve created a function in this example, so you’ll need to be able to create the function on your database.
Let’s start with our basic query. Again, I’m selecting only report objects from the catalog table:
Now that we have the basic query, we want to parse the path into its individual pieces. We use the CHARINDEX function to find the characters in the path string and separate them into pieces. The code looks like this:
Basically, this loops through the path string, looking for / marks and placing each folder into a different variable.
I’ve set this one up to work for nine levels as the number of columns returned from a function must be fixed. This was certainly one of those times I wanted to clear an array. But I digress.
So, we have our query and function. We can then combine them into one query:
And the result has each folder in a separate column:
Once you have your query working, you can easily paste it into your SSRS report.
Rather than cut and paste the values used above, you can download the source code here.
* * *
For more of this kind of content, sign up for our newsletter.