I want to create an hours report that is flexible enough to include "every user who is my subordinate" which would include direct reports, as well as reports to those reports, and so on.
Assuming I have everyone's Manager filled-out properly, of course.
Ideally this report would work when:
Is this a $$-style filter, or is some other method? Or is it even possible?
Topics help categorize Community content and increase your ability to discover relevant content.
We use a similar concept in our Executive Dashboards solution, but if you'd like to roll your own, I'd suggest you try an OR statement for as many levels as you need, looking for:
What would happen if different managers have different levels of hierarchy to "reach bottom"?
So in the above example, #1 only has one level of reports, but example #2 has two levels.
And even from the same managerial level, say, Director‚Ä¶some will have 4 levels beneath them, some have 5, some have only 3.
Would the calculation freak out of you do Owner.Manager.Manager.Manager.Manager.Manager ID = $$USER.ID but only 3 of those Manager statements have values?
Or are you saying I have to do a multi-level OR and basically on statements in the "or "where it fails it just doesn't report back?
Is this done as a filter in the report definition?
I tried getting this filter to work and I can get the owner's manager and owner's manager's manager, but get errors if I try to go any further.
Error when I try to add:
Anyone else know what the syntax would be for getting the owner's manager's manager's manager?
Good point Kevin,
In my experience, hierarchies are rarely hierarchical, due to organizational distinctions between different departments, vacancies, etc. in our Executive Dashboards solution, we recognize that fact by introducing an Org Chart Level (OCL) concept to help organize reports to be among a similar peers (e.g. C-Level, VPs, Directors, Managers, etc.), particularly when comparing data apples-to-apples. That OCL technique "pulls" the hierarchy down to each user which makes it easier to then report upon using "contains" vs a bunch of "ORs": both in our reports, and any others you might choose to develop on your own. You could do likewise by installing our package, or manually creating a similar set of custom data.
Alternatively (returning to the ORs...), yes, precisely: the idea of checking for data that might not exist (e.g. a Manager two levels up from a person whose Manager has no Manager) shouldn't "hurt" in an OR statement...it just doesn't apply: only the data that matches the (relevant) OR branches are then returned.
That said (from Sarah's note), it might require some finessing to get the OR working as intended, too, and -- although this might have changed -- one of my rules of thumb is to try to avoid more than four OR clauses as my longstanding impression is that performance can sometimes suffer beyond that.
We worked around this with a custom form. Create a User custom form (or leverage your existing one) and create a calculated custom field from the following logic (borrowed from a post in the old community days):
CONCAT(IF(!ISBLANK(Manager),Manager),IF(!ISBLANK(Manager.Manager), ", "+ Manager.Manager),IF(!ISBLANK(Manager.Manager.Manager), ", "+ Manager.Manager.Manager),IF(!ISBLANK(Manager.Manager.Manager.Manager), ", "+ Manager.Manager.Manager.Manager),IF(!ISBLANK(Manager.Manager.Manager.Manager.Manager), ", "+ Manager.Manager.Manager.Manager.Manager),IF(!ISBLANK(Manager.Manager.Manager.Manager.Manager.Manager), ", "+ Manager.Manager.Manager.Manager.Manager.Manager),IF(!ISBLANK(Manager.Manager.Manager.Manager.Manager.Manager.Manager), ", "+ Manager.Manager.Manager.Manager.Manager.Manager.Manager),IF(!ISBLANK(Manager.Manager.Manager.Manager.Manager.Manager.Manager.Manager), ", "+ Manager.Manager.Manager.Manager.Manager.Manager.Manager.Manager),IF(!ISBLANK(Manager.Manager.Manager.Manager.Manager.Manager.Manager.Manager.Manager), ", "+ Manager.Manager.Manager.Manager.Manager.Manager.Manager.Manager.Manager),IF(!ISBLANK(Manager.Manager.Manager.Manager.Manager.Manager.Manager.Manager.Manager.Manager), ", "+ Manager.Manager.Manager.Manager.Manager.Manager.Manager.Manager.Manager.Manager))
Make sure you attach this custom form to your users.
Now, in your report, use a filter that takes this Assignment Users >> [custom field] with the contains operator and the value is $$USER.name
Thanks Dale! I think this will be the solution I'll try. Since I may have more than four levels of "OR" to deal with (per Doug/Sarah).
Is there a way to get this filter to work on an Hours report?
I need to report the hours of all subordinates. I'm still kicking this around myself, but the syntax eludes me; assuming I need text mode.
Nevermind, stumbled upon it:
Didn't need text mode, the UI had it, just wasn't looking in the correct spot.
In text mode I got tripped-up by where to put the "DE:" part. Still not wrapping my head around the whole "when to use colons, periods, DE, brackets" thing.
I think this worked; currently having the Finance person check the resulting report.