I'm trying to get a report that will pull all the tickets for an affected user. I do not see this in the console. Is there not such a report?

Such a report does not exist out of box - which...believe me I already share your frustration reading that. Regardless, I have such a SQL query for the DW if you're i

Yes very frustrated. If you don't mind Adam I would like to try your query. Something is better than nothing. Thanks for your help.

I'm a firm believer in over delivering. That said, the following will get the average time to resolution per Incident. However before it's possibly seen as "That is not even close to what I was asking Adam!" I want to draw your attention to the kind of results this is going to pull first.

• Get me a list of all Incidents (IR34985, IR34986, etc.)
• For each incident show me the Status, Title, Resolution Description (will be null if not resolved), the Incident classification, the Employee, the Employee's Department, the Hours and Minutes to resolve

Based on the above, in the Where clause you'll be able to decide what you want/don't want in your result set. The SQL query will make more sense contextually (I hope?) but suffice it to say you could do a Where clause of only showing a specific Affected User, or a specific Department, or a specific Source, etc. To be honest, the genesis of the SQL query was I wanted something to show me all Incidents broken up by Active Directory Department. Obviously this means you have to have the AD User's Department attribute defined and thus in turn would be synced to the SCSM DB, and thus DW.

Additionally I cheated in a few areas and didn't do all of the SQL joins that I probably should have, but it still gets the job done. It goes without saying for onlookers of this thread - help make this better! Never the less, I hope you find this useful.

DECLARE @StartDate datetime, @EndDate datetime
SET @StartDate = '03-01-2015'; 
SET @EndDate = '03-31-2015'

SELECT	IncidentDimvw.Id as [Incident Number], au.Department as [Dept], REPLACE(IncidentDimvw.Status, 'IncidentStatusEnum.', '') as 'Status', IncidentDimvw.Title, 
			incidentdimvw.ResolutionDescription,
			IncidentClassificationvw.IncidentClassificationValue as 'Category', au.displayname as 'Employee', 
			datediff(minute, incidentdimvw.CreatedDate, IncidentDimvw.ResolvedDate) / 60 as [Hours to Resolve],
			datediff(second, incidentdimvw.CreatedDate, IncidentDimvw.ResolvedDate) % 60 as [Minutes to Resolve]
FROM	UserDimvw as au 
		INNER JOIN WorkItemAffectedUserFactvw 
			ON au.UserDimKey = WorkItemAffectedUserFactvw.WorkItemAffectedUser_UserDimKey 
		INNER JOIN WorkItemDimvw 
			ON WorkItemAffectedUserFactvw.WorkItemDimKey = workitemdimvw.WorkItemDimKey
		INNER JOIN EntityDimvw as c
			ON c.EntityDimKey = WorkItemDimvw.EntityDimKey
		INNER JOIN IncidentDimvw
			ON c.EntityDimKey = IncidentDimvw.EntityDimKey
		INNER JOIN IncidentClassificationvw 
			ON IncidentClassificationId = IncidentDimvw.Classification_IncidentClassificationId 

WHERE	(IncidentDimvw.id is not null) 
		and (au.Department not like 'Information Systems')
		and (IncidentDimvw.Source not like 'IncidentSourceEnum.SCOM')
		and (au.DisplayName = 'Adam Dzak')
		and ((incidentdimvw.CreatedDate >= @StartDate) and (incidentdimvw.CreatedDate < @EndDate + 1))
GROUP BY Incidentdimvw.Id, IncidentDimvw.Status, IncidentDimvw.Title,incidentdimvw.ResolutionDescription, au.DisplayName, au.Department, IncidentClassificationvw.IncidentClassificationValue, incidentdimvw.CreatedDate, IncidentDimvw.ResolvedDate
ORDER BY Department		

This will be very useful for me when my boss request for something like this. Like I said something is better than nothing. Thanks for your help Adam!!

It incentivized me to upload to my TechNet gallery as a "primer" query along with a related blogpost. Glad to help! http://scsmmercenary.blogspot.com/2015/04/sql-reporting-affected-user-report.html