Linking Non-Sprint Work to Sprint Based on Logged By and Date

Questions & Answers
1

I’m new to working with cubes (MDX) so any assistance would be great. We have two projects I need to link. I want to pull hours logged in our TIME project (no sprints, time logged for PTO, Unassigned Time, and any other time that reduces the time available to work on the sprint) and sum and associate them with Sprints based on Logged By and Date.
Basically along the lines of…
get the Sprint Start and End dates and Logged By from our DVST project, go to the TIME project and sum all the time for the Logged By between those Dates and feed that back into my table showing the Sprint hours (original estimated hours, time spent, etc.). The goal is to report all the hours logged for the sprint period for the various groups (Dev Teams/Boards).

2

Hi,

The idea of such a calculation is to create the formula summing up the hours spent over the period matching the sprint start and end dates. The following formula collects all the hours from the sprint period, considering that you use the sprint dimension in the report:

sum(
  {[Time].[Day].DateMember([Measures].[Sprint start date]):
  [Time].[Day].DateMember([Measures].[Sprint end date])},
  
  ([Measures].[Hours spent],
   [Sprint].DefaultMember)
)

Note a tiny limitation of this solution; the hours are taken from full days regardless when exactly sprint was started. That might return a bit more than expected if the hours are logged in the same day before sprint start or in the day of sprint completion after the sprint is closed.

You can further extend part of the formula to collect the hours from another project or from a specific user group. For instance, if I wish to collect hours during the sprint from my project “DEMO Beta”:

sum(
  {[Time].[Day].DateMember([Measures].[Sprint start date]):
  [Time].[Day].DateMember([Measures].[Sprint end date])},
  
  ([Measures].[Hours spent],
   [Sprint].DefaultMember,
   [Project].[DEMO Beta])
)

Such a formula would allow creating ar report for comparing hours spent in the sprint from and outside the sprint during the same period of sprint:

image
image.png1614×1254 197 KB

Kindly,
Janis, eazyBI support

3

Thank you for the information. Our “DEMO Beta” equivalent is my company’s Time tracking “project”. I want to limit the time sum from that project to hours logged by our developers. I have created a calculated member under Logged By listing the specific developers. I also want to pull all hours worked on by those developers in the development project during the period that were not in the sprint. The end result would be development hours in Sprint and out of the sprint (same period), then hours in the Time tracking project (PTO, Meetings, Holidays, etc.). We base hour capacity on hours rather than issues or story points so I’m trying to pull the historical information. I want to use that information to calculate velocity and use that for forecasting. Hope this all makes sense. Another question, for the fields in the demo project, is there somewhere that lists the formulas for all the predefined fields? Some have the “show” option, but several do not. I want to view those so I can adjust for using hours instead of issues or story points.

4

Hi,

The solution with the Logged by calculated member seems correct to filter a team of developers. The solution for finding the hours logged outside sprints is to use the specific member from sprint dimension:

[Sprint].[(no board)].[(no sprint)]

The formula for counting the hours outside any sprint for the sprint period would be the following:

sum(
      {[Time].[Day].DateMember([Measures].[Sprint start date]):
      [Time].[Day].DateMember([Measures].[Sprint end date])},
  
  ([Measures].[Hours spent],
   [Sprint].[(no board)].[(no sprint)],
   [Project].DefaultMember)
)

Note that I used the default member of the Project dimension to count the hours from any project. You can use either your PTO project or omit this part of the tuple to count the hours from the project selected in the report.

The standard measures without the “show” option are fully precalculated. You can check the definition of them in the documentation:
https://docs.eazybi.com/eazybijira/getting-started/measures-and-dimensions
https://docs.eazybi.com/eazybijira/data-import/data-from-jira-and-apps

Kindly,
Janis, eazyBI support

1 Like