Monday, June 29, 2015

SQL Reporting - A Tip on "Affected User" Reports

8:16 PM Posted by A No comments

I previously published a blogpost detailing how to get started with an Affected User report and it struck me as I was working the other day that there was arguably vital step I had missed. So if you haven't noticed it, corrected my work, or possibly using it in your own reporting this is your heads up!

There are some cases where an Incident in the report will return itself multiple times. It may not be obvious, but you will see every Affected User the Incident has had.

What? How is that possible? I want to yell at you.

Alright. I welcome the criticism in this case, so before you go on a tirade let me explain why that happens. The WorkItemAffectedUserVw table contains a property called DeletedDate. This date will generally be null, but when you flip the Affected User of an Incident the field has value. Needless to say, this only takes an extra step in the WHERE clause to make sure you grabbing the current Affected User.

WHERE user.DeletedDate is null

WITH THIS said. I've already updated my TechNet posting to calling out in the comments how to quickly turn this on/off for yourself. The query has had nothing altered with the exception of the single extra WHERE statement. The link hasn't change so feel free to get the updated version here.


Post a Comment