On eazyBI evaluation period: Request for support on a customised report

Hi All,

I am evaluating eazyBI for Jira and plan to use it for creating customised reports / dashboards for my company. I sent my question to eazyBI Support team on 27.01.2020 but I have not heard any feedback. So, I try to find out the solution myself and found eazyBI community. Hope I can get support from eazyBI experts here.

I use Jira Software Server 8.0.2 as core application and have BigPicture as a Jira’s plugin for Project and Resources Management. Now, I could not see both Allocation hours and Spent hours per day of any assignee for each issue on the same table. I can see Allocation hours per day of any assignee for each issue on Resource Allocation table on BigPicture. While I can see Spent hours per day of any assignee for each issue on Jira’s Timesheet. Since there are too many assignees, around 80 users, in the system, it is not easy to monitor whether each assignee is working as plan or not. So, I need eazyBI for creating a comparison report between Allocation hours and Spent hours per day of any assignee for each issue on a selected period such as 10 working days, last week and next week. I am not sure whether eazyBI can support me on this kind of customised reports or not. I would like to ask for kind support from eazyBI community.

Steps that I worked and created the report can be found below:

  1. Created an issue as sub-task in Jira, put Original Estimate, select Start Date and End Date – customised fields in Jira by BigPicture, and choose an assignee

(Figure 01 and Figure 02)

  1. Got updated resource plan on Resource Allocation table on BigPicture

(Figure 03)

  1. Assignee started working and logged Spent hours per day on the issue

(Figure 04)

  1. Got updated status of the issue on Gantt chart on BigPicture

(Figure 05)

  1. Found Spent hours on the issue on Jira’s Timesheet

(Figure 06)

  1. Imported data from Jira to eazyBI and created a customised report to compare between Allocation hours and Spent hours. The current report can be found below.

(Figure 07)

However, I would like to have the expected report that can be calculated from the following information:

Original Estimate = 40 hours
Working Duration, customised field = End Date – Start Date + 1 day = 07.02.2020 – 03.02.2020 + 1 = 5 days
Average Allocation, customised field = Original Estimation / Working Duration = 40 / 5 = 8 hours / day
Fill-in Average Allocation 8 hours per day on Allocation hours, customised field, on the report from Start Date to End Date

I am a new user and not allowed to put more than one figure on the topic. For eazyBI support team, if you need more details and all figures, please contact me.

How can I achieve this using eazyBI? I would appreciate it if anyone can help me on this. Thank you so much for your kind help in advance.

Best regards,

OoDy

eazyBI does not have default measures for allocation plan. However, here is an example formula you can use for this. The formula below will calculate estimation per period for Original estimated hours between two dates - start date and end date:

CASE WHEN 
-- show for default time periods on workingdays only
([Time].CurrentHierarchyMember.Level.Name = "Day" 
  and 
 NOT CoalesceEmpty([Time].CurrentHierarchyMember.Get("Week day name"),"") MATCHES "Saturday|Sunday")
 or [Time].CurrentHierarchyMember.Level.Name MATCHES "Year|Week|Month|Quarter"
 or [Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember
THEN
NonZero(SUM(
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
  -- check if an issue has start and end date, and it falls into selected period:
  NOT IsEmpty([Measures].[Issue Start date])
  AND
  NOT IsEmpty([Measures].[Issue End date])
  AND
  (DateBetween(
    [Time].CurrentHierarchyMember.StartDate,
    [Measures].[Issue Start date],
    [Measures].[Issue End date])
    OR 
  DateInPeriod(
    [Measures].[Issue Start date], 
    [Time].CurrentHierarchyMember)
  )),
  -- total planned time
  ([Measures].[Original estimated hours],
  [Time].CurrentHierarchy.DefaultMember)
  /
  -- total count of workdays in planned period between start date and end date
  DateDiffWorkdays(
    [Measures].[Issue Start date],
    DateAddDays([Measures].[Issue End date],1)
  )
  *
   -- days per period 
   DateDiffWorkdays(
     TimestampToDate(IIF(DateInPeriod([Measures].[Issue Start date], [Time].CurrentHierarchyMember), 
       DateToTimestamp(DateWithoutTime([Measures].[Issue Start date])),
       DateToTimestamp([Time].CurrentHierarchyMember.StartDate))),
     TimestampToDate(IIF(DateInPeriod([Measures].[Issue End date], [Time].CurrentHierarchyMember), 
       DateToTimestamp(DateAddDays([Measures].[Issue End date],1)),
       DateToTimestamp([Time].CurrentHierarchyMember.NextStartDate)))
   )
))
END

Here I created a measure Estimates with the formula above and used it in the report and split (drill into) it’s values per month.

Daina / support@eazybi.com