Report on the absence of data? (noncompliance)

[SK74]

A couple examples of noncompliance: 

- Who hasn't logged any timecards this week?

- Who logged less than 40 hours in timecadrs this week, including those that didn't log any time at all? 

- Which projects do not have status reports this week?

Hopefully these examples illustrate my challenge--I need to report on records not existing.  For my first question  Im figuring I start with the User table for rows and then week number would be column and I could see total hours (or null, or zero) in each cell (see example)?  Welcome to other layout ideas to get the desired outcome.  I can't start with Time cards because C (in example below) woudln't even show up in a report based on timecards because s/he has none.

Name	Week of 28 April 2019	Week of 5 May 2019
A	40	40
B	40	37
C	-	-

  So hopeful you can help! I went to multiple reporting sessions and the Genius Bar last week at Knowledge and no one could solve my question!

[Adam Stout · Accepted solution]

The first and third questions are straight forward with a Related List Condition on sys_user (RLC on time cards) and on pm_project (RLC on status report).  See this article for more example of that: https://community.servicenow.com/community?id=community_blog&sys_id=bd0eaa2ddbd0dbc01dcaf3231f96199e

As for the missing hours, I think there is some functionality to do this in Madrid.  If you aren't there yet, you may need a job that summarizes hours submitted/approved per user per week then report on that.  Not a simple report, but still possible without too much work.

[Tim Provin]

The only way I have been able to report on the absence of data is to configure database views.  For example, the image below will get all users, and join in any incidents that they are the caller for.  This would allow me to report on how many incidents a user has reported, as well as those that have not reported any.  I can then report against this table and filter the data down to specific date ranges, or whatever I need to look for.

[Adam Stout]

Related List Conditions greatly reduced the need for the use of views in this case:  https://docs.servicenow.com/bundle/madrid-platform-user-interface/page/use/using-lists-v3/task/creat...

[Tim Provin]

You sir are correct.  I haven't played with them since I have had my views set up and working for quite a few versions now.  

Not trying to hijack SK's question, but if he does go with Related List Conditions to build a report, is it possible to access data from the related list records?  I was just trying in my system to pull a list of all users with timesheets containing less than 40 hours, or have no timesheet at all and could only access the user record data.