title: Custom Report Builder category: Reports tags: reports, custom, sql, builder, parameters priority: Normal

Custom Report Builder

The Report Builder at /admin/reports/builder lets you create custom reports using SQL queries. Design reports with configurable parameters, preview results before saving, and share them with other administrators.

Opening the Report Builder

From the Reports Center, click Create Report to open a blank report. To edit an existing custom report, click the edit icon next to the report name. The builder supports three modes:

Report Details Panel

The left panel captures the report metadata:

Available Categories

The Report Builder supports these built-in categories:

• Identity -- People, accounts, and workforce data
• Access -- Groups, memberships, and access rights
• Compliance -- Policy violations and framework adherence
• Access Review -- Certification campaigns and decisions
• Security -- Privileged access and risk indicators
• Sync -- Synchronization operations and history
• Audit -- Change tracking and decision history
• Custom -- General-purpose reports

Writing the SQL Query

The right panel contains the SQL query editor. Write a standard SELECT query against the IdentityCenter database.

Security Model

All custom reports execute with read-only access. INSERT, UPDATE, DELETE, DROP, and other data-modifying statements are blocked. This ensures that reports cannot alter production data.

Common Tables Reference

Example Queries

Accounts inactive for 90+ days:

SELECT DisplayName, Username, Email, Department,
       LastSyncedAt, IsActive
FROM Objects
WHERE ObjectClass = 'user'
  AND IsActive = 1
  AND LastSyncedAt < DATEADD(DAY, -90, GETDATE())
ORDER BY LastSyncedAt

Groups with more than 50 members and no owner:

SELECT g.DisplayName as GroupName, g.DN,
       COUNT(ogm.Id) as MemberCount
FROM Objects g
INNER JOIN ObjectGroupMemberships ogm ON g.Id = ogm.GroupId
WHERE g.ObjectClass = 'group'
  AND g.OwnerObjectId IS NULL
GROUP BY g.Id, g.DisplayName, g.DN
HAVING COUNT(ogm.Id) > 50
ORDER BY MemberCount DESC

Violation summary by department:

SELECT i.Department,
       COUNT(v.Id) as ViolationCount,
       SUM(CASE WHEN v.Severity = 'Critical' THEN 1 ELSE 0 END) as Critical,
       SUM(CASE WHEN v.Severity = 'High' THEN 1 ELSE 0 END) as High
FROM CompliancePolicyViolations v
INNER JOIN Identities i ON v.EntityId = i.Id
WHERE v.Status IN ('Open', 'Active')
GROUP BY i.Department
ORDER BY ViolationCount DESC

Adding Report Parameters

Parameters make reports dynamic. Click Add Parameter to define inputs that users fill in at runtime.

Reference parameters in your query using the @parameterName syntax.

Preview Mode

Before saving, click Test Query to execute the report in preview mode. The preview:

• Executes the SQL with read-only access
• Displays column names and the first rows of results
• Shows any error messages if the query fails
• Reports execution time

Tip: Always test your query before saving. If the query takes more than a few seconds, consider adding filters or limiting the result set.

Saving and Managing Reports

Click Save Report to store the configuration. Required fields are Report Name, Display Name, and Category. After saving:

• The report appears in the Reports Center under its assigned category
• Other administrators can run the report
• You can edit it later from the Report Builder
• Built-in reports cannot be deleted, but custom reports can

Tips for Efficient Queries

1. Use WHERE clauses -- Always filter data to the minimum needed
2. Avoid SELECT * -- Specify only the columns you need
3. Use indexes -- Filter on indexed columns (Id, ObjectClass, SourceConnectionId, IsActive)
4. Limit results -- Use TOP or date-range filters for large tables
5. Test with preview -- Verify performance before saving
6. Use JOINs wisely -- Only join tables you actually need
7. Add parameters -- Let users narrow scope instead of returning everything

Next Steps

• Built-In Reports -- Explore the 50+ reports included with IdentityCenter
• Report Export & Distribution -- Schedule and email report results
• Dashboard & Reporting -- Overview of reporting capabilities