Non-User Account Information

This can be added to the custom SSRS report, but an SSRS/database developer must modify and redeploy the .rdl; it is not a built-in feature that an end user can enable.

Recommended implementation

1. Add a Level 6 leader parameter
   • Open the report in Microsoft Report Builder or SQL Server Data Tools.
   • Add a dataset that returns the Level 6 leaders, exposing the leader’s NUID as the value and display name as the label.
   • Add a report parameter such as Level6LeaderNUID.
   • Under Parameter Properties → Available Values, select Get values from a query and map it to that dataset.
   • If another parameter first selects a business unit or organization, make the Level 6 parameter dependent on it. SSRS supports these cascading, query-backed parameters.^1^
   The basic lookup must not be treated as an authorization control. If users may only select certain leaders, add a real current-user authorization predicate or return only leaders permitted for the executing identity.
2. Expand the selected leader into all subordinate owners Use the organization’s employee-to-manager relationship to return the selected leader and every direct or indirect report. Recursive CTEs are designed for hierarchical data such as organizational charts.^2^

WITH OrgTree AS
(
    SELECT NUID, ManagerNUID
    FROM dbo.OrganizationHierarchy
    WHERE NUID = @Level6LeaderNUID

    UNION ALL

    SELECT e.NUID, e.ManagerNUID
    FROM dbo.OrganizationHierarchy AS e
    JOIN OrgTree AS p ON e.ManagerNUID = p.NUID
)
SELECT a.*
FROM dbo.NonUserAccounts AS a
JOIN OrgTree AS o ON o.NUID = a.OwnerNUID
OPTION (MAXRECURSION 100);

This sample is executable only after mapping these placeholders to the actual schema:

• OrganizationHierarchy
• NonUserAccounts
• NUID
• ManagerNUID
• OwnerNUID

MAXRECURSION 100 is a configurable testing safeguard, not a required production value. Confirm the maximum legitimate hierarchy depth and check for circular manager relationships before choosing the production limit.

3. Replace the existing one-owner filter
   • Update the main report dataset so it joins to the hierarchy result rather than using an equality condition for one owner.
   • Refresh the dataset fields.
   • Preview with a known Level 6 leader and validate:
     • the leader is included or excluded according to the requirement;
     • direct and indirect reports are included;
     • owners outside that hierarchy are excluded;
     • duplicate accounts are not introduced.
4. Test and deploy
   • Compare the exported row count with an independently validated hierarchy.
   • Test a large Level 6 organization and have the DBA review the execution plan and indexes on the hierarchy NUID/manager fields and account owner field.
   • Deploy the revised .rdl through the organization’s normal release process.

If multiple Level 6 leaders must be selected

Enable Allow multiple values on Level6LeaderNUID, and change the CTE anchor specifically to:

WHERE NUID IN (@Level6LeaderNUID)

SSRS multi-value query parameters require IN (@Parameter) with parentheses.^3^

Security requirement

Because NUIDs are sensitive, do not rely on the leader parameter, hidden fields, report filters, or folder access to restrict which rows can be exported. Microsoft states that a report is not a security boundary and that data access must be enforced at the data source, such as with database permissions or row-level security.^4^

Implement both:

• Report access: grant only the required SSRS users or groups permission to run/export the report.
• Data access: restrict the report execution identity to the leadership trees it is permitted to retrieve. SQL Server row-level security can apply filter predicates to reads so unauthorized rows are removed by the database.^5^

Finally, specify the required export format separately. “Secure NUID” describes the sensitive content or security requirement, not an SSRS export format; the implementation request should name the expected output, such as CSV or Excel, and its approved storage/delivery location.

References

1. Add cascading parameters to a paginated report (Report Builder)
2. Recursive queries using common table expressions (Transact-SQL)
3. Add a multi-value parameter to a paginated report
4. Secure reports and resources
5. Row-level security