Customize reports

You can generate built-in reports to report on one user, one application, one role, or one certifier, as described in Generate built-in reports. If you want a single report to contain more than one user, application, role, or certifier, you must create a custom report.

Create a custom report

In the Identity Administration portal, go to Reports > My Reports > New Report.
In the upper left text box, type the name of the new report. For example:

Click Edit Script, then Yes to confirm.
Copy the query syntax for the report you want from Report query syntax and paste it into the Editor box, as shown in this example.
Edit the parameters you want to change. See Report query parameters for details.
Click Preview to display the report. When prompted, enter the start and end dates you want the report to include. Click Save when you're done.

Report query syntax

Certification decisions taken by multiple certifiers

select

cc.Name as 'Cycle Name' , cc.CycleStartDate as 'Cycle start date', cc.CycleEndDate as 'Cycle end date',

cr.ResourceType as 'Resource Type',

CASE

WHEN cr.ResourceType='Role' THEN role.Name

WHEN cr.ResourceType='Application' THEN app.DisplayName

END as 'Resource Name',

ccu.Status as 'Action type',

u.DisplayName as Certifier,

COALESCE(mem.Username, mem.Email) as 'Username/Email' ,

ccu.ActionTime as 'Action taken on'

from CycleUsers as ccu

join CampaignCycle as cc on cc.ID=ccu.CycleId and cc.Certifier = ccu.Certifier and cc.CampaignId=ccu.CampaignId

inner join CampaignResource as cr on cr.CampaignId = cc.CampaignId and cr.ResourceId = ccu.ResourceId

left outer join Role as role on role.ID = cr.ResourceId and cr.ResourceType='Role'

join Application as app on app.ID = cr.ResourceId and cr.ResourceType='Application'

join User as u on u.ID = ccu.Certifier

join User as mem on mem.ID = ccu.UserId

where cc.CycleStartDate >=@<StartDate> and cc.CycleEndDate <=@<EndDate>

and u.Username IN( '<certifier1>','<certifier2>' )

Certification decisions taken on multiple users

select

cc.Name as 'Cycle Name' , cc.CycleStartDate as 'Cycle start date',

cc.CycleEndDate as 'Cycle end date',

cr.ResourceType as 'Resource Type',

CASE

WHEN cr.ResourceType='Role' THEN role.Name

WHEN cr.ResourceType='Safe' THEN cr.ResourceId

WHEN cr.ResourceType='Application' THEN app.DisplayName

END as 'Resource Name',

ccu.Status as 'Action type',

cf.DisplayName as 'Certifier',

COALESCE(u.Username, u.Email) as 'Username/Email' ,

ccu.ActionTime as 'Action taken on'

from campaigncycles as cc

join campaigncertifierusers as ccu on ccu.CycleId = cc.ID and ccu.Certifier = cc.Certifier

join users as u on ccu.UserId = u.ID

join users as cf on ccu.Certifier = cf.ID

inner join campaignresources as cr on cr.CampaignId = cc.CampaignId and cr.ResourceId = ccu.ResourceId

left outer join roles as role on role.ID = cr.ResourceId and cr.ResourceType='Role'

left outer join application as app on app.ID = cr.ResourceId and cr.ResourceType='Application'

where cc.CycleStartDate >= @StartDate and cc.CycleEndDate <= @EndDate

and u.Username IN( '<username1>', '<username2>' )

Certification decisions taken on multiple applications

select

cc.Name as 'Cycle Name' , cc.CycleStartDate as 'Cycle start date', cc.CycleEndDate as 'Cycle end date',

cr.ResourceType as 'Resource Type',

app.DisplayName as 'Resource Name',

ccu.Status as 'Action type',

u.DisplayName as Certifier,

COALESCE(mem.Username, mem.Email) as 'Username/Email' ,

ccu.ActionTime as 'Action taken on'

from CycleUsers as ccu

join CampaignCycle as cc on cc.ID=ccu.CycleId and cc.Certifier = ccu.Certifier and cc.CampaignId=ccu.CampaignId

inner join CampaignResource as cr on cr.CampaignId = cc.CampaignId and cr.ResourceId = ccu.ResourceId and cr.ResourceType='Application'

left outer join Application as app on app.ID = cr.ResourceId and cr.ResourceType='Application'

join User as u on u.ID = ccu.Certifier

join User as mem on mem.ID = ccu.UserId

where cc.CycleStartDate >= @StartDate and cc.CycleEndDate <= @EndDate

and app.DisplayName IN( '<app1>','<app2>' )

Certification decisions taken on multiple roles

select

cc.Name as 'Cycle Name' , cc.CycleStartDate as 'Cycle start date', cc.CycleEndDate as 'Cycle end date',

cr.ResourceType as 'Resource Type',

role.Name as 'Resource Name',

ccu.Status as 'Action type',

u.DisplayName as Certifier,

COALESCE(mem.Username, mem.Email) as 'Username/Email' ,

ccu.ActionTime as 'Action taken on'

from CycleUsers as ccu

join CampaignCycle as cc on cc.ID=ccu.CycleId and cc.Certifier = ccu.Certifier and cc.CampaignId=ccu.CampaignId

join CampaignResource as cr on cr.CampaignId = cc.CampaignId and cr.ResourceId = ccu.ResourceId and cr.ResourceType='Role'

join Role as role on role.ID = cr.ResourceId and cr.ResourceType='Role'

join User as u on u.ID = ccu.Certifier

join User as mem on mem.ID = ccu.UserId

where cc.CycleStartDate >= @StartDate and cc.CycleEndDate <= @EndDate

and role.Name IN( '<role1>','<role2>' )

Report query parameters

You must edit the query to specify parameters. In this example, you must specify at least one certifier:

where cc.CycleStartDate >=@<StartDate> and cc.CycleEndDate <=@<EndDate>

and u.Username IN( '<certifier1>','<certifier2>' )

The following table describes how to specify the parameters.

Report query parameters
Parameter	Description	Report
u.Username	Indicates that you are using a username (instead of an email address) to identify each user. Enclose each username within single quotes and use a comma between each username. For example: 'john@example.com','jane@example.com'	Users, certifiers
u.Email	Indicates that you are using an email address (instead of a username) to identify each user. Enclose each email address within single quotes and use a comma between each email address. For example: 'jsmith@example.com','mroberts@example.com'	Users, certifiers
'<certifier1>','<certifier2>'	Certifiers to include in the report. Specify either usernames with u.Username or email addresses with e.Email.	Certifiers
'<username1>','<username2>'	Users to include in the report. Specify either usernames with u.Username or email addresses with e.Email.	Users
'<app1>','<app2>'	Applications to include in the report. Use the application display name. For example: 'AssetSonar','Office365'	Applications
'<role1>','<role2>'	Roles to include in the report. For example: 'PortalAdmin','Approvers'	Roles