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.
To actually use this to achive what I want I am calling he function like this
Select *
From dbo.staffView
Where OUID IN(Select OUID From dbo.Generate_OUTree(@OUID))
This effectively returns all the rows from my view where the OUID in the view is in the list generated by my function.
The follow on to this was we had to build a full OU tree report. The report shows each OU under its parent and indents teh list so it is wasy to see which child OU belongs to Which parent.
The hard part was to be able to sort the report so that the OU structure was returned in a format that made it easy to group the OU's together.
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 
, and a bit of testing on a couple of report servers found the following.
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;
}
This forces Firefox to use a taller IFRAME.
I also found that the width of the report was getting squashed up on my deployment server, although on my laptop's report server it wasn't happening. On investigating the two machines the only thing I could find was a difference in the build versions of the 2 SQL servers. The Deployment server is using SQL2005 SP1 while my laptop is SQL2005 SP2.
As a work around I found that by putting an empty text box in the header of my reports that was the full width of the report Firefox no longer squashes up the report. While ideally I will update the server with the latest SP for SQL this is acting as a work around for the time being.
