Reporting on Subordinate Hours | Community
Skip to main content
K
KevinQu1
Level 10
September 30, 2020
Question

Reporting on Subordinate Hours

  • September 30, 2020
  • 4 replies
  • 1467 views

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?

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

    4 replies

    Doug_Den_Hoed_AtAppStore
    Community Advisor
    Doug_Den_Hoed_AtAppStoreCommunity Advisor
    Community Advisor
    September 30, 2020

    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

    K
    KevinQu1Author
    Level 10
    September 30, 2020

    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?

    S
    SarahNa
    Level 6
    September 30, 2020

    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
    Community Advisor
    Doug_Den_Hoed_AtAppStoreCommunity Advisor
    Community Advisor
    September 30, 2020

    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

      D
      DaleWh1
      Level 3
      October 1, 2020

      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

        K
        KevinQu1Author
        Level 10
        October 1, 2020

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

        Terms & ConditionsAccessibility statement

        Loading footer...