This week I have been doing some Report development for a custom application we have been developing at work. Given everything is SQL based we opted to build the reports using Reporting Services.
On the whole the reports are pretty straight forward. However there were 2 challenges.
The first is around hierarchical reporting. With in the application people can belong to different Occupational Units's(OU). One of the business rules for the reporting is 'A user can only report on their own OU and any OU that is below them.
To achieve this I ended up building a recursive function that returns a Scalar Table that works out all the OU's a specific user can see. I then use that in my where clause.
The Table Function looks like this
CREATE FUNCTION [Generate_OUTree]
(
-- Add the parameters for the function here
@OUID [uniqueidentifier]
)
RETURNS @OUTree TABLE
(
OUID [uniqueidentifier],
ParentID [uniqueidentifier],
OUName Varchar(500),
Level Int
)
AS
begin
-- Add the SELECT statement with parameter references here
With OUTree (OUID, ParentID, OUName, Level)
As
(
-- Anchor Member defination to get top level OU
Select OUID, ParentOUID, OUName, 0 as Level
From CustomerOUView
Where OUID = @OUID
UNION ALL
--Recursive member defination to get child companies
Select CCV.OUID, CCV.ParentOUID, CCV.OUName, Level + 1
From CustomerOUView CCV
Inner Join OUTree
On CCV.ParentOUID = OUTree.OUID
)
insert into @OUTree
Select * From OUTree
RETURN
As you can see I pass in a parameter that is the ID of the users OU.
Select *
From dbo.staffView
Where OUID IN(Select OUID From dbo.Generate_OUTree(@OUID))
CREATE PROCEDURE [Report_GenerateOUList]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
With OUTree (OUID, ParentID, OUName, Level, Sort)
As
(
-- Anchor Member defination to get top level OU
Select OUID, ParentOUID, OUName, 0 as Level ,Cast(OUName as Varchar(4000))
,OperatorCategoryName,MembershipCategoryName
From CustomerOUView
Where ParentOUID = '00000000-0000-0000-0000-000000000000' --top level OU has no parent
UNION ALL
--Recursive member defination to get child OU's Select CCV.OUID, CCV.ParentOUID, CCV.OUName,
Level + 1, Cast(Sort + '|' + CCV.OUName as Varchar(4000))
From CustomerOUView CCV
Inner Join OUTree
On CCV.ParentOUID = OUTree.OUID
)
Select * From OUTree
Order by Sort
In the database the OU ID's are all GUID's and as I wanted all OU's displayed in this report I have hard-coded for the top level OU.
Inside Reporting Services I am using the following to get indentation
=(Space(Fields!Level * 5) + Fields!OUName.Value)
This then causes 5 spaces for each level down the tree to be inserted in front of the OU's Name.
Once I had all this going and deployed to my Report Server everything appeared to be working fine when I viewed the reports with IE7. The only remaining thing to do was do a second check on the reports using Firefox.
Firefox and Reporting Services have a few issues. Due to he way RS renders the reports in IFRAME's Firefox causes them to get squashed up and not display correctly. After some frantic searching, Google is your friend 
First-up Firefox only shows the top 2 to 3 cm's of your report. The rendering puts it a small IFRAME instead of expanding out down your 'page' the way IE does. This is fairly easy to resolve, thanks to Jon Galloway's blog for this answer. You need to edit the ReportingServices.css. On my machine it lives here "C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager\Styles\"
In the css file you need to add the following entry.
/* Fix report IFRAME height for Firefox */
.DocMapAndReportFrame
{
min-height: 860px;
}
