Tuesday, December 3, 2013

SSRS Recursive Parent

Overview

In this article I'm going to explain how the 'Recursive Parent' grouping works in SSRS.  This grouping feature was very tricky for me to grasp (mostly due to the lack of documentation and examples available), so my hope is that I can help lower the learning curve for others.

Background

I explored the 'Recursive Parent' option in a recent assignment to create a management report for our SSRS instance.  We wanted to see the entire reporting structure and be able to see key statistics for each report. In this case, we were using SSRS 2012 integrated in SharePoint 2010.  We only had one SSRS instance on one SharePoint server.  

The most difficult part of the assignment was structuring the data so that the report could reflect each folder and its contents.  The structure was certainly hierarchical, but with unknown depth.  Some reports were several layers deep, and others were only 1 or 2.  This type of hierarchy is called a jagged hierarchy.  The 'Recursive Parent' property makes reporting on jagged hierarchies possible.

Getting Started

To begin, I knew that I needed a function in my database to help me split up the report path into its various folders and parts.  I created a function to help me do that.  The function takes a report path, and report name, and returns a table with each row representing the folder structure to get to that report.  The code for my function is below. (It is probably easier to read the code if you copy and paste it into Management Studio or a text editor)

CREATE FUNCTION [dbo].[fnReportPathBreakDown]
(
@reportPath nvarchar(425)
,@reportName nvarchar(425)
)
RETURNS 
@Results TABLE 
(
FolderLevel tinyint
,FolderName nvarchar(100)
,RelativePath nvarchar(425)
,ReportLevelFlag bit
,ParentFolderPath nvarchar(425)
)
AS
BEGIN

declare @workingPath nvarchar(425)
declare @currentIndex int = 0
declare @nextIndex int = 0
declare @currentFolderLevel tinyint = 0
declare @maxLoop int = 1
declare @parentFolderPath nvarchar(425) = ''
declare @currentFolderName nvarchar(425)
set @workingPath = substring(@reportPath,0,charindex(@reportName,@reportPath))--strip off the report portion of the report

if CHARINDEX('/',@workingPath,@currentIndex) = 1
begin
set @workingPath = substring(@workingPath,2,len(@workingPath) - 1) --strip off leading slashes
end

while CHARINDEX('/',@workingPath,@currentIndex) > 0 and @maxLoop < 100--loop through the path and populate our table with the folder levels
begin
set @nextIndex = CHARINDEX('/',@workingPath,@currentIndex) --location of the next slash in the string (should be the end point of the folder name we want)
select @currentFolderName = substring(@workingPath,@currentIndex,@nextIndex - @currentIndex)


insert into @Results (FolderLevel,FolderName,RelativePath,ReportLevelFlag,ParentFolderPath)
select @currentFolderLevel
,@currentFolderName
,substring(@workingPath,0,@nextIndex)
,0
,@parentFolderPath

set @currentFolderLevel = @currentFolderLevel + 1 --advance currentFolderLevel
set @currentIndex = @nextIndex + 1--advance the currentIndex
set @maxLoop = @maxLoop + 1
set @parentFolderPath = substring(@workingPath,0,@nextIndex) --set parent to current relative path

end

declare @rootFolder varchar(50) --the root folder is a GUID representing the SharePoint server
select @rootFolder = FolderName
from @Results
where FolderLevel = 0

update @Results
set RelativePath = Replace(RelativePath,@rootFolder,'//') --replace the root folder GUID with a leading // for better readablity
,ParentFolderPath =  Replace(ParentFolderPath,@rootFolder,'//')

update @Results
set ReportLevelFlag = 1
where FolderLevel = @currentFolderLevel - 1 --the last folder level has the reports

return
END

Passing a path and report name into the function as follows

select * 
from dbo.BAF__ReportPathBreakDown2('/{759a08db-dcfd-4ef5-b7e9-e654ede733fd}/test/BITesting/Documents/Report1.rdl','Report1.rdl')

returns the following result set:










The function breaks each folder into its own row with the specific folder level, the name of the folder, its
relative path, a flag indicating if we are at the level where the report is located, and the parent folder path.  Structuring the data vertically like this is key to making the report work the way we want it to.  Keeping the data vertically allows us to deal with the jagged hierarchy because some reports will return 4 folder levels, others 2, 6, 12...etc.  This structure keeps things flexible so as folders are nested in folders inside the SSRS instance, it doesn't break the report, and we capture the information we need.  Notice that ParentFolderPath has values that are contained in the RelativePath column.  This is very important, and I'll explain this essential point later in the article.

The Data Set

The code for this procedure is quite lengthy, but relatively simple in concept, so I'll explain what it does instead of posting the code.  I built a stored procedure that opens up a cursor and loops through all of the reports in my ReportServer database.  I look in the Catalog table and pass in the 'Path' and 'Name' columns to my function and build up a table with my result set from each call. (I delete all rows with FolderLevel 0 since everything rolls up to that level)  This stored proc returns a select statement that my report can now consume.  The data set looks like this:


The Report

Now that I have my data set set up and structured vertically, I'm ready to build my report.  I set up my report so that I display one group showing each of the folders, and a child group which will display the reports.  My report looks like this:




The Folder group has groupings set on the RelativePath, and the Recursive Parent set to the ParentFolderPath as follows:

















Here's the important lesson to learn about using Recursive Parent, which I alluded to in my "very important" point above:  The values in the Recursive parent column (in our case ParentFolderPath) must be found in the Child group (in our case RelativePath).  The way the Recursive parent property works is it looks up the value of the Recursive parent value in the child column recursively until it gets to the top of the hierarchy.

I will try to explain this using some pictures from the data set.  If we isolate the Report1 data in our data set, we have the following 3 rows for that report:

The red square shows the inner grouping of the ReportName, so SSRS knows that these lines all belong to the same child group.  Now, to figure out  the Folder grouping, SSRS looks at the values in the ParentFolderPath (the recursive parent), shown in the green box, and looks for that value in the RelativePath column (child group), shown in in the orange box.

It continues that process recursively as follows:


Now SSRS is on the green box, "///test/BITesing" and so it looks for that member's look-up value, which is shown in the purple box.  It goes back to the RelativePath column to find the row with that value.  It continues this process until it gets to the brown box "//".  There is no value in the RelativePath column for that value, and so it stops the recursion.  SSRS follows the path from green, to purple to orange and then to brown to build the hierarchy.  

This recursive look-up is the reason why the values in the recursive parent column must have the same values as the child group, otherwise the chain will not link together properly.

Formatting

I add a little formatting to my report to hide the report detail lines for all rows except where the ReportLevelFlag = 1 (so the report shows up at the right place in the reporting structure), and I also indent the folder and report according to what level it is.

My padding looks like this:







The formula looks like this:  

=CStr((Parameters!PaddingConstant.Value * (First(Fields!FolderLevel.Value) - 1))+2) & "pt"

I have a parameter called PaddingConstant that I have defaulted to 15. 

Here's an example of what the report looks like.  Notice how the dark blue upper-case headers represent the folder structure, properly indented to reflect the nested folders, and the reports show up under their parent folder.





































The Recursive parent property in SSRS is a very powerful feature that makes reporting on jagged hierarchies much easier.  The concept is pretty straight forward once it is understood.  Hopefully this article helps fill in the huge void out there as to how this property works and how to structure the data set to take advantage of this feature.

Happy reporting!

2 comments:

  1. Hey great article. Can you send me the procedure that you created for the datasets. Im struggling with that. Giacalonesal@gmail.com

    ReplyDelete
  2. Thanks for your article.
    I think the dataset "sp" couldbe a select like this:

    Select f.FolderLevel, f.FolderName, f.RelativePath, f.ReportLevelFlag, c.Name ReportName, f.ParentFolderPath
    From Catalog c
    Cross Apply dbo.fnReportPathBreakDown( c.Path + '.rdl', c.Name + '.rdl' ) f
    Where FolderLevel != 0

    ReplyDelete