Management Rollup

Question

Is it possible to run a report of all employees that report up to a manager, regardless of levels in between (sort of an org chart report)? For instance, the CIO has 4 VPs that report to him. Those 4 VPs have 4 directors each that report to them, those directors... and so on. If I run a report on the CIO, it would would show the VPs, the Directors, the Managers, the Employees, etc, all rolled up under the CIO. Similar to the "Show indirect members" box can be used for group members to expand out members of nested groups.

Thank you!

Answer 1

Update 2018

Starting with Adaxes 2018.1 you can use a built-in report, Subordinates of user. By default, the report is located in container Reports\All Reports\users\Managers and Subordinates. Also, you can create custom reports of your own. For details, see https://www.adaxes.com/tutorials_ActiveDirectoryManagement_CreateReport.htm.

Original

Hello,

There is no built in report for this purpose, but you can accomplish the task with PowerShell scripts. You can create a PowerShell script that will build a list of all subordinates of a user in the form of an HTML report sent to the initiator's email. Then, you can use the script with a Custom Command (to generate and send the report on demand) or with a Scheduled Task (to generate a report on schedule).

We can help you with the script, but for this purpose we need to know which properties of the subordinates need to be included in the report.

By the way, thanks for a good suggestion! We've decided to include the functionality to generate such a report in one of our future releases.

Comments

I figured it would be possible with Powershell, but my skills are relatively basic, so any scripting assistance would be appreciated. The properties I would need would be pretty basic: Manager Name, Job Title (of both manager and employee) and Display Name. So something like:

Manager Name          Manager Job Title         Employee Name            Employee Job Title
Joe Smith                    CIO                 Jim Wilson                 VP of Widgets
Joe Smith                    CIO                 Robert Town                VP of Sparks
Jim Wilson              VP of Widgets            Mike Winger              Director of Widgets
Robert Town             VP of Sparks             Aaron Lewin               Director of Sparks

Thanks!

---

Hello,

Here's the script that will do the job:

# Email message setings
$to = "%adm-InitiatorEmail%" # TODO: modify me
$subject = "My Subject" # TODO: modify me
$htmlReportHeader = @"
<h1><b>Full List of Subordinates for %name%</b></h1><br/>
<table border="1">
    <tr>
        <th>Manager Name</th>
        <th>Manager Job Title</th>
        <th>Employee Name</th>
        <th>Employee Job Title</th>
    </tr>
"@ # TODO: modify me

$htmlReportFooter = @"
</table><br/>

<p><i>Please do not reply to this e-mail, it has been sent to you for notification purposes only.</i></p>
"@ # TODO: modify me

function GetAllSubordinates($directReportDN, $subordinates, $managerName, $managerJobTitle)
{
    if($subordinates.ContainsKey($directReportDN))
    {
        return
    }

    # Bind to Subordinate
    $user = $Context.BindToObjectByDN($directReportDN)

    # Get user name
    $userName = $user.Get("name")

    # Get user job title
    try
    {
        $userJobTitle = $user.Get("title")
    }
    catch
    {
        $userJobTitle = " "
    }

    # Add to HashTable
    $userInfo = New-Object PSObject
    $userInfo | add-member Noteproperty ManagerName $managerName
    $userInfo | add-member Noteproperty ManagerJobTitle $managerJobTitle
    $userInfo | add-member Noteproperty UserName $userName
    $userInfo | add-member Noteproperty UserJobTitle $userJobTitle

    $subordinates.Add($directReportDN, $userInfo) | Out-Null

    # Get Subordinates
    try
    {
        $directReportDNs = $user.GetEx("directReports")
    }
    catch
    {
        return
    }

    foreach ($directReportDN in $directReportDNs)
    {
        GetAllSubordinates $directReportDN $subordinates $userName $userJobTitle
    }
}

# Get subordinates
try
{
    $directReportDNs = $Context.TargetObject.GetEx("directReports")
}
catch
{
    $Context.LogMessage("The user doesn't have any direct reports.", "Error") # TODO: modify me
    return
}

$subordinates = New-Object "System.Collections.Hashtable" 
foreach ($directReportDN in $directReportDNs)
{
    GetAllSubordinates $directReportDN $subordinates "%name%" "%title%"
}

# Sort list
$subordinates = $subordinates.Values | Sort-Object -Property @{Expression={$_.ManagerName}; Ascending=$True}

# Build HTML report
foreach ($subordinate in $subordinates)
{
    $htmlReportHeader += "<tr><td>" + $subordinate.ManagerName + "</td>"
    $htmlReportHeader += "<td>" + $subordinate.ManagerJobTitle + "</td>"
    $htmlReportHeader += "<td>" + $subordinate.UserName + "</td>"
    $htmlReportHeader += "<td>" + $subordinate.UserJobTitle + "</td></tr>"
}
$htmlBody = $htmlReportHeader + "</table>" + $htmlReportFooter

# Send the report
$Context.SendMail($to, $subject, $NULL, $htmlBody)

In the script:

• $to - specifies the recipient of the email notification. The %adm-InitiatorEmail% that is used in the script will be replaced with the email address of the user who triggered execution of the script. For example, if the script is executed with the help of a Custom Command, it will be replaced with the email address of the user who launched the Custom Command.
• $subject - specifies the subject of the email notification.
• $htmlReportHeader - specifies the header of the email notification.
• $htmlReportFooter - specifies the footer of the email notification.

Modify the script to your requirements.

To allow generating the report on demand with the help of a Custom Command:

1. Create a new Custom Command.
2. On the 2nd step of the Create Custom Command wizard, select the User object type.
   
3. On the 3rd step, add the Run a program or PowerShell script action and paste the above script in the Script field.
   

---

This is ridiculously awesome, and works perfectly. Thank you!

---

Thank you for your good words. We really appreciate it! :)