Filter Hours by Customer User Picklist Field

[acreech]

Hi EazyBi Community,

I am trying to filter hours logged on an issue by a customer field we have called, Primary Developer. The custom field is a single select user pick list. We set the primary developer at the start of the sprint, because the ticket passes off for code review, and QA. This helps us keep track of who worked on the actual code later when doing demos/reviews.

Now we want to show in a report the number of hours only that primary devloper spent. I found a similar topic here: Effort Logged by the Issue Assignee related to hours spent by just the assignee.

I am trying to modify this case to use our custom field Primary Developer as opposed to assignee.

For this customer field, I am importing it as a dimension and property.

Here is what I have come up with so far.

CASE WHEN
[Issue].CurrentMember.Level.Name <> "Issue"
THEN
Sum(
  Descendants([Primary Developer].CurrentHierarchyMember,[Primary Developer].CurrentHierarchy.Levels("User")),
  CASE WHEN [Primary Developer].CurrentHierarchyMember.Name <> "(none)" THEN
  NonZero(([Measures].[Hours spent],
  -- check how many hours were logged by current assignee
   [Logged by].[User].GetMemberByKey([Primary Developer].CurrentHierarchyMember.Key)
  ))
  END
)
ELSE
-- if details per issue - check the value for current issue assignee:
  NonZero(([Measures].[Hours spent],
  -- check how many hours were logged by current assignee
   [Logged by].[User].GetMemberByKey([Issue].CurrentHierarchyMember.Get('Primary Developer'))
  ))
END

Thanks in advance.

[roberts.cacus]

Hi @acreech,

Unfortunately, the very same approach of retrieving work logged by issue assignees won’t work for user-picker field dimensions. eazyBI currently doesn’t import the same properties for user-picker fields as for the default user fields, most importantly, in this case, the user key. We have a feature request regarding that in our backlog, and I added your vote to it. I don’t have any estimates at this time.

Nevertheless, you can use a slightly different approach. Please see the formula below as a reference and try adapting it to fit your use case:

CASE WHEN
[Developer].CurrentMember.Level.Name <> "Developer"
THEN
Sum(
  Descendants([Developer].CurrentHierarchyMember,[Developer].[Developer]),
  CASE WHEN [Developer].CurrentHierarchyMember.Name <> "(none)" THEN
  Sum(
    Filter(
      Descendants([Logged by].CurrentMember,[Logged by].[User]),
      [Logged by].CurrentMember.Name = [Developer].CurrentMember.Name
    ),
    [Measures].[Hours spent]
  )
  END
)
ELSE
  Sum(
    Filter(
      Descendants([Logged by].CurrentMember,[Logged by].[User]),
      [Logged by].CurrentMember.Name = [Developer].CurrentMember.Name
    ),
    [Measures].[Hours spent]
  )
END

The formula compares both dimension user names as the user-picker field dimension doesn’t have the same user key as default user dimensions. Update the dimension and level names Developer into your Primary Developer. The report could look similar to the one below:

Best,
Roberts // support@eazybi.com