?
Solved

Org Chart in SQL Server 2005 Reporting Services

Posted on 2009-02-17
5
Medium Priority
?
606 Views
Last Modified: 2012-05-06
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.
0
Comment
Question by:fesnyng
  • 4
5 Comments
 
LVL 14

Accepted Solution

by:
Emes earned 2000 total points
ID: 23666693
use a matrix and you can do it

0
 
LVL 5

Author Comment

by:fesnyng
ID: 23669033
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.
0
 
LVL 5

Author Comment

by:fesnyng
ID: 23670387
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.

0
 
LVL 5

Author Comment

by:fesnyng
ID: 23670537
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.
0
 
LVL 5

Author Closing Comment

by:fesnyng
ID: 31547830
Thank you.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question