asked on

Org Chart in SQL Server 2005 Reporting Services

Is it possible to get Reporting Services to render a 'dynamic' org chart. For example:

Parameter:      PersonId

IF PersonId represents a team lead, then the Report renders something like this:

Team Lead 1A
      Member 1
      Member 2
      Member 3
      Member 4

IF PersonId represents the Manager of a group of Team Leads, then the Report renders something like this:

Manager A
      Team Lead 1A
            Member 1
            Member 2
            Member 3
            Member 4
      Team Lead 2A
            Member 1
            Member 2
            Member 3

IF PersonId represents a Director of Managers of Team Leads, then the Report renders something like this

Director A
      Manager A
            Team Lead 1A
                  Member 1
                  Member 2
                  Member 3
                  Member 4
            Team Lead 2A
                  Member 1
                  Member 2
                  Member 3
      Manager B
            Team Lead 1B
                  Member 1
                  Member 2
                  Member 3
                  Member 4
            Team Lead 2B
                  Member 1
                  Member 2
                  Member 3

et cetera, et cetera

I have written the query that will return the above given a PersonId at any level. But, Reporting Services groups appear to be hard coded (i.e. have to know exactly how many groups I need before I render). Is there any way around this?

All help appreciated.

ASKER CERTIFIED SOLUTION

Emes

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

fesnyng

ASKER

Thank you for the feedback. I had thought about a matrix, but it did not seem that would work. I will give it a (more determined) try and let you know how that turns out.

fesnyng

ASKER

So far, no luck with the matrix. I have used the Matrix control for pivot table reports. That may be a limitation.

I tried a heirarchy of 3 levels. I made the HeirarchyLevel a column and UserName the data. I left row blank. By default, the matrix puts in the FIRST agrregate function for data. That returns 3 UserNames (one for each level). That was encouraging. However, the Matrix requires aggregate functions. Removing the FIRST from the UserName in Data returns:

>>The Value expression for the textbox UserName references a field outside an aggregate function. Value expressions in matrix cells should be aggregates, to allow for subtotaling.<<

Still working with the Matrix.

fesnyng

ASKER

Found a workaround:

Columns: HeirarchyLevel
Rows: UserName
Data: Username

Set the properties for 'Rows' (which is first column in the matrix) to white font on white background. Set the properties for 'Columns' (which is the top row in the matrix) to white font on white background.

Not pretty (yet), but it does the trick.

Thank you.

fesnyng

ASKER

Thank you.