Reporting on Subordinate Hours

[CalvinFold]

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:

1. A planner's manager runs the report, gets all her direct reports.
2. The manager's manager runs the report, gets all her direct reports, plus THEIR direct reports (#1 above).
3. The manager's manager's manager runs the report, gets all the users under #2 plus each of their reports under #1.
4. And so on.

Is this a $$-style filter, or is some other method? Or is it even possible?

[Doug_Den_Hoed__AtAppStore]

Hi Kevin,

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:

• Owner Manager ID = $$USER.ID (my direct reports) OR
• Owner.Manager.Manager ID = $$USER.ID (my manager's direct reports and their direct reports) OR
• Owner.Manager.Manager.Manager ID = $$USER.ID (and so on)

Regards,

Doug

[CalvinFold]

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?

• Check for 5 managers, if exists, use that, if not‚Ķ
• Check for 4 managers, if exists, use that, if not‚Ķ
• Check for 3 managers, if exists, use that, if not‚Ķ
• Check for 2 managers, if exists, use that, if not‚Ķ
• Check for 1 managers (everyone should have this, as far as this report is concerned)

Is this done as a filter in the report definition?

[SarahNa]

Hi Kevin,

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.

owner:managerID=$$USER.ID

owner:managerID_Mod=in

OR:1:ownerManagerMM:managerID=$$USER.ID

OR:1:ownerManagerMM:managerID_Mod=in

Error when I try to add:

OR:2:owner:manager:managerID=$$USER.ID

OR:2:owner:manager:managerID_Mod=in

Anyone else know what the syntax would be for getting the owner's manager's manager's manager?

[Doug_Den_Hoed__AtAppStore]

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.

Regards,

Doug

[DaleWh1]

Hi Kevin,

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

[CalvinFold]

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).

[CalvinFold]

@Dale Whitchurch‚

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.

[CalvinFold]

@Dale Whitchurch‚

Nevermind, stumbled upon it:

DE:owner:Managers=$$USER.name

DE:owner:Managers_Mod=cicontains

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.