Report on data that does not exist

[kchorny]

I have been asked to create a report to show on a manager dashboard.  They want it to show, of their direct reports, which ones HAVE NOT created any time cards for the previous week.

I can obviously report on data that exists, but am at a loss as to how to report on data that doesn't exist.  

My only solution at this point is to send a notification containing a mail script, that is triggered by an event that is triggered from a scheduled job.  But there will be no dashboard report.

Any ideas on how I could show this in a report/dashboard?

[Uncle Rob]

You're right that a report is only a presentation of records that actually exist.  So if you have Larry, his brother Darryl, and his other brother Darryl missing time cards, you have to have a record to present in the report.

You've got 2 options as far as I can see, ranked worst to best:

1)  Make a summary table for no-time cards
At the end of your time keeping periods, you insert a record here for every user without a timesheet, with a second rule to delete the entry if a Timesheet gets created.  It will work, but it requires system customization, which should always be last resort

2)  Auto generate Time Sheets and report on blanks
Using this property you can have the cards (and therefore sheets) generate automagically.  Now you only have to report on Time Sheets that don't have a submitted status.  

I like #2 MUCH better as its OOB.  Way less tech debt, same end result.

[kchorny]

Thanks, Robert.  After I posted this, I had another thought... a scripted report, which is something I've only done once before, so it took some tweaking to get it to work. 

I created a script include that generates and returns an array of user names of the users reporting to the current manager (the one who's running the report) who have no time cards, either this week or last week.  Then I created a report on the sys_user table, and the filter is set up like this:

User ID (user_name) - is one of - javascript:new reportHelper().getUsersWithNoTimeSheet(gs.getUserID(),'last');

My script is written so that I can call the same function with 'this' instead of 'last', and plug that into a report to show users who have not created any time cards THIS week.

Where would this fall on your list of worst to best?

[Uncle Rob]

That's an approach that completely escaped me.  Thank you for teaching me something new!

I'd put that between 1 and 2 if you really want my preference.  Its better than a custom table / summary solution.  But its still semi-exotic for a very narrow band of function.  That is to say: it can only ever be "users with no timesheet for this week.

Using Option 2 you get an OOB solution that has a myriad of unplanned benefits.  Namely, the solution works for anyone why may have multiple periods of time cards unsubmitted.  I only know this because I *MAY* have been extraordinarily bad at time keeping in my youth.  You could also use the created records for other stuff... such as timed reminders with hyperlinks to the timecards that need filling.

[Pradnyesh]

Can You please share the script include Please.